Skip site navigation (1) Skip section navigation (2)

Performs WAY better with enable_seqscan = off

From: Brendan Duddridge <brendan(at)clickspace(dot)com>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Performs WAY better with enable_seqscan = off
Date: 2006-05-21 08:21:55
Message-ID: E9515F1E-AB80-483D-A15F-3BB347C721AB@clickspace.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)clickspace(dot)com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com

Responses

pgsql-performance by date

Next:From: RagnarDate: 2006-05-21 10:50:11
Subject: Re: Performs WAY better with enable_seqscan = off
Previous:From: David WheelerDate: 2006-05-20 04:51:30
Subject: Benchmarking Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group