Re: detecting poor query plans

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> 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).

I wonder, perhaps we could add a "certainty" parameter to the
estimated query plan + result sizes + costs produced by the
planner. That way, when we run into a planner deficiency we can
basically mark the relevant portion of the query tree as a WAG, and
not bother with emitting hints for it.

> In some situations that doesn't really matter, as the same plan
> would have gotten picked anyway.

The hint is NOT "the chosen plan was non-optimal"; the hint is "the
query planner did not produce an accurate row count estimate for this
node in the query tree." The chosen query plan may or may not be
optimal -- we're merely pointing out that we chose the plan we did on
shakey grounds. The hint might just as well indicate a problem with
another query that happens to apply a similar predicate to the column
in question.

> 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.

Perhaps, but is there a reasonable way to answer the second question?

-Neil