Re: detecting poor query plans

Neil Conway <neilc(at)samurai(dot)com> writes:
> It occurred to me that these kinds of poor planning decisions could
> easily be detected by PostgreSQL itself: after we've finished
> executing a plan, we can trivially compare the # of results produced
> by each node in the query tree with the # of results the planner
> expected that node to produce (look at EXPLAIN ANALYZE, for
> example). If the estimate is off by a significant margin (say, 300%),
> we could perhaps emit a HINT suggesting that the user re-run ANALYZE

I think such a thing would have such a low signal-to-noise ratio as to
be useless :-(. As you note, there are many places where the planner's
estimate is routinely off by more than 3x (or any other threshold you
might pick instead). In some situations that doesn't really matter,
as the same plan would have gotten picked anyway. Also, since 7.2 came
out what we've seen more and more is cases where the row count estimate
is acceptably good, but the wrong plan was picked anyway because of
deficiencies in the cost equations.

The question you really want to know about is not whether the row count
estimate is close, it's whether another plan could have done better.

regards, tom lane