DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=VxWPcoKxI0n2MYOHxYvVVS0jOCmvOj58zoxcTD+y2V9MKW9NCDAmi0Yc8/JasTpLFw
	Wi3xMaqvghiGBLtnnufKhoxB6LzLUnTi/rRJbfd1qw+NI8/5d3D+SRL7JhsYq17bYf4N
	rybkgOJVZKCGlaqMK9zTqIm3kNW3w1gp6L+SQ=
MIME-Version: 1.0
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>
From: =?ISO-8859-1?Q?C=E9dric_Villemain?= <cedric.villemain.debian@gmail.com>
Date: Fri, 13 May 2011 22:51:08 +0200
Message-ID: <BANLkTimpyaL5UAcu_BcuMBS0+AgTSQJr+Q@mail.gmail.com>
Subject: Re: reducing random_page_cost from 4 to 2 to force index scan
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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. =A0For specific
> storage, that coefficient should be roughly the same regardless of the
> table size. =A0So 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 =A0 | 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. =A0but 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. =A0Since this is all
> probability-based, you'd be assigning a cost as a mixed % of in-memory
> and on-disk costs. =A0Improvements 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
>


--=20
C=E9dric Villemain=A0 =A0 =A0 =A0 =A0 =A0 =A0=A0 2ndQuadrant
http://2ndQuadrant.fr/=A0 =A0=A0 PostgreSQL : Expertise, Formation et Suppo=
rt