X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id B36BACC1B9 for ; Mon, 14 Jul 2003 22:41:01 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 78857-01 for ; Mon, 14 Jul 2003 22:41:24 +0000 (GMT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 77F1ACC1CD for ; Mon, 14 Jul 2003 19:40:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6EMfPQ3016145; Mon, 14 Jul 2003 18:41:25 -0400 (EDT) To: Greg Stark Cc: pgsql-performance@postgresql.org Subject: Re: optimizer picks smaller table to drive nested loops? In-reply-to: <14110.1058216305@sss.pgh.pa.us> References: <873chi2o53.fsf@stark.dyndns.tv> <10251.1058205875@sss.pgh.pa.us> <87znjhkl3u.fsf@stark.dyndns.tv> <14110.1058216305@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Mon, 14 Jul 2003 16:58:25 -0400" Date: Mon, 14 Jul 2003 18:41:25 -0400 Message-ID: <16143.1058222485@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/230 X-Sequence-Number: 2523 I said: > I am not sure why the planner did not choose to stick a Materialize > node atop the Subquery Scan, though. It looks to me like it should > have considered that option --- possibly the undercharging for Subquery > Scan is the reason it wasn't chosen. Indeed, after fixing the unrealistic estimate for SubqueryScan, I get this: regression=# explain analyze select * from tenk1, (select 1 union all select 2) as x; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.06..858.06 rows=20000 width=248) (actual time=0.25..1448.19 rows=20000 loops=1) -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) (actual time=0.06..162.48 rows=10000 loops=1) -> Materialize (cost=0.06..0.08 rows=2 width=4) (actual time=0.01..0.03 rows=2 loops=10000) -> Subquery Scan x (cost=0.00..0.06 rows=2 width=4) (actual time=0.10..0.27 rows=2 loops=1) -> Append (cost=0.00..0.04 rows=2 width=0) (actual time=0.07..0.20 rows=2 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.02 rows=1 width=0) (actual time=0.05..0.08 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.03..0.03 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=0) (actual time=0.03..0.06 rows=1 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=1) Total runtime: 1627.26 msec (10 rows) which is probably the best way to do it, all things considered. regards, tom lane