Re: per table random-page-cost?

On Mon, Oct 19, 2009 at 5: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.
>
> I think this is a good idea for widely-wanted planner hints. This way
> You can say "I do NOT want this table to be index-scanned, because I
> know it is not cached" by setting it`s random_page_cost to a large
> value (an obviously You can do the other way around, when setting the
> random_page_cost to 1 You say "I don`t care how You fetch the pages,
> they are all in cache")
>
> The value for the per-table setting could be inferred from
> pg_stat(io)?.*tables . We could have a tool to suggest appropriate
> values.
>
> We could call it something like cached_percentage (and have the cost
> of a random tuple fetch be inferred from the global random_page_cost,
> seq_tuple_cost and the per-table cached_percentage). Then we could set
> the global random_page_cost to a sane value like 200. Now one can
> wonder why the planner works while having such blantantly unrealistic
> values for random_page_cost :)
>
> What do You think?

I've been thinking about this a bit, too. I've been wondering if it
might make sense to have a "random_page_cost" and "seq_page_cost"
setting for each TABLESPACE, to compensate for the fact that different
media might be faster or slower, and a percent-cached setting for each
table over top of that.

...Robert