On Fri, Apr 29, 2011 at 11:37 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Anyway, how to collect this data is a separate problem from what should be
> done with it in the optimizer. I don't actually care about the collection
> part very much; there are a bunch of approaches with various trade-offs.
> Deciding how to tell the optimizer about what's cached already is the more
> important problem that needs to be solved before any of this takes you
> somewhere useful, and focusing on the collection part doesn't move that
> forward. Trying to map the real world into the currently exposed parameter
> set isn't a solvable problem. We really need cached_page_cost and
> random_page_cost, plus a way to model the cached state per relation that
> doesn't fall easily into feedback loops.
This is valuable input...
I was already worried about feedback loops, and hearing that it has
been tried and resulted in them is invaluable.
From my experience, what really blows up in your face when your
servers are saturated, is the effective cache size. Postgres thinks an
index will fit into the cache, but it doesn't at times of high load,
meaning that, actually, a sequential scan would be orders of magnitude
better - if it's a "small enough table".
Perhaps just adjusting effective cache size would provide a good
enough benefit without the disastrous feedback loops?
I'll have to test that idea...