To: Peter Eisentraut <peter_e@gmx.net>
cc: pgsql-performance@postgresql.org
Subject: Re: Nested loops overpriced 
In-reply-to: <200705101735.06509.peter_e@gmx.net> 
References: <200705081722.16731.peter_e@gmx.net>
	<200705091817.45056.peter_e@gmx.net>
	<28620.1178732406@sss.pgh.pa.us>
	<200705101735.06509.peter_e@gmx.net>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
	message dated "Thu, 10 May 2007 17:35:06 +0200"
Date: Thu, 10 May 2007 11:47:05 -0400
Message-ID: <14164.1178812025@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <peter_e@gmx.net> writes:
> Am Mittwoch, 9. Mai 2007 19:40 schrieb Tom Lane:
>> Hmmm ... I see at least part of the problem, which is that email_header
>> is joined twice in this query, which means that it's counted twice in
>> figuring the total volume of pages competing for cache space. So the
>> thing thinks cache space is oversubscribed nearly 3X when in reality
>> the database is fully cached.

> I should add that other, similar queries in this database that do not
> involve joining the same table twice produce seemingly optimal plans.
> (It picks hash joins which are actually faster than nested loops.)

It strikes me that in a situation like this, where the same table is
being scanned twice by concurrent indexscans, we ought to amortize the
fetches across *both* scans rather than treating them independently;
so there are actually two different ways in which we're being too
pessimistic about the indexscanning cost.

Difficult to see how to fix that in the current planner design however;
since it's a bottom-up process, we have to cost the individual scans
without any knowledge of what approach will be chosen for other scans.

			regards, tom lane