Re: per table random-page-cost?

On Fri, Oct 23, 2009 at 5:23 AM, Cédric Villemain
<cedric(dot)villemain(at)dalibo(dot)com> wrote:
> Le vendredi 23 octobre 2009 01:08:15, Joshua D. Drake a écrit :
>> On Thu, 2009-10-22 at 11:33 -0400, Robert Haas wrote:
>> > On Thu, Oct 22, 2009 at 11:16 AM, Cédric Villemain
>> >
>> > <cedric(dot)villemain(at)dalibo(dot)com> wrote:
>> > > Le lundi 19 octobre 2009 23:27:20, Greg Stark a écrit :
>> > >> On Mon, Oct 19, 2009 at 2:08 PM, marcin mank <marcin(dot)mank(at)gmail(dot)com>
> wrote:
>> > >> > Currently random_page_cost is a GUC. I propose that this could be
>> > >> > set per-table.
>> > >>
>> > >> Or per-tablespace.
>> > >>
>> > >> Yes, I think there are a class of GUCs which describe the physical
>> > >> attributes of the storage system which should be per-table or
>> > >> per-tablespace. random_page_cost, sequential_page_cost,
>> > >> effective_io_concurrency come to mind.
>> > >
>> > > and, perhaps effective_cache_size.
>> > >
>> > > You can have situation where you don't want some tables go to OS memory
>> > > (you can disabled that at filesystem level, ... l'd like to be able to
>> > > do that at postgres level but it is another point)
>> > >
>> > > So you put those tables in a separate tablespace, and tell postgresql
>> > > that the effective_cache_size is 0 (for this tablespace), up to
>> > > postgres to do the right thing with that ;)
>> >
>> > Why would you ever want to set effective_cache_size to 0?
>>
>> I think this is a misunderstanding of how effective_cache_size works. I
>> can't think of any reason to do that. I could see a reason to tell the
>> OS to not throw a relation into cache but that is a different thing.
>
> Well the effective_cache_size in this context is OS cache memory (0 in my case)
> + estimation of shared_buffer.. ah so DBA should estimate the amount in the
> shared_buffer only, ok.
>
> So consider effective_cache_size = 0 + what pg_buffer_cache will tell.
>
> My case is a table containing 29 GB of bytea in a database of 52 GB. Every row
> on the 29GB table is grab only few times. And it will just renew OS cache
> memory  every time (the server have only 8GB of ram).
> So when I remove this table (not the index) from the OS cache memory,  I keep
> more interesting blocks in the OS cache memory.
>
> And disk + raid are quick enought to bypass the OS cache memory for this
> tablespace.
>
>
> Are things a bit clearer and usage not so silly ?

Well, I think you're vastly overestimating the power of
effective_cache_size. effective_cache_size changes the planner's
estimation of how likely a repeated partial index scan is to find the
same block in cache. So it only affects
nested-loop-with-inner-indexscan plans, and if effective_cache_size is
set to a value larger than the size of the index (or maybe the
relation, I'm too lazy to go reread the code right now), one value is
as good as another. For a typical user, I think you could set
effective_cache_size to, say, a terabyte, and it wouldn't make a bit
of difference. Heck, why not 2TB.

As far as I can see, the only possible value of setting this knob to a
value other than positive-infinity is that if you have a huge dataset
that's not close to fitting in memory, this might cause the planner to
pick a merge join over a nested loop with inner indexscan, which might
be better if it makes the I/O sequential rather than random. Anyone
think I'm a pessimist?

...Robert