Skip site navigation (1) Skip section navigation (2)

Re: More thoughts about planner's cost estimates

From: Mike Benoit <ipso(at)snappymail(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Rod Taylor <pg(at)rbt(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: More thoughts about planner's cost estimates
Date: 2006-06-03 19:15:31
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
pgbench appears to already support arbitrary SQL queries with the -f
switch, so why couldn't we just make it a little smarter and have people
enable SQL query logging for a day or two, then pass the log off to

pgbench -f <log file>

Seems to me like that wouldn't be too difficult to do, and would give
much closer "real-world" results than pgbench's built-in benchmark. 

On top of that the community could start offering up "template"
benchmarks like: "busy website", "data warehouse", "forums", "financial"
and distribute them with pgbench:

pgbench -f templates/data_warehouse.pgbench
pgbench -f templates/forums.pgbench

From that point a brute force auto-tune utility would be pretty straight
forward to write. 

pgautotune -f templates/data_warehouse.bench,myapp.sqllog

Or if one server runs multiple custom apps that you want to tune for:

pgautotune -f myapp1.sqllog,myapp2.sqllog,myapp3.sqllog

Even if it took 48hrs to run, it would be a good burn-in test for a
brand new server. ;)

On Fri, 2006-06-02 at 19:38 -0400, Tom Lane wrote:
> Rod Taylor <pg(at)rbt(dot)ca> writes:
> >> One objection to this is that after moving "off the gold standard" of
> >> 1.0 = one page fetch, there is no longer any clear meaning to the
> >> cost estimate units; you're faced with the fact that they're just an
> >> arbitrary scale.  I'm not sure that's such a bad thing, though.  For
> >> instance, some people might want to try to tune their settings so that
> >> the estimates are actually comparable to milliseconds of real time.
> > Any chance that the correspondence to time could be made a part of the
> > design on purpose and generally advise people to follow that rule?
> We might eventually get to that point, but I'm hesitant to try to do it
> immediately.  For one thing, I really *don't* want to get bug reports
> from newbies complaining that the cost estimates are always off by a
> factor of X.  (Not but what we haven't gotten some of those anyway :-()
> In the short term I see us sticking to the convention that seq_page_cost
> is 1.0 in a "typical" database, while anyone who's really hot to try to
> make the other happen is free to experiment.
> > If we could tell people to run *benchmark* and use those numbers
> > directly as a first approximation tuning, it could help quite a bit
> > for people new to PostgreSQL experiencing poor performance.
> We don't have such a benchmark ... if we did, we could have told
> people how to use it to set the variables already.  I'm very very
> suspicious of any suggestion that it's easy to derive appropriate
> numbers for these settings from one magic benchmark.
> 			regards, tom lane
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
Mike Benoit <ipso(at)snappymail(dot)ca>

In response to

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2006-06-03 19:18:14
Subject: Re: More thoughts about planner's cost estimates
Previous:From: Greg StarkDate: 2006-06-03 19:09:45
Subject: Re: COPY (query) TO file

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group