From: Greg Stark <gsstark@mit.edu>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: What about utility to calculate planner cost constants?
Date: 22 Mar 2005 12:30:05 -0500
Message-ID: <87hdj3bnc2.fsf@stark.xeocode.com> (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:
> Are you certain it's a linear system?
If you just consider the guc parameters that tell postgres how long various
real world operations take (all the *_cost parameters) then it's a linear
system. It has to be. The resulting time is just a sum of the times for some
number of each of these real world operations.
If you include parameters like the geqo_* parameters or the hypothetical
parameter that controls what selectivity to assume for clauses with unknown
selectivity then no, it wouldn't be.
But if you assume the estimated row counts are correct and you're just trying
to solve for the parameters to come up with the most accurate cost for the
current hardware then I think you're golden.
> There might well be some results to be gotten out of a linear
> approximation; the Grand Challenge is to come up with the model in the
> first place...
Indeed. The model's not perfect now of course, and it'll never really be
perfect since some of the parameters represent operations that aren't always a
consistent cost. But you should be able to solve for the values that result in
the most accurate totals the most often. There may be some tradeoffs (and
therefore new guc variables :)
PS
It occurs to me that there's no reason to use the unreliable EXPLAIN counts of
the costs. You may as well account accurately for them and use the actual
values used in performing the query. This means there's no reason to discard
inaccurately estimated data points.
Moreover, the overhead issue a non-issue. Since you only need the total time,
and the total costs. You would have the overhead of performing lots of
increments on those costs, but you only have to do two gettimeofdays. Once at
the beginning and once at the end.
--
greg
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, cbbrowne@acm.org
Subject: Re: What about utility to calculate planner cost constants?
In-Reply-To: <87hdj3bnc2.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