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

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.

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.  In particular we *know* that the cost model for
nestloop-inner-indexscan joins is wrong, because it doesn't account for
cacheing effects across repeated scans.  There are some other obvious
weak spots as well.  It could be argued that we ought to allow the
system to assume index cacheing even for standalone queries, on the
grounds that if you are doing a query often enough to care about it,
there was probably a recent use of the same query that pulled in the
upper index levels.  The current cost models all assume starting from
ground zero with empty caches for each query, and that is surely not
reflective of many real-world cases.

I've looked at fixing this a couple times, but so far my attempts
to devise a more believable index access cost estimator have come
out with numbers higher than the current estimates ... not the
direction we want it to go :-(

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.

			regards, tom lane



view thread (21+ messages)  latest in thread

Message-ID: <10759.1111516477@sss.pgh.pa.us>
Permalink:  ../10759.1111516477@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10759.1111516477@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, cbbrowne@acm.org
  Subject: Re: What about utility to calculate planner cost constants?
  In-Reply-To: <10759.1111516477@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