From: Jim C. Nasby <jnasby@pervasive.com>
To: Michal Taborsky - Internet Mall <michal.taborsky@mall.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: 3-table query optimization
Date: Tue, 15 Aug 2006 10:08:35 -0500
Message-ID: <20060815150834.GP27928@pervasive.com> (raw)
In-Reply-To: <44DAE09B.20009@mall.cz>
References: <44D9EBEB.9010704@mall.cz>
<20367.1155136139@sss.pgh.pa.us>
<44DAE09B.20009@mall.cz>
On Thu, Aug 10, 2006 at 09:30:35AM +0200, Michal Taborsky - Internet Mall wrote:
> Tom Lane napsal(a):
> >Michal Taborsky - Internet Mall <michal.taborsky@mall.cz> writes:
> >>SELECT product.product_id
> >> FROM action
> >> JOIN product ON (product.product_id=action.product_id)
> >> WHERE action.shop_group_id=1
> >> AND EXISTS (SELECT 1
> >> FROM catalog.product_program
> >> WHERE product_id=product.product_id
> >> AND product_program.program_id =1104322
> >> )
> >
> >Try converting the EXISTS subquery to an IN.
>
> The performance is roughly the same. For some groups it's better, for
> some groups, the bigger ones, it's a bit worse. I forgot to mention,
> that the server is running 8.0.2. Upgrading would be a bit painful, as
> it is a 24/7 production system, but if it would help significantly, we'd
> give it a go.
You're exposing yourself to at least one data-loss bug and a security
hole by running 8.0.2. You should at least move to 8.0.8, which won't
require a lot of downtime.
If you can make it happen, moving to 8.1.4 would almost certainly net a
noticable performance gain. I've seen 50-100% improvements, but how much
gain you'll actually see is highly workload dependent.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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: jnasby@pervasive.com, michal.taborsky@mall.cz
Subject: Re: 3-table query optimization
In-Reply-To: <20060815150834.GP27928@pervasive.com>
* 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