Re: What about utility to calculate planner cost constants?

Tambet,

> I was following the cpu_tuple_cost thread and wondering, if it could be
> possible to make PQA style utility to calculate configuration-specific
> values for planner cost constants. It could make use of output of
> log_(statement|parser|planner|executor)_stats, tough I'm not sure if the
> output contains anything useful for those purposes.

Yeah, that's something I need to look at.

> Otherwise it could just collect statements, run EXPLAIN ANALYZE for all
> of them and then play with planner cost constants to get the estimated
> values as close as possible to actual values. Something like Goal Seek
> in Excel, if you pardon my reference to MS :).

That's not really practical. There are currently 5 major query tuning
parameters, not counting the memory adjustments which really can't be left
out. You can't realistically test all combinations of 6 variables.

> Somewhat similar project seems to be pgautotune from pgFoundry, but it
> only considers shared_buffers, sort_mem and vacuum_mem. And it seems to
> use synthetic data instead of actual database and actual statements from
> log. And it has been inactive for a while.

Yeah, pg_autotune is a dead project. Once we got OSDL able to run tests, we
came up with some rules-of-thumb which are more useful than autotune's
output. More importantly, the approach doesn't scale to the 15-20 GUCs which
we'd realistically want to test.

--
Josh Berkus
Aglio Database Solutions
San Francisco