From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
Date: Fri, 13 May 2011 13:13:41 -0700
Message-ID: <4DCD90F5.2010501@agliodbs.com> (raw)
In-Reply-To: <BANLkTimTqXAy7_KqQz_OW1RvMbDd9ojfqg@mail.gmail.com>
References: <BANLkTikZhYc+h1=YDJfXwZEZc1tN3_VwCg@mail.gmail.com>
<4DB6F4BB020000250003CEB9@gw.wicourts.gov>
<BANLkTindB68ogMg8zrR7GrYEV+mQQddj0A@mail.gmail.com>
<BANLkTik6OaVkJGEjs5--vcynUE36mWbzbg@mail.gmail.com>
<2823.1305314437@sss.pgh.pa.us>
<BANLkTimTqXAy7_KqQz_OW1RvMbDd9ojfqg@mail.gmail.com>
> I guess maybe the reason why it didn't matter for the OP is that - if
> the size of the index page in pages is smaller than the pro-rated
> fraction of effective_cache_size allowed to the index - then the exact
> value doesn't affect the answer.
>
> I apparently need to study this code more.
FWIW: random_page_cost is meant to be the ratio between the cost of
looking up a single row as and index lookup, and the cost of looking up
that same row as part of a larger sequential scan. For specific
storage, that coefficient should be roughly the same regardless of the
table size. So if your plan for optimization involves manipulating RPC
for anything other than a change of storage, you're Doing It Wrong.
Instead, we should be fixing the formulas these are based on and leaving
RPC alone.
For any data page, there are actually four costs associated with each
tuple lookup, per:
in-memory/seq | on disk/seq
----------------+----------------
in-memory/random| on disk/random
(yes, there's actually more for bitmapscan etc. but the example holds)
For any given tuple lookup, then, you can assign a cost based on where
you think that tuple falls in that quadrant map. Since this is all
probability-based, you'd be assigning a cost as a mixed % of in-memory
and on-disk costs. Improvements in accuracy of this formula would come
through improvements in accuracy in predicting if a particular data page
will be in memory.
This is what the combination of random_page_cost and
effective_cache_size ought to supply, but I don't think it does, quite.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
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: josh@agliodbs.com
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
In-Reply-To: <4DCD90F5.2010501@agliodbs.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