1

Struggling with this one a little bit, as I would have expected the performance to be better combining the queries that separated.

The queries are produced using Django ORM 1.11.

With the Similarity in the where cause, full sequence scans are performed both on products_displayproduct and products_displayproductvariant. Without it and running both queries separately, there is no seq scan.

    \d+ products_displayproduct                                                 Table "public.products_displayproduct"
        Column         |           Type           | Collation | Nullable |                       Default                       | Storage  | Stats target | Description 
-----------------------+--------------------------+-----------+----------+-----------------------------------------------------+----------+--------------+-------------
 id                    | integer                  |           | not null | nextval('products_displayproduct_id_seq'::regclass) | plain    |              | 
 date_created          | timestamp with time zone |           | not null |                                                     | plain    |              | 
 date_updated          | timestamp with time zone |           | not null |                                                     | plain    |              | 
 name                  | character varying(255)   |           | not null |                                                     | extended |              | 
 sub_title             | character varying(255)   |           |          |                                                     | extended |              | 
 tags                  | character varying(255)   |           |          |                                                     | extended |              | 
 has_multiple_variants | boolean                  |           | not null |                                                     | plain    |              | 
 rating                | numeric(3,2)             |           | not null |                                                     | main     |              | 
 reviews               | smallint                 |           | not null |                                                     | plain    |              | 
 is_toppick            | boolean                  |           | not null |                                                     | plain    |              | 
 brand_id              | integer                  |           |          |                                                     | plain    |              | 
 placement_id          | integer                  |           | not null |                                                     | plain    |              | 
 poster_image_id       | integer                  |           |          |                                                     | plain    |              | 
Indexes:
    "products_displayproduct_pkey" PRIMARY KEY, btree (id)
    "products_displayproduct_brand_id_c215ec11" btree (brand_id)
    "products_displayproduct_is_toppick_3901660d" btree (is_toppick)
    "products_displayproduct_name_6e5686c5" btree (name)
    "products_displayproduct_name_6e5686c5_like" btree (name varchar_pattern_ops)
    "products_displayproduct_name_fsimilarity_new" gist (name gist_trgm_ops)
    "products_displayproduct_placement_id_90f0d4a2" btree (placement_id)
    "products_displayproduct_poster_image_id_7c188e4d" btree (poster_image_id)
    "products_displayproduct_rating_98794c10" btree (rating)
    "products_displayproduct_reviews_4d5513bb" btree (reviews)
    "products_displayproduct_sub_title_72354226" btree (sub_title)
    "products_displayproduct_sub_title_72354226_like" btree (sub_title varchar_pattern_ops)
    "products_displayproduct_tags_ab900c6b" btree (tags)
    "products_displayproduct_tags_ab900c6b_like" btree (tags varchar_pattern_ops)
