Re: database profiling - Mailing list pgsql-performance

From Christopher Kings-Lynne
Subject Re: database profiling
Date
Msg-id 400DC6D7.1040705@familyhealth.com.au
Whole thread Raw
In response to database profiling  (Orion Henry <orion@trustcommerce.com>)
List pgsql-performance
> 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


pgsql-performance by date:

Previous
From: Orion Henry
Date:
Subject: database profiling
Next
From: Jeroen Baekelandt
Date:
Subject: Really slow even after vacuum