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 207EBCC10D for ; Mon, 14 Jul 2003 18:04:16 +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 39006-03 for ; Mon, 14 Jul 2003 18:04:36 +0000 (GMT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 00F9ECC10C for ; Mon, 14 Jul 2003 15:04:10 -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 h6EI4ZQ3010252; Mon, 14 Jul 2003 14:04:35 -0400 (EDT) To: Greg Stark Cc: pgsql-performance@postgresql.org Subject: Re: optimizer picks smaller table to drive nested loops? In-reply-to: <873chi2o53.fsf@stark.dyndns.tv> References: <873chi2o53.fsf@stark.dyndns.tv> Comments: In-reply-to Greg Stark message dated "07 Jul 2003 14:22:00 -0400" Date: Mon, 14 Jul 2003 14:04:35 -0400 Message-ID: <10251.1058205875@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/227 X-Sequence-Number: 2520 Greg Stark writes: > slo=> explain analyze select * from region, (select 1 union all select 2) as x; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------ > Nested Loop (cost=0.00..11162.00 rows=5534 width=108) (actual time=0.13..541.19 rows=5534 loops=1) > -> Subquery Scan x (cost=0.00..2.00 rows=2 width=0) (actual time=0.03..0.08 rows=2 loops=1) > -> Append (cost=0.00..2.00 rows=2 width=0) (actual time=0.02..0.05 rows=2 loops=1) > -> Subquery Scan "*SELECT* 1" (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=1) > -> Result (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) > -> Subquery Scan "*SELECT* 2" (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=1) > -> Result (cost=0.00..1.00 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) > -> Seq Scan on region (cost=0.00..2813.00 rows=2767 width=104) (actual time=0.03..123.44 rows=2767 loops=2) > Total runtime: 566.24 msec > (9 rows) > Wouldn't it be faster to drive the nested loop the other way around? 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 cost settings I get an other-way-around plan for a similar test. (I used tenk1 from the regression database as the outer table.) 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) -> Append (cost=0.00..0.02 rows=2 width=0) (actual time=0.05..0.17 rows=2 loops=10000) -> Subquery Scan "*SELECT* 1" (cost=0.00..0.01 rows=1 width=0) (actual time=0.03..0.06 rows=1 loops=10000) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=10000) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.01 rows=1 width=0) (actual time=0.03..0.06 rows=1 loops=10000) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.02 rows=1 loops=10000) Total runtime: 3807.39 msec (9 rows) regression=# set cpu_tuple_cost = 1; SET regression=# explain analyze select * from tenk1, (select 1 union all select 2) as x; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ 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) -> Append (cost=0.00..2.00 rows=2 width=0) (actual time=0.06..0.22 rows=2 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..1.00 rows=1 width=0) (actual time=0.05..0.08 rows=1 loops=1) -> Result (cost=0.00..1.00 rows=1 width=0) (actual time=0.03..0.04 rows=1 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..1.00 rows=1 width=0) (actual time=0.05..0.08 rows=1 loops=1) -> Result (cost=0.00..1.00 rows=1 width=0) (actual time=0.02..0.03 rows=1 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 (9 rows) The flipover point between the two plans is cpu_tuple_cost = 0.04 in my tests. It looks to me like we've neglected to charge any cost associated with Subquery Scan or Append nodes. Certainly Subquery Scan ought to charge at least a cpu_tuple_cost per row. Perhaps Append ought to as well --- although since it doesn't do selection or projection, I'm not quite sure where the time is going in that case. (Hmmm... time to get out the profiler...) regards, tom lane