X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id C9A9A925C47 for ; Thu, 29 May 2003 08:48:44 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 42685-01 for ; Thu, 29 May 2003 08:48:43 -0400 (EDT) Received: from rwcrmhc51.attbi.com (rwcrmhc51.attbi.com [204.127.198.38]) by developer.postgresql.org (Postfix) with ESMTP id D5D28925C41 for ; Thu, 29 May 2003 08:48:42 -0400 (EDT) Received: from attbi.com (dtenny.ne.client2.attbi.com[24.60.113.221]) by attbi.com (rwcrmhc51) with SMTP id <2003052912484105100k6c39e>; Thu, 29 May 2003 12:48:42 +0000 Message-ID: <3ED601A9.50209@attbi.com> Date: Thu, 29 May 2003 08:48:41 -0400 From: Dave Tenny User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3.1) Gecko/20030425 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Christopher Kings-Lynne Cc: Bruno Wolff III , Andreas Pflug , pgsql-performance@postgresql.org Subject: Re: IN list processing performance (yet again) References: <3ED4B0E5.3050703@attbi.com> <3ED4B75A.2050106@web.de> <3ED4FB02.1010908@attbi.com> <20030528183914.GA6118@wolff.to> <3ED5159E.2050407@attbi.com> <0b9a01c32585$2a8e6900$6500a8c0@fhp.internal> In-Reply-To: <0b9a01c32585$2a8e6900$6500a8c0@fhp.internal> Content-Type: multipart/alternative; boundary="------------070300060608000803080604" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/261 X-Sequence-Number: 2040 This is a multi-part message in MIME format. --------------070300060608000803080604 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Christopher Kings-Lynne wrote: > > >>Also, IN (subquery) is a known performance problem in PGSQL, at least if >> >> >the subquery is going to return > many rows. > > >>It's too bad, since I'm rather fond of subqueries, but I avoid them like >> >> >the plague in PostgreSQL. > >You're not really using a subquery - really just a long list of integers. > Oops, you got that out of context, it was a different piece of conversation about subqueries in IN predicate, not the scalar forms that was my overall discussion point. You're right, I'm using lists of integers, someone else was suggesting using subqueries in some context and I was responding to that. >Subqueries are lightning fast, so long as you conver to the EXISTS form: > >SELECT * FROM tab WHERE id IN (SELECT id2 FROM tab2); > >converts to: > >SELECT * FROM tab WHERE EXISTS (SELECT id2 FROM tab2 WHERE id2=id); > >Chris > > I hadn't thought of that, it's an excellent tip. I'll have to remember it next time I want to use subqueries. (Again, it's a side topic, my primary concern is scalar-form IN lists.) Thanks, Dave --------------070300060608000803080604 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Christopher Kings-Lynne wrote:
  
Also, IN (subquery) is a known performance problem in PGSQL, at least if
    
the subquery is going to return > many rows.
  
It's too bad, since I'm rather fond of subqueries, but I avoid them like
    
the plague in PostgreSQL.

You're not really using a subquery - really just a long list of integers.

Oops, you got that out of context, it was a different piece of conversation about subqueries in IN predicate,
not the scalar forms that was my overall discussion point.  You're right, I'm using lists of integers,
someone else was suggesting using subqueries in some context and I was responding to that.

Subqueries are lightning fast, so long as you conver to the EXISTS form:

SELECT * FROM tab WHERE id IN (SELECT id2 FROM tab2);

converts to:

SELECT * FROM tab WHERE EXISTS (SELECT id2 FROM tab2 WHERE id2=id);

Chris
  
I hadn't thought of that, it's an excellent tip.  I'll have to remember it next time I want to use subqueries.
(Again, it's a side topic, my primary concern is scalar-form IN lists.)

Thanks,

Dave
--------------070300060608000803080604--