Re: IN() alternatives - Mailing list pgsql-general

From Arjen van der Meijden
Subject Re: IN() alternatives
Date
Msg-id 002d01c2cd64$96ce2070$3ac15e91@acm
Whole thread Raw
In response to Re: IN() alternatives  ("Dann Corbit" <DCorbit@connx.com>)
List pgsql-general
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Namens Dann Corbit
Verzonden: woensdag 5 februari 2003 22:12
Aan: John Smith; pgsql-general@postgresql.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@yahoo.com]
Sent: Wednesday, February 05, 2003 1:03 PM
To: pgsql-general@postgresql.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! Mail Plus - Powerful. Affordable. Sign up now

pgsql-general by date:

Previous
From: Neil Conway
Date:
Subject: Re: not exactly a bug report, but surprising behaviour
Next
From: Andrew Sullivan
Date:
Subject: Re: IN() alternatives