Re: Performs WAY better with enable_seqscan = off

On sun, 2006-05-21 at 02:21 -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;

is there some reason for the complicated form of the
join conditions in the subselect?

would this not be clearer:

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 = cp.product_id
and pav.attribute_id = ac.attribute_id
and pav.status_code is null
and cp.category_id= '1001402'
and cp.is_visible = 'true'
)
order by (ac.sort_order is null),
ac.sort_order,
la.name asc;

possibly the planner would have a better time
figuring out if any indexes are usable or estimating
the subselect rowcount

gnari