On Mon, Nov 5, 2012 at 12:54 AM, Christoffer Lernö
<christoffer@aegik.com> wrote:
> • Top Queries - See which queries are being run the most on your server.
> • Top Users - See which users are using your server the most.
> • Top Tables - See which database tables are opened the most.
> • Top States - See which states your database is most busy doing, such as creating temp tables.
> • Top IPs - See which client IPs are using your server the most.
You probably also want to see top *and bottom* indexes, as maintaining
indexes in postgresql not only hurts insert performance, but it also
impairs HOT updates, which makes it much more important to remove
unused indexes than in MySQL.
You have access to lots of statistics for all relations (tables,
indexes and such), which can also help refine your reports.
Personally, I routinely check "long-lived transactions" and the
queries that generate them, as they're a constant pain with MVCC, by
sorting pg_stat_activity by transaction start time, and checking the
first few transaction if they've been started "long ago" (with "long"
varying from application to application)