Thread: Checking query results against selectivity estimate

I don't recall, has it ever been considered to compare the number of
actual result rows against the estimate computed by the optimizer and then
draw some conclusions from it?  Both numbers should be easily available.
Possible "conclusions" might be suggesting running analyze, suggesting
tweaking "M" and "K", assigning an out-of-whack factor to the statistics
(once it goes to infinity (or zero) you do one of the previous things), in
some future life possibly automatically switching to an alternative
statistics model.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:
> I don't recall, has it ever been considered to compare the number of
> actual result rows against the estimate computed by the optimizer and then
> draw some conclusions from it?  Both numbers should be easily available.

It's been suggested, but doing anything with the knowledge that you
guessed wrong seems to be an AI project, the more so as the query gets
more complex.  I haven't been able to think of anything very productive
to do with such a comparison (no, I don't like any of your suggestions
;-)).  Which parameter should be tweaked on the basis of a bad result?
If the real problem is not a bad parameter but a bad model, will the
tweaker remain sane, or will it drive the parameters to completely
ridiculous values?

The one thing that we *can* recommend unreservedly is running ANALYZE
more often, but that's just a DB administration issue, not something you
need deep study of the planner results to discover.  In 7.2, both VACUUM
and ANALYZE should be sufficiently cheap/noninvasive that people can
just run them in background every hour-or-so...
        regards, tom lane