From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim C. Nasby <decibel@decibel.org>
Cc: josh@agliodbs.com, Markus Schaber <schabios@logi-track.com>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: PostgreSQL Performance List <pgsql-performance@postgresql.org>
Subject: Re: Automagic tuning
Date: Tue, 01 Feb 2005 00:06:27 -0500
Message-ID: <10114.1107234387@sss.pgh.pa.us> (raw)
In-Reply-To: <20050201045211.GB32356@decibel.org>
References: <20040727151531.4f60b5b0@kingfisher.intern.logi-track.com>
<41066485.7080502@familyhealth.com.au>
<41FE4692.6080104@logi-track.com>
<200501311209.31827.josh@agliodbs.com>
<3900.1107203172@sss.pgh.pa.us>
<20050201045211.GB32356@decibel.org>
"Jim C. Nasby" <decibel@decibel.org> writes:
> On Mon, Jan 31, 2005 at 03:26:12PM -0500, Tom Lane wrote:
>> Preferably a whole lot of queries. All the measurement techniques I can
>> think of are going to have a great deal of noise, so you shouldn't
>> twiddle these cost settings based on just a few examples.
> Are there any examples of how you can take numbers from pg_stats_* or
> explain analize and turn them into configuration settings (such and
> random page cost)?
Well, the basic idea is to adjust random_page_cost so that the ratio of
estimated cost to real elapsed time (as shown by EXPLAIN ANALYZE) is the
same for seqscans and indexscans. What you have to watch out for is
that the estimated cost model is oversimplified and doesn't take into
account a lot of real-world factors, such as the activity of other
concurrent processes. The reason for needing a whole lot of tests is
essentially to try to average out the effects of those unmodeled
factors, so that you have a number that makes sense within the planner's
limited view of reality.
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, decibel@decibel.org, schabios@logi-track.com, chriskl@familyhealth.com.au
Subject: Re: Automagic tuning
In-Reply-To: <10114.1107234387@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