From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimizer picks smaller table to drive nested loops?
Date: Mon, 14 Jul 2003 14:04:35 -0400
Message-ID: <10251.1058205875@sss.pgh.pa.us> (raw)
In-Reply-To: <873chi2o53.fsf@stark.dyndns.tv>
References: <873chi2o53.fsf@stark.dyndns.tv>
Greg Stark <gsstark@mit.edu> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, gsstark@mit.edu
Subject: Re: optimizer picks smaller table to drive nested loops?
In-Reply-To: <10251.1058205875@sss.pgh.pa.us>
References: <873chi2o53.fsf@stark.dyndns.tv> <10251.1058205875@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox