EDB Stat Monitor Features
Enhanced Statistics
Background: Time Buckets
Statistics are accumulated in EDB Stat Monitor (edb_stat_monitor) in the form of time intervals buckets.
This avoids issues with ever increasing counters and allows for a more manageable way to track query performance over time.
Aggregated information is stored within the bucket.
The system will first create a number of buckets (based on the parameter edbsm_max_buckets).
It will then collect information for the bucket for its lifetime (configured through parameter edbsm_bucket_time).
Once the time expires, it moves on to the next bucket.
Once all buckets are filled, it rewrites again from bucket 0.
The information in the buckets lasts until it's time for the bucket to be recycled.
SELECT bucket, bucket_start_time,substring(query,0,150), calls FROM edb_stat_monitor WHERE query LIKE '%pgbench_accounts%' ORDER BY bucket; bucket | bucket_start_time | substring | calls --------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------- 2 | 11-JUN-25 11:08:00 +05:30 | UPDATE pgbench_accounts SET abalance = abalance + 467 WHERE aid = 4258024 | 203 2 | 11-JUN-25 11:08:00 +05:30 | SELECT abalance FROM pgbench_accounts WHERE aid = 4258024 | 203 3 | 11-JUN-25 11:08:15 +05:30 | SELECT abalance FROM pgbench_accounts WHERE aid = 3324015 | 50 3 | 11-JUN-25 11:08:15 +05:30 | UPDATE pgbench_accounts SET abalance = abalance + -2709 WHERE aid = 3324015 | 50 (4 rows)
Enhanced Statistics with T-Digest
The edb_stat_monitor extension offers a more precise way to track the execution time of queries within a bucket using the T-Digest algorithm. This allows for more accurate percentile calculations.
Here are the related features: edb_stat_monitor.edbsm_td_compression: Controls the compression level of the T-Digest. The value ranges from 1 to 100, with a default of 100. Higher values provide more accuracy but may consume slightly more memory. Note: currently we are using static memory allocation and so there is no memory benefit.
edb_stat_monitor_td_quantile: This function (available in three variants, depending on parameter selection) returns quantile information for the specified data range.
edb_stat_monitor_td_histogram: This function (available in three variants, depending on parameter selection) generates a histogram representation of the T-Digest data.
| proname | Argument data types |
|---|---|
| edb_stat_monitor_td_histogram | _bucket bigint |
| edb_stat_monitor_td_histogram | _bucket bigint, _qid text |
| edb_stat_monitor_td_histogram | _bucket_start bigint, bucket_end bigint, queryid text |
| edb_stat_monitor_td_quantile | _quantile double, _bucket bigint |
| edb_stat_monitor_td_quantile | _quantile double, _bucket bigint, _qid text |
| edb_stat_monitor_td_quantile | _quantile double, _bucket_from bigint, _bucket_to bigint, _qid text |
Example Usage
50 percentile for a bucket 92
postgres=# SELECT * FROM edb_stat_monitor_td_quantile (0.50,92); edb_stat_monitor_td_quantile ------------------------------ 0.004830253637740545 (1 row)
99 percentile for a query in bucket 92
postgres=# SELECT * FROM edb_stat_monitor_td_quantile(0.99,92, '6584856920617530174'); edb_stat_monitor_td_quantile ------------------------------ 0.05314776 (1 row)
90 percentile for a query in bucket 71 to 73
postgres=# SELECT * FROM edb_stat_monitor_td_quantile(0.90,71,73, '6584856920617530174'); edb_stat_monitor_td_quantile ------------------------------ 0.036865426119980754 (1 row)
Get histogram for one bucket 61
SELECT * FROM edb_stat_monitor_td_histogram(61); exec_time | freq | bar ---------------------+------+------------------------------------------------------------------ ----------- 0.116834 | 1 | ######### 0.12825099999999998 | 1 | ######### 0.145689 | 2 | ######################## 0.1967432 | 5 | ################################################################# 0.3654195 | 4 | ##################################################### 0.4331 | 3 | ###################################### 0.548338 | 1 | ######### 0.6519199999999999 | 1 | ######### (8 rows)
Get histogram for one query id in bucket 51
postgres=# SELECT * FROM edb_stat_monitor_td_histogram(51, '6584856920617530174'); exec_time | freq | bar ----------------------+------+-------------------------------- 0.012374999999999999 | 1 | 0.014167 | 1 | 0.015365666666666665 | 9 | 0.020881586956521734 | 46 | # 0.02446471140939597 | 149 | #### 0.02696138578680203 | 591 | ################## 0.03259709365244537 | 961 | ############################## 0.04432158793969849 | 199 | ###### 0.0585399142857143 | 35 | # 0.08280583333333334 | 6 | 0.12212599999999998 | 1 | 0.12845900000000002 | 1 | (12 rows)
Get histogram for one query id in bucket 45 to 100
postgres=# SELECT * FROM edb_stat_monitor_td_histogram(45,100, '-799266892278171905'); td_means | td_calls | bar ---------------------+----------+--------------------------------------- 0.116001 | 1 | ####### 0.18021 | 1 | ####### 0.190147 | 2 | ############### 0.20317849999999998 | 4 | ############################## 0.20942639999999998 | 5 | ##################################### 0.22366825 | 4 | ############################## 0.2310225 | 2 | ############### 0.235543 | 1 | ####### 0.283835 | 1 | ####### (9 rows)
Could this page be better? Report a problem or suggest an addition!