Greg Smith <gsmith@xxxxxxxxxxxxx> writes: > On Wed, 20 Jun 2007, Tom Lane wrote: >> I think what would be much more useful in the long run is some serious >> study of the parameters themselves. For instance, random_page_cost is a >> self-admitted oversimplification of reality. > If I could figure out who would sponsor such a study that's what I'd be > doing right now. Hmm ... Sun? EDB? Greenplum? [I'm afraid Red Hat is not likely to step up to the plate right now, they have other priorities] > Many of the tuning knobs on the query optimizer > seem very opaque to me so far, At least some of them are demonstrably broken. The issue here is to develop a mental model that is both simple enough to work with, and rich enough to predict real-world behavior. > Here's an example of one of the simplest questions in this area to > demonstate things I wonder about. Let's say I have a properly indexed > database of some moderate size such that you're in big trouble if you do a > sequential scan. How can I tell if effective_cache_size is in the right > ballpark so it will do what I want to effectively navigate that? As the guy who put in effective_cache_size, I'd say it's on the broken side of the fence. Think about how to replace it with a more useful parameter, not how to determine a good value for it. "Useful" means both "easy to determine a value for" and "strong for estimating query costs", which are contradictory to some extent, but that's the problem to be solved --- and effective_cache_size doesn't really win on either metric. To me, the worst catch-22 we face in this area is that we'd like the optimizer's choices of plan to be stable and understandable, but the real-world costs of queries depend enormously on short-term conditions such as how much of the table has been sucked into RAM recently by other queries. I have no good answer to that one. regards, tom lane
