Re: Nested loops overpriced

Peter Eisentraut <peter_e(at)gmx(dot)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