Re: RFC: built-in historical query time profiling

Ed L. wrote:
> Hackers,
> (some snippage...)
> Our Problem: We work with 75+ geographically distributed pg
> clusters; it is a significant challenge keeping tabs on
> performance. We see degradations from rogue applications,
> vacuums, dumps, bloating indices, I/O and memory shortages, and
> so on. Customers don't generally tell us when applications are
> slow, so we need to know for ourselves in a timely manner. At
> present, we can remotely and systematically query system
> relations for diskspace usage, detailed I/O usage,
> index/sequential scans, and more. But our _ultimate_ DB
> performance measure is query execution time. Obviously, you can
> measure that now in an ad hoc fashion with EXPLAIN ANALYZE, and
> by examining historical logs. But we need to be able to see the
> history in a timely fashion to systematically identify
> customer-experienced execution time degradations for "query
> patterns of interest" without any visual log inspection
> whatsoever, and correlate those with other events.

Is enabling the various postgresql.conf stats* options and taking
regular snapshots of pg_stat_activity a possible way to get this?

regards

Mark