pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim C. Nasby <decibel@decibel.org>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tuning planner cost estimates
Date: Fri, 20 May 2005 16:47:38 -0400
Message-ID: <28982.1116622058@sss.pgh.pa.us> (raw)
In-Reply-To: <20050520202017.GK44623@decibel.org>
References: <20050518002236.GU30902@decibel.org>
	<200505190931.47662.josh@agliodbs.com>
	<20050520202017.GK44623@decibel.org>

"Jim C. Nasby" <decibel@decibel.org> writes:
> On Thu, May 19, 2005 at 09:31:47AM -0700, Josh Berkus wrote:
>> can test our formula for accuracy and precision.  However, such a formula 
>> *does* need to take into account concurrent activity, updates, etc ... that 
>> is, it needs to approximately estimate the relative cost on a live database,
>> not a test one.

> Well, that raises an interesting issue, because AFAIK none of the cost
> estimate functions currently do that.

I'm unconvinced that it'd be a good idea, either.  People already
complain that the planner's choices change when they ANALYZE; if the
current load factor or something like that were to be taken into account
then you'd *really* have a problem with irreproducible behavior.

It might make sense to have something a bit more static, perhaps a GUC
variable that says "plan on the assumption that there's X amount of
concurrent activity".  I'm not sure what scale to measure X on, nor
exactly how this would factor into the estimates anyway --- but at least
this approach would maintain reproducibility of behavior.

> Another issue is: what state should the buffers/disk cache be in?

The current cost models are all based on the assumption that every query
starts from ground zero: nothing in cache.  Which is pretty bogus in
most real-world scenarios.  We need to think about ways to tune that
assumption, too.  Maybe this is actually the same discussion, because
certainly one of the main impacts of a concurrent environment is on what
you can expect to find in cache.

			regards, tom lane



view thread (11+ messages)  latest in thread

Message-ID: <28982.1116622058@sss.pgh.pa.us>
Permalink:  ../28982.1116622058@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28982.1116622058@sss.pgh.pa.us

 · 

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: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, decibel@decibel.org, josh@agliodbs.com
  Subject: Re: Tuning planner cost estimates
  In-Reply-To: <28982.1116622058@sss.pgh.pa.us>

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

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