public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Greg Smith <[email protected]>
Cc: [email protected]
Subject: Re: Volunteer to build a configuration tool
Date: Wed, 20 Jun 2007 03:06:27 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Greg Smith <[email protected]> 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

view thread (33+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Volunteer to build a configuration tool
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox