Re: detecting poor query plans

Greg Stark <gsstark@mit.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
expectedruntime 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
exacttime 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
invokesa function that happens to        occasionally block waiting on some resource, the execution        time of the
querycould be wildly unpredictable.
 
    (3) ISTM we couldn't produce a really helpful hint message, even        if we somehow resolved #1 and #2

-Neil