> 1) Statistics kept on all the tables and indexes showing the number of
> times and the number of milliseconds spent sequential scanning / index
> scanning them. You could easily identify an index that's not being used
> or a table that's being sequential scanned too often. Also supply a
> database command to reset the statistic gather to zero.
Enable the stats collector in your postgresql.conf, and then look at the
pg_stat_* views. They are your friend.
> 2) Statics that show count/total times to insert into/updating/deleting
> from a table with time times spent in table and index separated so you
> could see how much the indexes are effecting the write performance on
> the table.
Lots of that is in the pg_statio_* tables (if you have the collector
running)
> 3) The ability to view the current running time, plan and SQL text of
> all currently running queries.
That's in the pg_stat_activity table.
There's also a new postgresql.conf setting in 7.4 that lets you log any
query that runs more than a certain number of milliseconds - which is
very useful for tracking down your slow queries.
Chris