On Thu, Oct 31, 2019 at 12:51:17PM -0300, Igor Calabria wrote:
Hi everyone,
I was taking a look at pg_stat_statements module and noticed that it does
not collect any percentile metrics. I believe that It would be really handy
to have those available and I'd love to contribute with this feature.
The basic idea is to accumulate the the query execution times using an
approximation structure like q-digest or t-digest and add those results to
the pg_stat_statements table as fixed columns. Something like this
p90_time:
p95_time:
p99_time:
p70_time:
...
Another solution is to persist de digest structure in a binary column and
use a function to extract the desired quantile ilke this SELECT
approx_quantile(digest_times, 0.99) FROM pg_stat_statements
IMO having some sort of CDF approximation (being a q-digest or t-digest)
would be useful, although it'd probably need to be optional (mostly
becuase of memory consumption).
I don't see why we would not store the digests themselves. Storing just
some selected percentiles would be pretty problematic due to losing a
lot of information on restart. Also, pg_stat_statements is not a table
but a view on in-memory hash table.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
