Dennis,
> I'm running into a quite puzzling simple example where the index I've
> created on a fairly big table (465K entries) is not used, against all common
> sense expectations:
> The query I am trying to do (fast) is:
>
> select count(*) from addresses;
PostgreSQL is currently unable to use indexes on aggregate queries. This is
because of two factors:
1) MVCC means that the number of rows must be recalculated for each
connection's current transaction, and cannot be "cached" anywhere by the
database system;
2) Our extensible model of user-defined aggregates means that each aggregate
is a "black box" whose internal operations are invisible to the planner.
This is a known performance issue for Postgres, and I believe that a couple of
people on Hackers are looking at modifying aggregate implementation for 8.0
to use appropriate available indexes, at least for MIN, MAX and COUNT. Until
then, you will need to either put up with the delay, or create a
trigger-driven aggregates caching table.
If you are trying to do a correlated count, like "SELECT type, count(*) from
aggregates GROUP BY type", Tom Lane has already added a hash-aggregates
structure in the 7.4 source that will speed this type of query up
considerably for systems with lots of RAM.
(PS: in the future, please stick to posting questions to one list at a time,
thanks)
--
-Josh Berkus
Aglio Database Solutions
San Francisco