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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
