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:
But doing both these queries separately results in an acceptable query response time.
Separately it also looks like the indexes are being used first and properly.
In the first query, the results don't use the indexes until later and only for unrelated fields:
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
