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

On Apr 22, 2008, at 5:13 AM, PFC wrote:
> In order to have it use the fast plan I must set random_page_cost
> to 1 which I absolutely don't want to do.
> Setting effective_cache_size to huge values has no effect.
> If I select a value of parent_id that has much less children, the
> index will be used, but in this case I think the threshold is
> misplaced, it should be slightly higher. Here we have about 5% of
> values selected. Hash join becomes better at about 15% because the
> table is cached.
> This is 8.3.
>
> Perhaps there would be a need for a per-object setting
> (object=table,index,partition) to alter the aggressiveness/lazyness
> of the page flushing and how long the pages for this object are
> kept in shared_buffers... this would be used to modify
> random_page_cost on a per-table/index/partition basis.
>
> Example : let's imagine a "cache priority" setting.
>
> - "cache priority" set to the minimum means this table is mostly
> write-only
> - "cache priority" set to default would give current behaviour
> (which is correct in most cases)
> - "cache priority" set to a high value would tell Postgres "I know
> this table/index/partition is small and often accessed rather
> randomly, so I want you to keep it in shared_buffers, purge it if
> you must but otherwise keep it in memory, flush something else
> instead which has lower cache_priority".
>
> The optimizer could then use a different (much lower) value of
> random_page_cost for tables for which "cache priority" is set
> highest since it would know.

"cache priority" to me sounds like we're trying to influence caching
behavior, which isn't what's happening. I do agree that we need a
better way to tell the planner what tables are in memory.

> An alternative would be for the background writer to keep some
> stats and do the thing for us :
>
> - begin bgwriter scan
> - setup hashtable of [relid => page count]
> - at each page that is scanned, increment "page count" for this
> relation (uses very little CPU)
> - end bgwriter stats
> - for each relation, compare the number of pages we found in
> shared_buffers with the number of pages in the relation and draw
> conclusions about how well cached the relation is
> - update random_page_cost accordingly for this relation
>
> This would not examine whatever is in the OS' cache, though.

Actually, there's no need for bgwriter to do that; we can just look
at the hit rate for the object. But we'd also need stats for how
often we find pages for a relation in the OS cache, which no one has
come up with a good method for.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828