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 13:40:06 -0400
Message-ID: <28620.1178732406@sss.pgh.pa.us> (raw)
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>
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
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: <28620.1178732406@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