Re: Per-table random_page_cost for tables that we know are always cached - Mailing list pgsql-hackers

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.