Received: from localhost (pluto.hub.org [200.46.204.4]) by postgresql.org (Postfix) with ESMTP id 2069F9FA23F for ; Thu, 2 Nov 2006 07:07:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024) with ESMTP id 61466-06 for ; Thu, 2 Nov 2006 07:07:18 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id AFF4C9F98FA for ; Thu, 2 Nov 2006 07:07:21 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id g2so1104750nfe for ; Thu, 02 Nov 2006 03:07:20 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=ldwX7VIFxdGuNrNRM7mt5TAU7I42L/RPH1XU7Szcf1g1y1EfrV6M74nPp9u5/cZkUUMjfd3Z1hHCueDH8tCaeyyMWeXlMLm00EEv1dK9nr6SsZp5dlRus6JK0Y23mX1L/aON5mXcjA41dZ9Xvlh//WcK93LF33bc6hlj09+EavQ= Received: by 10.78.166.7 with SMTP id o7mr420205hue.1162465639911; Thu, 02 Nov 2006 03:07:19 -0800 (PST) Received: by 10.78.164.8 with HTTP; Thu, 2 Nov 2006 03:07:19 -0800 (PST) Message-ID: Date: Thu, 2 Nov 2006 14:07:19 +0300 From: "Nikolay Samokhvalov" Reply-To: nikolay@samokhvalov.com To: pgsql-performance@postgresql.org Subject: Query plan for "heavy" SELECT with "lite" sub-SELECTs MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/20 X-Sequence-Number: 21790 Hello, I do not understand, why Postgres very ofter starts execution from sub-select instead of doing main select and then proceeding to "lite" sub-selects. For example: (example is quite weird, but it demonstrates the problem) 1. explain analyze select * from pg_proc offset 1500 limit 1; "Limit (cost=116.91..116.99 rows=1 width=365) (actual time=2.111..2.112 rows=1 loops=1)" " -> Seq Scan on pg_proc (cost=0.00..175.52 rows=2252 width=365) (actual time=0.034..1.490 rows=1501 loops=1)" "Total runtime: 2.156 ms" 3. explain analyze select oid,* from pg_type where oid=2277 limit 1; "Limit (cost=0.00..5.91 rows=1 width=816) (actual time=0.021..0.022 rows=1 loops=1)" " -> Index Scan using pg_type_oid_index on pg_type (cost=0.00..5.91 rows=1 width=816) (actual time=0.018..0.018 rows=1 loops=1)" " Index Cond: (oid = 2277::oid)" "Total runtime: 0.079 ms" 2. explain analyze select *, (select typname from pg_type where pg_type.oid=pg_proc.prorettype limit 1) from pg_proc offset 1500 limit 1; "Limit (cost=8983.31..8989.30 rows=1 width=365) (actual time=17.648..17.649 rows=1 loops=1)" " -> Seq Scan on pg_proc (cost=0.00..13486.95 rows=2252 width=365) (actual time=0.100..16.851 rows=1501 loops=1)" " SubPlan" " -> Limit (cost=0.00..5.91 rows=1 width=64) (actual time=0.006..0.007 rows=1 loops=1501)" " -> Index Scan using pg_type_oid_index on pg_type (cost=0.00..5.91 rows=1 width=64) (actual time=0.004..0.004 rows=1 loops=1501)" " Index Cond: (oid = $0)" "Total runtime: 17.784 ms" We see that in the 2nd example Postgres starts with "Index Scan using pg_type_oid_index" (1501 iterations!). My understanding of SQL says me that the simplest (and, in this case - and probably in *most* cases - fastest) way to perform such queries is to start from main SELECT and then, when we already have rows from "main" table, perform "lite" sub-selects. So, I expected smth near 2.156 ms + 0.079 ms, but obtain 17.784 ms... For large table this is killing behaviour. 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)? -- Best regards, Nikolay