From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Steve McLellan <smclellan@mintel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance over a large proportion of data
Date: Tue, 10 Mar 2009 20:09:04 -0400
Message-ID: <17384.1236730144@sss.pgh.pa.us> (raw)
In-Reply-To: <49B69E1E.EE98.0025.0@wicourts.gov>
References: <1ca6422a7addad0207f758f54300dd0b.squirrel@semichrome.net>
<49B69E1E.EE98.0025.0@wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> You probably need to reduce random_page_cost. If your caching is
> complete enough, you might want to set it equal to seq_page_cost
> (never set it lower that seq_page_cost!) and possibly reduce both of
> these to 0.1.
> Some people have had good luck with boosting cpu_tuple_cost and
> cpu_index_tuple_cost. (I've seen 0.5 for both recommended.) I've
> never had to do that, but if the earlier suggestions don't get good
> plans, you might try that.
It might be worth pointing out here that all that matters are the
relative values of the various xxx_cost parameters. If your DB is
mostly or entirely cached, you probably want to lower the estimated cost
of I/O relative to CPU work. You can do that *either* by dropping the
seq_/random_page_costs, *or* by raising the cpu_xxx_costs (there are
more than two of those BTW). Doing both, as Kevin's comments might be
read to suggest, is not useful ... and in particular I bet that having
seq_page_cost actually less than cpu_tuple_cost would lead to some
pretty wacko decision-making by the planner.
See
http://www.postgresql.org/docs/8.3/static/runtime-config-query.html
for some more info about what you're twiddling here.
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, Kevin.Grittner@wicourts.gov, smclellan@mintel.com
Subject: Re: Query performance over a large proportion of data
In-Reply-To: <17384.1236730144@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