To: Peter Eisentraut <peter_e@gmx.net>
cc: pgsql-performance@postgresql.org
Subject: Re: Nested loops overpriced 
In-reply-to: <200705081722.16731.peter_e@gmx.net> 
References: <200705081722.16731.peter_e@gmx.net>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
	message dated "Tue, 08 May 2007 17:22:16 +0200"
Date: Tue, 08 May 2007 11:53:13 -0400
Message-ID: <1987.1178639593@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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