Re: per table random-page-cost?

On Mon, 19 Oct 2009, Jeff Davis wrote:

> On Mon, 2009-10-19 at 21:22 -0500, Kevin Grittner wrote:
>> I'd bet accounts receivable applications often hit that.
>> (Most payments on recent billings; a sprinkling on older ones.)
>> I'm sure there are others.
>
> You worded the examples in terms of writes (I think), and we're talking
> about read caching, so I still don't entirely understand.

No, that part was fair. The unfortunate reality of accounts receivable is
that reports run to list people who owe one money happen much more often
than posting payments into the system does.

> Also, the example sounds like you'd like to optimize across queries.
> There's no mechanism for the planner to remember some query executed a
> while ago, and match it up to some new query that it's trying to plan.

Some of the use-cases here involve situations where you know most of a
relation is likely to be in cache just because there's not much going on
that might evict it. In any case, something that attempts to model some
average percentage you can expect a relation to be in cache is in effect
serving as a memory of past queries.

> I'm not clear on the scenario that we're trying to improve.

Duh, that would be the situation where someone wants optimizer hints but
can't call them that because then the idea would be reflexively rejected!

Looks like I should dust off the much more complicated proposal for
tracking and using in-cache hit percentages I keep not having time to
finish writing up. Allowing a user-set value for that is a lot more
reasonable if the system computes a reasonable one itself under normal
circumstances. That's what I think people really want, even if it's not
what they're asking for.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD