From: Cédric Villemain <cedric.villemain.debian@gmail.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
Date: Fri, 13 May 2011 22:51:08 +0200
Message-ID: <BANLkTimpyaL5UAcu_BcuMBS0+AgTSQJr+Q@mail.gmail.com> (raw)
In-Reply-To: <4DCD90F5.2010501@agliodbs.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>
<4DCD90F5.2010501@agliodbs.com>
2011/5/13 Josh Berkus <josh@agliodbs.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
it lacks some more theorical like sort_page/temp_page : those are
based on a ratio of seq_page_cost and random_page_cost or a simple
seq_page_cost (when working out of work_mem)
memory access is accounted with some 0.1 in some place AFAIR.
(and memory random/seq is the same at the level of estimations we do)
>
> (yes, there's actually more for bitmapscan etc. but the example holds)
(if I read correctly the sources, for this one there is a linear
approach to ponderate the cost between random_page cost and
seq_page_cost on the heap page fetch plus the Mackert and Lohman
formula, if needed, in its best usage : predicting what should be in
cache *because* of the current query execution, not because of the
current status of the page cache)
>
> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
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: cedric.villemain.debian@gmail.com, josh@agliodbs.com
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
In-Reply-To: <BANLkTimpyaL5UAcu_BcuMBS0+AgTSQJr+Q@mail.gmail.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