From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimizer picks smaller table to drive nested loops?
Date: 14 Jul 2003 14:40:37 -0400
Message-ID: <87znjhkl3u.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <10251.1058205875@sss.pgh.pa.us>
References: <873chi2o53.fsf@stark.dyndns.tv>
<10251.1058205875@sss.pgh.pa.us>
Tom Lane <tgl@sss.pgh.pa.us> 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
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: gsstark@mit.edu, tgl@sss.pgh.pa.us
Subject: Re: optimizer picks smaller table to drive nested loops?
In-Reply-To: <87znjhkl3u.fsf@stark.dyndns.tv>
References: <873chi2o53.fsf@stark.dyndns.tv> <10251.1058205875@sss.pgh.pa.us> <87znjhkl3u.fsf@stark.dyndns.tv>
* 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