A fair number of the performance problems posted to mailing lists are
the result of the query planner choosing a poor query plan. Some of
these instances are due to a defect in PostgreSQL (e.g. index type
comparisons), but many of them are caused by inaccurate statistics:
either ANALYZE has not been run recently, or the statistics target for
this column needs to be raised.
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
or raise the statistics target for the relevant column. We can add a
GUC to control whether any of these messages are emitted at all, or
perhaps the degree of estimation error that is deemed acceptable.
One potential problem might involve queries that the planner will
almost *never* do a good job on (see [1] for example). Until we get
around to improving the planner, there isn't much universally-
applicable advice we can offer in these situations: running ANALYZE
all day or setting the statistics target arbitrarily high won't
significantly improve the chosen query plan, so you'd continue to see
the hint described above.
BTW, this situation is a simple example of the general technique of
using feedback from the executor to improve query optimization
decisions. If you're interested, there is plenty of discussion of this
topic in the DBMS literature -- I'd be happy to provide pointers to
papers. It might be cool to try implementing some of this stuff for
PostgreSQL in the future.
Comments?
-Neil
[1] http://www.mail-archive.com/pgsql-performance%40postgresql.org/msg02415.html
