Re: Performs WAY better with enable_seqscan = off

The problem is that the planner is guessing horribly at what the nodes
will return, and I'm betting the reason for that is your join criteria.
Why are you joining on fields that are concatenated together, instead of
just joining on the fields themselves? That's a sure-fire way to confuse
the planner, and greatly limit your options.

On Sun, May 21, 2006 at 02:21:55AM -0600, Brendan Duddridge wrote:
> 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
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461