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

Peter Eisentraut <peter_e@gmx.net> writes:
>> Are you using any nondefault planner settings?

> random_page_cost = 3
> effective_cache_size = 384MB

>> How big are these tables, anyway?

> email		35 MB
> email_header	421 MB
> mime_part	37 MB

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 remember having dithered about whether
to try to avoid counting the same physical relation more than once in
total_table_pages, but this example certainly suggests that we
shouldn't.  Meanwhile, do the estimates get better if you set
effective_cache_size to 1GB or so?

To return to your original comment: if you're trying to model a
situation with a fully cached database, I think it's sensible
to set random_page_cost = seq_page_cost = 0.1 or so.  You had
mentioned having to decrease them to 0.02, which seems unreasonably
small to me too, but maybe with the larger effective_cache_size
you won't have to go that far.

			regards, tom lane