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: Wed, 09 May 2007 10:11:36 -0400
Message-ID: <26359.1178719896@sss.pgh.pa.us> (raw)
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>
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
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: <26359.1178719896@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