From: Jeff Hoffmann <jeff@propertykey.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ryan Bradetich <ryan_bradetich@hp.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use of index in 7.0 vs 6.5
Date: Thu, 25 May 2000 10:30:57 -0500
Message-ID: <392D4731.A8624F8C@propertykey.com> (raw)
References: <392C6056.D5BF7FE8@hp.com>
<21530.959235278@sss.pgh.pa.us>
Tom Lane wrote:
> One way to put a thumb on the scales is to reduce the value of the SET
> variable random_page_cost. The default value is 4.0, which seems to
> correspond more or less to reality, but reducing it to 3 or so would
> shift the planner pretty nicely in the direction of indexscans.
>
like you may know, i've gone through a lot of things related to cost
estimates & i've convinced myself that (on paper) they're pretty good
(other than selectivity estimates, of course). this may be heavily
influenced by my choice of OS & the corresponding disk cache management
scheme, but it seems to me that the sequential scan cost is a lot closer
to reality because cache doesn't come into play as much as compared to
the indexed scans which (probably) are heavily influence by disk
cache. unfortunately, you'd have to simulate some very high load
conditions to negate the disk cache effect and convince people that the
numbers are actually fairly correct. even though i like to think i have
very important & demanding, end-all, be-all applications and that
postgresql should cater to my situation, i, like almost all of the other
users, operate in a very low load environments for the most part.
therefore, you may want to consider placing the defaults where they may
be more advantageous to the casual user (or at least to the user which
has a small number of backends & is fairly dedicated to operating as a
database server)
anyway, it seems to me that if you're going to fudge a number,
random_page_cost is probably the best place to do it because it factors
so heavily in the total cost. fudging things in the selectivity can be
frustrating because you can fudge for a certain table distribution, but
who's to say that the same table distribution will be the same for all
tables? since the selectivity doesn't have a lot of real-world
relevance, what do you gain in a general case by fudging it? at least
messing with random_page_cost, you know that on paper the value of 4 is
reasonably close to reality and that you're factoring in disk cache,
which should be fairly constant on your system (assuming a similar
volume of disk accesses).
on another topic, is there a list somewhere of the variables that you
can adjust with a SET command? some of the variables have been
invaluable in learning about cost estimates, but i can't remember seeing
a list of them documented anywhere -- i've just stumbled onto them, more
or less.
jeff
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jeff@propertykey.com, tgl@sss.pgh.pa.us, ryan_bradetich@hp.com
Subject: Re: Use of index in 7.0 vs 6.5
In-Reply-To: <392D4731.A8624F8C@propertykey.com>
References: <392C6056.D5BF7FE8@hp.com> <21530.959235278@sss.pgh.pa.us> <392D4731.A8624F8C@propertykey.com>
* 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