Re: detecting poor query plans

Greg Stark <gsstark(at)mit(dot)edu> writes:
> There's a dual to this as well. If the results were very close but
> the actual time taken to run the node doesn't match the cost
> calculated then some optimizer parameter needs to be adjusted.

I was thinking about this, but I couldn't think of how to get it to
work properly:

(1) The optimizer's cost metric is somewhat bogus to begin with.
ISTM that translating a cost of X into an expected runtime of
Y msecs is definitely not trivial to do.

(2) The size of a node's result relation does not depend upon
anything outside of PostgreSQL, whereas the exact time it
takes to produce that result relation depends on a wide
collection of external factors. For example, if the system is
under heavy load, queries will take longer than normal to
run. Or, if the query invokes a function that happens to
occasionally block waiting on some resource, the execution
time of the query could be wildly unpredictable.

(3) ISTM we couldn't produce a really helpful hint message, even
if we somehow resolved #1 and #2

-Neil