Postgres Pro
Downloads
Home   >   mailing lists

Thread: IN() alternatives

IN() alternatives

From
John Smith
Date:

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

Re: IN() alternatives

From
"Dann Corbit"
Date:
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

Re: IN() alternatives

From
Arjen van der Meijden
Date:
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

Re: IN() alternatives

From
Andrew Sullivan
Date:
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
therebetter 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

Re: IN() alternatives

From
"Cornelia Boenigk"
Date:
Hi John

See chapter 6.15 Subquery Expressions.

Regards
Conni