From: Ragnar <gnari@hive.is>
To: Brendan Duddridge <brendan@clickspace.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Performs WAY better with enable_seqscan = off
Date: Sun, 21 May 2006 10:50:11 +0000
Message-ID: <1148208611.28240.101.camel@localhost.localdomain> (raw)
In-Reply-To: <E9515F1E-AB80-483D-A15F-3BB347C721AB@clickspace.com>
References: <E9515F1E-AB80-483D-A15F-3BB347C721AB@clickspace.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnari@hive.is, brendan@clickspace.com
Subject: Re: Performs WAY better with enable_seqscan = off
In-Reply-To: <1148208611.28240.101.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox