| Hi, I have a query that performs WAY better when I have enable_seqscan = off: explain analyze select ac.attribute_id, la.name, ac.sort_order from attribute_category ac, localized_attribute la where ac.category_id = 1001402 and la.locale_id = 1000001 and ac.is_browsable = 'true' and la.attribute_id = ac.attribute_id and exists ( select 'x' from product_attribute_value pav, category_product cp where (pav.product_id || '.' || pav.attribute_id) = (cp.product_id || '.' || ac.attribute_id) and pav.status_code is null and (cp.category_id || '.' || cp.is_visible) = '1001402.true') order by (ac.sort_order is null), ac.sort_order, la.name asc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=47.97..47.98 rows=7 width=34) (actual time=33368.721..33368.721 rows=2 loops=1) Sort Key: (ac.sort_order IS NULL), ac.sort_order, la.name -> Nested Loop (cost=2.00..47.87 rows=7 width=34) (actual time=13563.049..33368.679 rows=2 loops=1) -> Index Scan using attribute_category__category_id_fk_idx on attribute_category ac (cost=0.00..26.73 rows=7 width=8) (actual time=13562.918..33368.370 rows=2 loops=1) Index Cond: (category_id = 1001402) Filter: (((is_browsable)::text = 'true'::text) AND (subplan)) SubPlan -> Nested Loop (cost=0.02..278217503.21 rows=354763400 width=0) (actual time=4766.821..4766.821 rows=0 loops=7) -> Seq Scan on category_product cp (cost=0.00..158150.26 rows=18807 width=4) (actual time=113.595..4585.461 rows=12363 loops=7) Filter: ((((category_id)::text || '.'::text) || (is_visible)::text) = '1001402.true'::text) -> Index Scan using product_attribute_value__prod_id_att_id_status_is_null_ids on product_attribute_value pav (cost=0.02..14171.84 rows=18863 width=8) (actual time=0.012..0.012 rows=0 loops=86538) Index Cond: ((((pav.product_id)::text || '.'::text) || (pav.attribute_id)::text) = ((("outer".product_id)::text || '.'::text) || ($0)::text)) -> Bitmap Heap Scan on localized_attribute la (cost=2.00..3.01 rows=1 width=30) (actual time=0.129..0.129 rows=1 loops=2) Recheck Cond: (la.attribute_id = "outer".attribute_id) Filter: (locale_id = 1000001) -> Bitmap Index Scan on localized_attribute__attribute_id_fk_idx (cost=0.00..2.00 rows=1 width=0) (actual time=0.091..0.091 rows=1 loops=2) Index Cond: (la.attribute_id = "outer".attribute_id) Total runtime: 33369.105 ms Now when I disable sequential scans: set enable_seqscan = off; explain analyze select ac.attribute_id, la.name, ac.sort_order from attribute_category ac, localized_attribute la where ac.category_id = 1001402 and la.locale_id = 1000001 and ac.is_browsable = 'true' and la.attribute_id = ac.attribute_id and exists ( select 'x' from product_attribute_value pav, category_product cp where (pav.product_id || '.' || pav.attribute_id) = (cp.product_id || '.' || ac.attribute_id) and pav.status_code is null and (cp.category_id || '.' || cp.is_visible) = '1001402.true') order by (ac.sort_order is null), ac.sort_order, la.name asc; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=48.09..48.11 rows=7 width=34) (actual time=1675.944..1675.945 rows=2 loops=1) Sort Key: (ac.sort_order IS NULL), ac.sort_order, la.name -> Nested Loop (cost=2.00..48.00 rows=7 width=34) (actual time=687.600..1675.831 rows=2 loops=1) -> Index Scan using attribute_category__category_id_fk_idx on attribute_category ac (cost=0.00..26.86 rows=7 width=8) (actual time=687.441..1675.584 rows=2 loops=1) Index Cond: (category_id = 1001402) Filter: (((is_browsable)::text = 'true'::text) AND (subplan)) SubPlan -> Nested Loop (cost=0.03..278076992.97 rows=354763400 width=0) (actual time=239.299..239.299 rows=0 loops=7) -> Index Scan using category_product__cat_id_is_visible_idx on category_product cp (cost=0.01..17640.02 rows=18807 width=4) (actual time=0.036..30.205 rows=12363 loops=7) Index Cond: ((((category_id)::text || '.'::text) || (is_visible)::text) = '1001402.true'::text) -> Index Scan using product_attribute_value__prod_id_att_id_status_is_null_ids on product_attribute_value pav (cost=0.02..14171.84 rows=18863 width=8) (actual time=0.013..0.013 rows=0 loops=86538) Index Cond: ((((pav.product_id)::text || '.'::text) || (pav.attribute_id)::text) = ((("outer".product_id)::text || '.'::text) || ($0)::text)) -> Bitmap Heap Scan on localized_attribute la (cost=2.00..3.01 rows=1 width=30) (actual time=0.093..0.094 rows=1 loops=2) Recheck Cond: (la.attribute_id = "outer".attribute_id) Filter: (locale_id = 1000001) -> Bitmap Index Scan on localized_attribute__attribute_id_fk_idx (cost=0.00..2.00 rows=1 width=0) (actual time=0.060..0.060 rows=1 loops=2) Index Cond: (la.attribute_id = "outer".attribute_id) Total runtime: 1676.727 ms the tables involved with the query have all been vacuum analyzed. I also have default_statistics_target = 100. There's something definitely wrong with that Nested Loop with the high row count. That row count appears to be close to the product of the number of rows in category_product and product_attribute_value. Any ideas and help would be greatly appreciated. Thanks, ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@xxxxxxxxxxxxxx ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com |
