Re: 3-table query optimization

On Thu, Aug 10, 2006 at 09:30:35AM +0200, Michal Taborsky - Internet Mall wrote:
> Tom Lane napsal(a):
> >Michal Taborsky - Internet Mall <michal(dot)taborsky(at)mall(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461