From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested loops overpriced
Date: Tue, 08 May 2007 11:53:13 -0400
Message-ID: <1987.1178639593@sss.pgh.pa.us> (raw)
In-Reply-To: <200705081722.16731.peter_e@gmx.net>
References: <200705081722.16731.peter_e@gmx.net>
Peter Eisentraut <peter_e@gmx.net> writes:
> Note how spectacularly overpriced this plan is.
Hmm, I'd have expected it to discount the repeated indexscans a lot more
than it seems to be doing for you. As an example in the regression
database, note what happens to the inner indexscan cost estimate when
the number of outer tuples grows:
regression=# set enable_hashjoin TO 0;
SET
regression=# set enable_mergejoin TO 0;
SET
regression=# set enable_bitmapscan TO 0;
SET
regression=# explain select * from tenk1 a join tenk1 b using (thousand) where a.unique1 = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..52.82 rows=10 width=484)
-> Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..8.27 rows=1 width=244)
Index Cond: (unique1 = 1)
-> Index Scan using tenk1_thous_tenthous on tenk1 b (cost=0.00..44.42 rows=10 width=244)
Index Cond: (a.thousand = b.thousand)
(5 rows)
regression=# explain select * from tenk1 a join tenk1 b using (thousand) where a.ten = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..2531.08 rows=9171 width=484)
-> Seq Scan on tenk1 a (cost=0.00..483.00 rows=900 width=244)
Filter: (ten = 1)
-> Index Scan using tenk1_thous_tenthous on tenk1 b (cost=0.00..2.15 rows=10 width=244)
Index Cond: (a.thousand = b.thousand)
(5 rows)
This is with 8.2.4 but AFAICS from the CVS logs, 8.2's cost estimation
code didn't change since 8.2.1. What do you get for a comparably
simple case?
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter_e@gmx.net
Subject: Re: Nested loops overpriced
In-Reply-To: <1987.1178639593@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