Re: random_page_cost vs seq_page_cost

On Tue, Feb 7, 2012 at 2:06 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> On 02/07/2012 03:23 PM, Bruce Momjian wrote:
>>
>> Where did you see that there will be an improvement in the 9.2
>> documentation?  I don't see an improvement.
>
>
> I commented that I'm hoping for an improvement in the documentation of how
> much timing overhead impacts attempts to measure this area better.  That's
> from the "add timing of buffer I/O requests" feature submission.  I'm not
> sure if Bene read too much into that or not; I didn't mean to imply that the
> docs around random_page_cost have gotten better.
>
> This particular complaint is extremely common though, seems to pop up on one
> of the lists a few times each year.  Your suggested doc fix is fine as a
> quick one, but I think it might be worth expanding further on this topic.
>  Something discussing SSDs seems due here too.  Here's a first draft of a
> longer discussion, to be inserted just after where it states the default
> value is 4.0:
>
> True random access to mechanical disk storage will normally be more
> expensive than this default suggests.  The value used is lower to reflect
> caching effects.  Some common random accesses to disk, such as indexed
> reads, are considered likely to be in cache.  The default value can be
> thought of as modeling random access as 40 times as expensive as sequential,
> while expecting that 90% of random reads will actually be cached.

For these numbers to work out to 4, we must also be assuming that
virtually zero of the sequentially read pages are cached. Is that a
realistic assumption? If the table is accessed only by seq scans, the
ring buffer may prevent it from getting cached (although even then it
could very well be the OS cache as that doesn't respect the ring
buffer), but it would be pretty common for other parts of the
application to access the same table via index scan, and so cause
substantial parts of it to be cached.

But I can see that that would rapidly get too complicated to discuss
in the documentation.

Cheers,

Jeff