Re: database profiling

> 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