To: Peter Eisentraut <peter_e@gmx.net>
cc: pgsql-performance@postgresql.org
Subject: Re: Nested loops overpriced 
In-reply-to: <200705091358.25748.peter_e@gmx.net> 
References: <200705081722.16731.peter_e@gmx.net>
	<1987.1178639593@sss.pgh.pa.us>
	<200705091358.25748.peter_e@gmx.net>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
	message dated "Wed, 09 May 2007 13:58:25 +0200"
Date: Wed, 09 May 2007 10:11:36 -0400
Message-ID: <26359.1178719896@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <peter_e@gmx.net> writes:
> Am Dienstag, 8. Mai 2007 17:53 schrieb Tom Lane:
>> 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:

> I can reproduce your results in the regression test database. 8.2.1 and 8.2.4 
> behave the same.

Well, there's something funny going on here.  You've got for instance

           ->  Index Scan using email_pkey on email  (cost=0.00..3.85 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=280990)
                 Index Cond: (email.email_id = eh_from.email_id)
                 Filter: (("time" >= '2007-05-05 17:01:59'::timestamp without time zone) AND ("time" < '2007-05-05 18:01:59'::timestamp without time zone))

on the inside of a nestloop whose outer side is predicted to return
107156 rows.  That should've been discounted to *way* less than 3.85
cost units per iteration.

Are you using any nondefault planner settings?  How big are these
tables, anyway?

			regards, tom lane