Foreign-key constraints:
    "products_displayprod_brand_id_c215ec11_fk_products_" FOREIGN KEY (brand_id) REFERENCES products_displaybrand(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_placement_id_90f0d4a2_fk_retailers" FOREIGN KEY (placement_id) REFERENCES retailers_displaysubcategory(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_poster_image_id_7c188e4d_fk_products_" FOREIGN KEY (poster_image_id) REFERENCES products_displayproductimage(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "intake_intakeproduct" CONSTRAINT "intake_intakeproduct_displayed_as_id_479d7ed0_fk_products_" FOREIGN KEY (displayed_as_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_displayproductvariant" CONSTRAINT "products_displayprod_product_id_210696e5_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "reviews_displayproductreview" CONSTRAINT "reviews_displayprodu_product_id_1d59703e_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED

products_displayproductvariant

\d+ products_displayproductvariant;
                                                              Table "public.products_displayproductvariant"
      Column      |           Type           | Collation | Nullable |                          Default                           | Storage  | Stats target | Description 
------------------+--------------------------+-----------+----------+------------------------------------------------------------+----------+--------------+-------------
 id               | integer                  |           | not null | nextval('products_displayproductvariant_id_seq'::regclass) | plain    |              | 
 name             | character varying(128)   |           |          |                                                            | extended |              | 
 sub_title        | character varying(255)   |           |          |                                                            | extended |              | 
 date_created     | timestamp with time zone |           | not null |                                                            | plain    |              | 
 date_updated     | timestamp with time zone |           | not null |                                                            | plain    |              | 
 description      | text                     |           |          |                                                            | extended |              | 
 features         | text                     |           |          |                                                            | extended |              | 
 content          | text                     |           |          |                                                            | extended |              | 
 warranty_string  | character varying(255)   |           |          |                                                            | extended |              | 
 identifier_upc   | character varying(255)   |           |          |                                                            | extended |              | 
 identifier_model | character varying(255)   |           |          |                                                            | extended |              | 
 product_id       | integer                  |           | not null |                                                            | plain    |              | 
Indexes:
    "products_displayproductvariant_pkey" PRIMARY KEY, btree (id)
    "products_displayproductvariant_content_2318bca5" btree (content)
    "products_displayproductvariant_content_2318bca5_like" btree (content text_pattern_ops)
    "products_displayproductvariant_description_74f9acaa" btree (description)
    "products_displayproductvariant_description_74f9acaa_like" btree (description text_pattern_ops)
    "products_displayproductvariant_features_a0dd52ef" btree (features)
    "products_displayproductvariant_features_a0dd52ef_like" btree (features text_pattern_ops)
    "products_displayproductvariant_identifier_model_4728e069" btree (identifier_model)
    "products_displayproductvariant_identifier_model_4728e069_like" btree (identifier_model varchar_pattern_ops)
    "products_displayproductvariant_identifier_upc_ea927ee2" btree (identifier_upc)
    "products_displayproductvariant_identifier_upc_ea927ee2_like" btree (identifier_upc varchar_pattern_ops)
    "products_displayproductvariant_name_b4db6c57" btree (name)
    "products_displayproductvariant_name_b4db6c57_like" btree (name varchar_pattern_ops)
    "products_displayproductvariant_product_id_210696e5" btree (product_id)
    "products_displayproductvariant_sub_title_d2845610" btree (sub_title)
    "products_displayproductvariant_sub_title_d2845610_like" btree (sub_title varchar_pattern_ops)
    "products_displayproductvariant_warranty_string_9b748bb6" btree (warranty_string)
    "products_displayproductvariant_warranty_string_9b748bb6_like" btree (warranty_string varchar_pattern_ops)
Foreign-key constraints:
    "products_displayprod_product_id_210696e5_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "intake_intakeproductvariant" CONSTRAINT "intake_intakeproduct_displayed_as_id_4a21f6fa_fk_products_" FOREIGN KEY (displayed_as_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_displayproductspec" CONSTRAINT "products_displayprod_product_variant_id_249947b4_fk_products_" FOREIGN KEY (product_variant_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_displayproductimage" CONSTRAINT "products_displayprod_product_variant_id_54c522eb_fk_products_" FOREIGN KEY (product_variant_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_displayproductattr" CONSTRAINT "products_displayprod_product_variant_id_7ac08a9e_fk_products_" FOREIGN KEY (product_variant_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_displayproductaccess" CONSTRAINT "products_displayprod_product_variant_id_ab7b9896_fk_products_" FOREIGN KEY (product_variant_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "shop_pricealert" CONSTRAINT "shop_pricealert_product_id_cb755f29_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED

products_displayproductaccess

\d+ products_displayproductaccess;
                                                               Table "public.products_displayproductaccess"
       Column       |           Type           | Collation | Nullable |                          Default                          | Storage  | Stats target | Description 
--------------------+--------------------------+-----------+----------+-----------------------------------------------------------+----------+--------------+-------------
 id                 | integer                  |           | not null | nextval('products_displayproductaccess_id_seq'::regclass) | plain    |              | 
 date_created       | timestamp with time zone |           | not null |                                                           | plain    |              | 
 date_updated       | timestamp with time zone |           | not null |                                                           | plain    |              | 
 price              | numeric(16,2)            |           |          |                                                           | main     |              | 
 quantity           | smallint                 |           | not null |                                                           | plain    |              | 
 department         | character varying(64)    |           |          |                                                           | extended |              | 
 aisle              | character varying(64)    |           |          |                                                           | extended |              | 
 original_price     | numeric(16,2)            |           |          |                                                           | main     |              | 
 sale_until         | date                     |           |          |                                                           | plain    |              | 
 location_id        | integer                  |           | not null |                                                           | plain    |              | 
 product_variant_id | integer                  |           | not null |                                                           | plain    |              | 
 retailer_id        | integer                  |           | not null |                                                           | plain    |              | 
Indexes:
    "products_displayproductaccess_pkey" PRIMARY KEY, btree (id)
    "products_di_locatio_f11b68_idx" btree (location_id, product_variant_id)
    "products_displayproductaccess_aisle_61457682" btree (aisle)
    "products_displayproductaccess_aisle_61457682_like" btree (aisle varchar_pattern_ops)
    "products_displayproductaccess_date_created_d38d9b4e" btree (date_created)
    "products_displayproductaccess_date_updated_0c689801" btree (date_updated)
    "products_displayproductaccess_department_a7031553" btree (department)
    "products_displayproductaccess_department_a7031553_like" btree (department varchar_pattern_ops)
    "products_displayproductaccess_location_id_fe5aeb43" btree (location_id)
    "products_displayproductaccess_original_price_904da60f" btree (original_price)
    "products_displayproductaccess_price_0e2f3286" btree (price)
    "products_displayproductaccess_product_variant_id_ab7b9896" btree (product_variant_id)
    "products_displayproductaccess_quantity_72b1a17c" btree (quantity)
    "products_displayproductaccess_retailer_id_70041027" btree (retailer_id)
    "products_displayproductaccess_sale_until_afb569bf" btree (sale_until)
Foreign-key constraints:
    "products_displayprod_location_id_fe5aeb43_fk_retailers" FOREIGN KEY (location_id) REFERENCES retailers_baselocation(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_product_variant_id_ab7b9896_fk_products_" FOREIGN KEY (product_variant_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_retailer_id_70041027_fk_retailers" FOREIGN KEY (retailer_id) REFERENCES retailers_retailer(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "intake_intakeproductaccess" CONSTRAINT "intake_intakeproduct_displayed_as_id_5b030a8f_fk_products_" FOREIGN KEY (displayed_as_id) REFERENCES products_displayproductaccess(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "shop_shoppinglistitem" CONSTRAINT "shop_shoppinglistite_access_id_6217e45f_fk_products_" FOREIGN KEY (access_id) REFERENCES products_displayproductaccess(id) DEFERRABLE INITIALLY DEFERRED

Attempted Query:

SELECT
   "products_displayproduct"."id",
   "products_displayproduct"."date_created",
   "products_displayproduct"."date_updated",
   "products_displayproduct"."name",
   "products_displayproduct"."sub_title",
   "products_displayproduct"."tags",
   "products_displayproduct"."has_multiple_variants",
   "products_displayproduct"."placement_id",
   "products_displayproduct"."brand_id",
   "products_displayproduct"."poster_image_id",
   "products_displayproduct"."rating",
   "products_displayproduct"."reviews",
   "products_displayproduct"."is_toppick",
   (
      "products_displayproduct"."name" <-> 'Tundra Tested Winch with Remote Control, 2,000-lb'
   )
   AS "similarity" 
FROM
   "products_displayproduct" 
   INNER JOIN
      "products_displayproductvariant" 
      ON ("products_displayproduct"."id" = "products_displayproductvariant"."product_id") 
   INNER JOIN
      "products_displayproductaccess" 
      ON ("products_displayproductvariant"."id" = "products_displayproductaccess"."product_variant_id") 
WHERE
   (
      "products_displayproductaccess"."location_id" IN 
      (
         1,
         2,
         3,
         751,
         1079
      )
      AND 
      (
         "products_displayproduct"."name" <-> 'Tundra Tested Winch with Remote Control, 2,000-lb'
      )
      > 0.21 
      AND NOT ("products_displayproduct"."id" IN 
      (
         60267
      )
)
   )
ORDER BY
   "similarity" DESC
LIMIT 10;

Results in:

enter image description here

But doing both these queries separately results in an acceptable query response time.

enter image description here

enter image description here

Separately it also looks like the indexes are being used first and properly.

enter image description here

enter image description here

In the first query, the results don't use the indexes until later and only for unrelated fields:

enter image description here

Query Plans as text

QUERY PLAN
Limit  (cost=136501.21..136502.36 rows=10 width=600) (actual time=1468.055..1480.010 rows=10 loops=1)
  ->  Gather Merge  (cost=136501.21..143818.43 rows=63628 width=600) (actual time=1468.054..1480.006 rows=10 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Sort  (cost=135501.20..135660.27 rows=63628 width=600) (actual time=1463.679..1463.680 rows=10 loops=2)
              Sort Key: (((products_displayproduct.name)::text <-> 'Tundra Tested Winch with Remote Control, 2,000-lb'::text)) DESC
              Sort Method: top-N heapsort  Memory: 27kB
              Worker 0:  Sort Method: top-N heapsort  Memory: 27kB
              ->  Nested Loop  (cost=1630.59..134126.22 rows=63628 width=600) (actual time=401.495..1434.477 rows=97784 loops=2)
                    ->  Hash Join  (cost=1630.03..5103.08 rows=12463 width=600) (actual time=401.419..426.831 rows=31780 loops=2)
                          Hash Cond: (products_displayproductvariant.product_id = products_displayproduct.id)
                          ->  Parallel Seq Scan on products_displayproductvariant  (cost=0.00..3374.89 rows=37389 width=8) (actual time=0.009..7.390 rows=31781 loops=2)
                          ->  Hash  (cost=1442.71..1442.71 rows=14985 width=596) (actual time=401.285..401.286 rows=44884 loops=2)
                                Buckets: 65536 (originally 16384)  Batches: 1 (originally 1)  Memory Usage: 5782kB
                                ->  Seq Scan on products_displayproduct  (cost=0.00..1442.71 rows=14985 width=596) (actual time=0.108..385.217 rows=44884 loops=2)
                                      Filter: ((id <> 60267) AND (((name)::text <-> 'Tundra Tested Winch with Remote Control, 2,000-lb'::text) > '0.21'::double precision))
                                      Rows Removed by Filter: 2
                    ->  Index Only Scan using products_di_locatio_f11b68_idx on products_displayproductaccess  (cost=0.56..10.21 rows=13 width=4) (actual time=0.003..0.010 rows=3 loops=63560)
                          Index Cond: ((location_id = ANY ('{1,2,3,751,1079}'::integer[])) AND (product_variant_id = products_displayproductvariant.id))
                          Heap Fetches: 195567
Planning Time: 0.560 ms
Execution Time: 1480.121 ms

QUERY PLAN
Limit  (cost=0.28..2.18 rows=10 width=604) (actual time=32.223..32.463 rows=10 loops=1)
  ->  Index Scan using products_displayproduct_name_fsimilarity_new on products_displayproduct  (cost=0.28..2854.69 rows=14985 width=604) (actual time=32.221..32.460 rows=10 loops=1)
        Order By: ((name)::text <-> 'Tundra Tested Winch with Remote Control 2,000 - lb'::text)
        Filter: (((name)::text <-> 'Tundra Tested Winch with Remote Control 2,000 - lb'::text) > '0.21'::double precision)
        Rows Removed by Filter: 2
Planning Time: 0.119 ms
Execution Time: 32.493 ms


QUERY PLAN
Limit  (cost=1.14..14.65 rows=10 width=596) (actual time=0.024..0.110 rows=10 loops=1)
  ->  Nested Loop  (cost=1.14..438168.69 rows=324504 width=596) (actual time=0.023..0.107 rows=10 loops=1)
        ->  Nested Loop  (cost=0.85..333583.81 rows=324509 width=4) (actual time=0.019..0.047 rows=10 loops=1)
              ->  Index Scan using products_displayproductaccess_location_id_fe5aeb43 on products_displayproductaccess  (cost=0.56..230620.43 rows=324509 width=4) (actual time=0.015..0.026 rows=10 loops=1)
                    Index Cond: (location_id = ANY ('{1,2,3,751,1079}'::integer[]))
              ->  Index Scan using products_displayproductvariant_pkey on products_displayproductvariant  (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=10)
                    Index Cond: (id = products_displayproductaccess.product_variant_id)
        ->  Index Scan using products_displayproduct_pkey on products_displayproduct  (cost=0.29..0.32 rows=1 width=596) (actual time=0.005..0.005 rows=1 loops=10)
              Index Cond: (id = products_displayproductvariant.product_id)
              Filter: (id <> 60267)
Planning Time: 0.547 ms
Execution Time: 0.137 ms
1
  • So I found out this is due to the order by DESC (which makes sense to order by given the intention of finding similar rows). Removing the ORDER BY similarity DESC allows postegres to use the indexes. I found another great post by jjanes here: dba.stackexchange.com/a/183561/195368 and perhaps creating a DESC index would help, however I read gist and gin don't support creating a DESC index. The post said postgres should work with it, however as shown it does not. Any advice? Nov 20 '19 at 1:31
0

Answer by jjanes:

Two separate LIMITs do something quite different than one combined LIMIT.

If you are happy with the outcome of the two separate LIMITS, write two subqueries with one LIMIT each, and join the subqueries.

If you need the semantics of the query as written, you could set enable_seqscan=off and see what it changes to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.