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
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
<http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com> -
Powerful. Affordable. Sign up now
<http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com>
Import Notes
Resolved by subject fallback
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! <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
On Wed, Feb 05, 2003 at 01:02:53PM -0800, John Smith wrote:
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 ...);
Depending on what you're doing, postgres has an extension of SQL
DELETE FROM tab1 WHERE id=tab2.id2 AND tab2.somefield [some
condition].
At least, this has worked for me.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110