To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: "Steve McLellan" <smclellan@mintel.com>, pgsql-performance@postgresql.org
Subject: Re: Query performance over a large proportion of data 
In-reply-to: <49B69E1E.EE98.0025.0@wicourts.gov> 
References: <1ca6422a7addad0207f758f54300dd0b.squirrel@semichrome.net>
	<49B69E1E.EE98.0025.0@wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Tue, 10 Mar 2009 17:06:38 -0500"
Date: Tue, 10 Mar 2009 20:09:04 -0400
Message-ID: <17384.1236730144@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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