Re: RFC: built-in historical query time profiling

Ed L. wrote:
> On Wednesday March 23 2005 4:11, Mark Kirkwood wrote:
>
>>Is enabling the various postgresql.conf stats* options and
>>taking regular snapshots of pg_stat_activity a possible way to
>>get this?
>
>
> I don't see how; the duration is the key measurement I'm after,
> and I don't believe it is available anywhere but the logs.
>
>

I was thinking about something like :

- decide on a snapshot interval (e.g. 30 seconds)
- capture pg_stat_activity every interval and save the results in a
timestamped copy of this view (e.g. add a column 'snap_time')

You can then query this new table and find all those queries that are
taking a long time (you know a query is finished - modulo measurement
interval , if its no longer present in the snapshot).

Clearly the granularity limits the accuracy you can achieve, and there
could some confusion about when a query is finished if a session
repeatedly executes exactly the same query, but it would certainly bring
to attention any sudden change in query activity.

cheers

Mark