From: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested loops overpriced
Date: Thu, 10 May 2007 17:30:22 +0200
Message-ID: <200705101730.22883.peter_e@gmx.net> (raw)
In-Reply-To: <28620.1178732406@sss.pgh.pa.us>
References: <200705081722.16731.peter_e@gmx.net>
<200705091817.45056.peter_e@gmx.net>
<28620.1178732406@sss.pgh.pa.us>
Am Mittwoch, 9. Mai 2007 19:40 schrieb Tom Lane:
> 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?
Yes, that makes the plan significantly cheaper (something like 500,000 instead
of 5,000,000), but still a lot more expensive than the hash join (about
100,000).
> 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.
Heh, when I decrease these parameters, the hash join gets cheaper as well. I
can't actually get it to pick the nested-loop join.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
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: peter_e@gmx.net, tgl@sss.pgh.pa.us
Subject: Re: Nested loops overpriced
In-Reply-To: <200705101730.22883.peter_e@gmx.net>
* 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