Mladen Gogala wrote:
People are complaining about the optimizer not using the indexes all over the place, there should be a way to make the optimizer explicitly prefer the indexes, like was the case with Oracle's venerable RBO (rules based optimizer). RBO didn't use statistics, it had a rank of access method and used the access method with the highest rank of all available access methods. In practice, it translated into: if an index exists - use it.
Given that even Oracle kicked out the RBO a long time ago, I'm not so sure longing for those good old days will go very far. I regularly see queries that were tweaked to always use an index run at 1/10 or less the speed of a sequential scan against the same data. The same people complaining "all over the place" about this topic are also the sort who write them. There are two main fallacies at play here that make this happen:
1) Even if you use an index, PostgreSQL must still retrieve the associated table data to execute the query in order to execute its version of MVCC
2) The sort of random I/O done by index lookups can be as much as 50X as expensive on standard hard drives as sequential, if every block goes to physical hardware.
If I were to work on improving this area, it would be executing on some plans a few of us have sketched out for exposing some notion about what indexes are actually in memory to the optimizer. There are more obvious fixes to the specific case of temp tables though.
-- Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
