Re: Idea for the statistics collector

On Fri, Jun 21, 2002 at 12:47:18AM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > Since it's currently all for collecting statistics on tables, why can't it
> > collect another type of statistic, like:
> > - How often the estimator gets it wrong?
> > [snip]
> > Does anyone see any problems with this?
>
> (1) forced overhead on *every* query.
If yo don't want it, don't use it. The current statistics have the same
issue and you can not do those as well.

> (2) contention to update the same rows of pg_statistic (or wherever you
>     plan to store this info).

True, can't avoid that. Depends on how many queries you. Maybe only enable
it for specific sessions?

> (3) okay, so the estimate was wrong; exactly which of the many
>     parameters that went into the estimate do you plan to twiddle?
>     What if it's not the parameter values that are at fault, but the
>     cost-model equations themselves?

Firstly, I was only thinking of going for the basic nodes (Index Scan, Seq
Scan, Distinct). Other types have far more variables. Secondly, even if you
only count, it's useful. For example, if it tells you that the planner is
off by a factor of 10 more than 75% of the time, that's useful information
independant of what the actual variables are.

> Closed-loop feedback is a great thing when you understand the dynamics
> of the system you intend to apply feedback control to.  When you don't,
> it's a great way to shoot yourself in the foot.  Unfortunately I don't
> think the PG optimizer falls in the first category at present.

Using the results for planning is obviously a tricky area and should proceed
with caution. But just collecting statistics shouldn't be too bad?

See also -hackers.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.