Performs WAY better with enable_seqscan = off

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