Re: 3-table query optimization

Michal Taborsky - Internet Mall <michal.taborsky@xxxxxxx> · Thu, 10 Aug 2006 09:30:35 +0200

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.

--
Michal Táborský