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@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books