Re: Per-table random_page_cost for tables that we know are always cached

>> Example : let's imagine a "cache priority" setting.
>
> Which we can presume the DBA will set incorrectly because the tools  
> needed to set that right aren't easy to use.
LOL, yes.

> Jim threw out that you can just look at the page hit percentages  
> instead. That's not completely true.  If you've had some nasty query  
> blow out your buffer cache, or if the server has been up a looong time  
> and the total stas don't really reflect recent reality, what's in the  
> buffer cache and what the stats say have been historical cached can  
> diverge.
Yes :
- perform huge query on table A
- table A is now in cache
- perform huge query on table B
- table B is now in cache, A isn't
- perform huge query on table A again
- postgres still thinks table A is cached and chooses a bad plan

>> This would not examine whatever is in the OS' cache, though.
Yeah, but now that shared_buffers can be set to a large part of physical  
RAM, does it still matters ?Point is, postgres knows what is in the shared_buffers, so it can make a  
good decision. Postgres doesn't know what the OS has in cache, so it could  
only make a wild guess. I would rather err on the side of safety...

>
> I don't know that it's too unrealistic to model the OS as just being an  
> extrapolated bigger version of the buffer cache.  I can think of a  
> couple of ways those can diverge:
>
> 1) Popular pages that get high usage counts can end up with a higher  
> representation in shared_buffers than the OS
>
> 2) If you've being doing something like a bulk update, you can have lots  
> of pages that have been written recently in the OS cache that aren't  
> really accounted for fully in shared_buffers, because they never get a  
> high enough usage count to stay there (only used once) but can fill the  
> OS cache as they're spooled up to write.
Especially on CHECKPOINT