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 AA331925304 for ; Wed, 28 May 2003 16:01:38 -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 24942-09 for ; Wed, 28 May 2003 16:01:36 -0400 (EDT) Received: from sccrmhc01.attbi.com (sccrmhc01.attbi.com [204.127.202.61]) by developer.postgresql.org (Postfix) with ESMTP id 6CAF59251A5 for ; Wed, 28 May 2003 16:01:35 -0400 (EDT) Received: from attbi.com (dtenny.ne.client2.attbi.com[24.60.113.221]) by attbi.com (sccrmhc01) with SMTP id <20030528200135001004j3ske>; Wed, 28 May 2003 20:01:35 +0000 Message-ID: <3ED5159E.2050407@attbi.com> Date: Wed, 28 May 2003 16:01:34 -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: Bruno Wolff III Cc: 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> In-Reply-To: <20030528183914.GA6118@wolff.to> Content-Type: multipart/alternative; boundary="------------000401040609080703080200" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/247 X-Sequence-Number: 2026 This is a multi-part message in MIME format. --------------000401040609080703080200 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Bruno Wolff III wrote: >On Wed, May 28, 2003 at 14:08:02 -0400, > Dave Tenny wrote: > > >>Andreas Pflug wrote: >> >>I'm reminded to relay to the PostgreSQL devos that I might be able to do >>more in the join or subquery department if >>PostgreSQL had better performing MAX functions and a FIRST function for >>selecting rows from groups. >>("Performing" being the operative word here, since the extensible >>architecture of PostgreSQL currently makes for poorly >>performing MAX capabilities and presumably similar user defined >>aggregate functions). >> >> > >Have you tried replacing max with a subselect that uses order by and limit? > > I'm uncertain how that would work, since somewhere in there I still need to elaborate on the 1000 items I want, and they're not necessarily in any particular range, nor do they bear any contiguous group nature. 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. Perhaps I don't understand what you had in mind. --------------000401040609080703080200 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Bruno Wolff III wrote:
On Wed, May 28, 2003 at 14:08:02 -0400,
  Dave Tenny <tenny@attbi.com> wrote:
  
Andreas Pflug wrote:

I'm reminded to relay to the PostgreSQL devos that I might be able to do 
more in the  join or subquery department if
PostgreSQL had better performing MAX functions and a FIRST function for 
selecting rows from groups.
("Performing" being the operative word here, since the extensible 
architecture of PostgreSQL currently makes for poorly
performing MAX capabilities and presumably similar user defined 
aggregate functions).
    

Have you tried replacing max with a subselect that uses order by and limit?
  

I'm uncertain how that would work, since somewhere in there I still need to elaborate on the
1000 items I want, and they're not necessarily in any particular range, nor do they bear any
contiguous group nature.

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.

Perhaps I don't understand what you had in mind.

--------------000401040609080703080200--