Slow aggergate queries in PostgreSQL
Postgres is strange. If you make a query
select min(column_with_index) from table;
the database performs a sequential scan – you can only imagine how long it takes on a 10M record database. This seems to be a feature (!@!#!@), but indices can will used if you use the following workaround:
select column_with_index from table order by 1 (desc) limit 1;
This trick is explained here.
November 27th, 2005 at 4:29 pm
With the new PostgreSQL 8.1 aggregates (min() and max()) use indices as they are supposed to.
Another new thing are bitmap incides, which should reduce the need for multicolumn indices in many cases.