Performs WAY better with enable_seqscan = off

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux