Re: IN() alternatives

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

-----Oorspronkelijk bericht-----
Van: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Namens Dann Corbit
Verzonden: woensdag 5 februari 2003 22:12
Aan: John Smith; pgsql-general(at)postgresql(dot)org
Onderwerp: Re: [GENERAL] IN() alternatives

Unless the subquery is correlated, you can reformulate it as a join.

-----Original Message-----
From: John Smith [mailto:john_smith_45678(at)yahoo(dot)com]
Sent: Wednesday, February 05, 2003 1:03 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] IN() alternatives

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! <http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com>
Mail Plus - Powerful. Affordable. Sign up
<http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com> now