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@gregsmith.com http://www.gregsmith.com Baltimore, MD