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