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@xxxxxxx> 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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
