Date: Tue, 15 Aug 2006 10:08:35 -0500
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
Message-ID: <20060815150834.GP27928@pervasive.com>
References: <44D9EBEB.9010704@mall.cz> <20367.1155136139@sss.pgh.pa.us>
	<44DAE09B.20009@mall.cz>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <44DAE09B.20009@mall.cz>
User-Agent: Mutt/1.5.11

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