Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 130139FB28E for ; Thu, 2 Nov 2006 10:25:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 13993-02 for ; Thu, 2 Nov 2006 10:25:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id 04CD29FB28A for ; Thu, 2 Nov 2006 10:25:31 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [67.90.96.2] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 111749858; Thu, 02 Nov 2006 06:25:28 -0800 From: "Dave Dutcher" To: , Subject: Re: Query plan for "heavy" SELECT with "lite" sub-SELECTs Date: Thu, 2 Nov 2006 08:25:27 -0600 Message-ID: <012a01c6fe8a$bee330e0$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 Importance: Normal In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/23 X-Sequence-Number: 21793 > -----Original Message----- > From: pgsql-performance-owner@postgresql.org=20 > Nikolay Samokhvalov >=20 > 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=20 select (select typname from pg_type where pg_type.oid=3Dmainq.prorettype = limit 1) from (select * from pg_proc offset 1500 limit 1) mainq; QUERY = PLAN -------------------------------------------------------------------------= --- ------------------------------------------------- Subquery Scan mainq (cost=3D50.99..56.85 rows=3D1 width=3D4) (actual time=3D13.646..13.659 rows=3D1 loops=3D1) -> Limit (cost=3D50.99..51.02 rows=3D1 width=3D310) (actual time=3D13.575..13.579 rows=3D1 loops=3D1) -> Seq Scan on pg_proc (cost=3D0.00..62.34 rows=3D1834 = width=3D310) (actual time=3D0.014..7.297 rows=3D1501 loops=3D1) SubPlan -> Limit (cost=3D0.00..5.82 rows=3D1 width=3D64) (actual = time=3D0.038..0.043 rows=3D1 loops=3D1) -> Index Scan using pg_type_oid_index on pg_type (cost=3D0.00..5.82 rows=3D1 width=3D64) (actual time=3D0.028..0.028 = rows=3D1 loops=3D1) Index Cond: (oid =3D $0) Total runtime: 13.785 ms I would expect you to get closer to 2 ms on that query. My machine = takes 13 ms to do just the seq scan of pg_proc. Dave