X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id D1748CBFB2 for ; Mon, 14 Jul 2003 18:40:48 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 36719-09 for ; Mon, 14 Jul 2003 18:41:12 +0000 (GMT) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by svr1.postgresql.org (Postfix) with ESMTP id 43CBECBFB0 for ; Mon, 14 Jul 2003 15:40:46 -0300 (ADT) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id 29D16139067; Mon, 14 Jul 2003 14:40:39 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 19c8FZ-00085s-00; Mon, 14 Jul 2003 14:40:37 -0400 To: Tom Lane Cc: Greg Stark , pgsql-performance@postgresql.org Subject: Re: optimizer picks smaller table to drive nested loops? References: <873chi2o53.fsf@stark.dyndns.tv> <10251.1058205875@sss.pgh.pa.us> In-Reply-To: <10251.1058205875@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 14 Jul 2003 14:40:37 -0400 Message-ID: <87znjhkl3u.fsf@stark.dyndns.tv> Lines: 32 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/228 X-Sequence-Number: 2521 Tom Lane writes: > You seem to be using a rather wacko value of cpu_tuple_cost; those > Result nodes ought to be costed at 0.01 not 1.00. With the default oops yes, thanks. that was left over from other experimentation. > However, it looks to me like the subquery-scan-outside plan probably > is the faster one, on both my machine and yours. I get > > regression=# explain analyze select * from tenk1, (select 1 union all select 2) as x; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------- > Nested Loop (cost=0.00..858.00 rows=20000 width=248) (actual time=0.42..3648.61 rows=20000 loops=1) > -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) (actual time=0.23..199.97 rows=10000 loops=1) > -> Subquery Scan x (cost=0.00..0.02 rows=2 width=0) (actual time=0.07..0.24 rows=2 loops=10000) ... > Total runtime: 3807.39 msec > Nested Loop (cost=0.00..40718.00 rows=20000 width=248) (actual time=0.39..1214.42 rows=20000 loops=1) > -> Subquery Scan x (cost=0.00..2.00 rows=2 width=0) (actual time=0.10..0.31 rows=2 loops=1) > -> Seq Scan on tenk1 (cost=0.00..10358.00 rows=10000 width=244) (actual time=0.17..188.37 rows=10000 loops=2) > Total runtime: 1371.17 msec Woah, that's pretty whacky. It seems like it ought to be way faster to do a single sequential scan and return two records for each tuple read rather than do an entire unnecessary sequential scan, even if most or even all of the second one is cached. -- greg