With a delete query it is not very likely that that join will work...
Afaik there is no real optimisation possible for this situation, postgresql 7.4 claims to have much higher performance with these kinds of queries (where something IN (select)), so waiting for the release of pg7.4 might be your best bet :)
Although the query performance might be mostly foreign key checks, in which case you problably won't see that much difference.
Regards,
Arjen
Unless the subquery is correlated, you can reformulate it as a join.
I thought I saw a doc somewhere showing alternatives to using IN() for better performance, but can't find it :(. Are there better performing query alternatives to this?
delete from tab1 where id in (select id2 from tab2 where ...);
Where the subquery returns 1-10K's of records.
John
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
