Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 13F389FB2AF for ; Thu, 2 Nov 2006 15:16:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 45496-07 for ; Thu, 2 Nov 2006 19:16:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id D495A9FB2A7 for ; Thu, 2 Nov 2006 15:16:03 -0400 (AST) Received: from alvh.no-ip.org (201-220-123-102.bk10-dsl.surnet.cl [201.220.123.102]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id kA2JFxMn022359; Thu, 2 Nov 2006 11:16:00 -0800 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 54F16C2D33D; Thu, 2 Nov 2006 16:15:55 -0300 (CLST) Date: Thu, 2 Nov 2006 16:15:55 -0300 From: Alvaro Herrera To: Dave Dutcher Cc: nikolay@samokhvalov.com, pgsql-performance@postgresql.org Subject: Re: Query plan for "heavy" SELECT with "lite" sub-SELECTs Message-ID: <20061102191555.GL25444@alvh.no-ip.org> Mail-Followup-To: Dave Dutcher , nikolay@samokhvalov.com, pgsql-performance@postgresql.org References: <012a01c6fe8a$bee330e0$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <012a01c6fe8a$bee330e0$8300a8c0@tridecap.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Thu, 02 Nov 2006 11:16:05 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/31 X-Sequence-Number: 21801 Dave Dutcher wrote: > > -----Original Message----- > > From: pgsql-performance-owner@postgresql.org > > Nikolay Samokhvalov > > > > What should I do to make Postgres work properly in such cases (I have > > a lot of similar queries; surely, they are executed w/o seqscans, but > > overall picture is the same - I see that starting from sub-selects > > dramatically decrease performance)? > > How about this: > > explain analyze > select (select typname from pg_type where pg_type.oid=mainq.prorettype limit > 1) > from (select * from pg_proc offset 1500 limit 1) mainq; What's the use of such a query? One would think that in the real world, you'd at least have an ORDER BY somewhere in the subqueries. Performance analysis of strange queries is useful, but the input queries have to be meaningful as well. Otherwise you end up optimizing bizarre and useless cases. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support