pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: What about utility to calculate planner cost constants?
Date: 22 Mar 2005 16:28:18 -0500
Message-ID: <87is3j9xql.fsf@stark.xeocode.com> (raw)
In-Reply-To: <10759.1111516477@sss.pgh.pa.us>
References: <A66A11DBF5525341AEF6B8DE39CDE77008805C@black.aprote.com>
	<200503210951.06044.josh@agliodbs.com>
	<87acowcmut.fsf@stark.xeocode.com>
	<m34qf3aktn.fsf@knuth.knuth.cbbrowne.com>
	<10759.1111516477@sss.pgh.pa.us>


Tom Lane <tgl@sss.pgh.pa.us> writes:

> Christopher Browne <cbbrowne@acm.org> writes:
> > Martha Stewart called it a Good Thing when gsstark@mit.edu (Greg Stark) wrote:
> >> It's just a linear algebra problem with a bunch of independent
> >> variables and a system of equations. Solving for values for all of
> >> them is a straightforward problem.
> 
> > Are you certain it's a linear system?  I'm not.
> 
> I'm quite certain it isn't a linear system, because the planner's cost
> models include nonlinear equations.

The equations will all be linear for the *_cost variables. If they weren't
they would be meaningless, the units would be wrong. Things like caching are
just going to be the linear factors that determine how many random page costs
and sequential page costs to charge the query.

> While I don't have a whole lot of hard evidence to back this up, my
> belief is that our worst problems stem not from bad parameter values
> but from wrong models.  

I think these are orthogonal issues. 

The time spent in real-world operations like random page accesses, sequential
page accesses, cpu operations, index lookups, etc, are all measurable
quantities. They can be directly measured or approximated by looking at the
resulting net times. Measuring these things instead of asking the user to
provide them is just a nicer user experience.

Separately, plugging these values into more and more accurate model will come
up with better estimates for how many of these operations a query will need to
perform.

> Anyway, I see little point in trying to build an automatic parameter
> optimizer until we have cost models whose parameters are more stable
> than the current ones.

Well what people currently do is tweak the physical values until the produce
results for their work load that match reality. It would be neat if postgres
could do this automatically.

Arguably the more accurate the cost model the less of a motivation for
automatic adjustments there is since you could easily plug in accurate values
from the hardware specs. But actually I think it'll always be a nice feature.

-- 
greg




view thread (21+ messages)  latest in thread

Message-ID: <87is3j9xql.fsf@stark.xeocode.com>
Permalink:  ../87is3j9xql.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87is3j9xql.fsf@stark.xeocode.com

 · 

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: gsstark@mit.edu, tgl@sss.pgh.pa.us, cbbrowne@acm.org
  Subject: Re: What about utility to calculate planner cost constants?
  In-Reply-To: <87is3j9xql.fsf@stark.xeocode.com>

* 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