K002, K003 for each metric, show ratio

Add the 4th line in each column – ratio, in %:

last_image

Formula

round(100 * value / sum(value)),

where sum(value) is sum of ALL observed values (not only for 50 lines, but for all observed by pg_stat_statements/kcache).

Warning on possible error related to difference in sets of query groups

When we work with two snapshots of pg_stat_statements/kcache, it is normal to see that they contain slightly different sets of query groups.

We need to have some estimate, showing how big is that difference, in terms of the two main metrics: calls and total_time.

How to get this estimate:

  • consider that we have two snapshots, A and B. Snapshot A has N query groups, snapshot B has M query groups (N might be equal to M, but not necessarily);
  • let's name I as intersection of A and B (I contains all query groups which both A and B have);
  • calculate sum(calls) and sum(total_time) for A, for B, and finally, for I;
  • for both metrics (calls and total_time) we now can get two numbers: the difference between sum for I and sum for A, and the difference between sum for I and sum for A. Let's name them diff1(calls), diff2(calls), diff1(total_time), diff2(total_time).
  • "absolute error" with respect to calls metric is calculated as: (diff1(calls) + diff2(calls)) / 2. For total_time: (diff1(total_time) + diff2(total_time)) / 2
  • "relative error" is derived from "absolute error" by dividing it by "basement" – sum(...) for intersection I. It is converted to % by multiplying by 100.
  • finally, we need to display two estimated errors above the table, with the phrase:
    • for K003:
For "ratio" values (the 4th line of values in each column displayed with `%` sign), the estimated error caused by unmatched sets of query groups in two observed snapshots is as follows:
- for `calls` – XX%
- for `total_time` – YY%
- for K002 and K001 we emphasize that it's applied to all presented numbers: 
For **all** values presented in this report, the estimated error caused by unmatched sets of query groups in two observed snapshots is as follows:
- for `calls` – XX%
- for `total_time` – YY%

❗ ❗ This methodology and the concept of estimated errors is useful in K001 and K002, but there it is to be applied to all metrics/values, since both those reports deal with aggregation when showing any numbers. For K001 we don't need to show % because it's already aggregated at higher level, but we do need to highlight that there is some error and provide estimation for it.

Acceptance criteria

As a DBA working with postgres-checkup, I can see one more value in each column of K001-003 reports: XXX%, meaning that the corresponding metric value for this query group (or class of queries in K002) is XXX per cent of total amount observed.

Above the table, I see the warning, saying that for ratio numbers (for K003) or in general, for all presented numbers (for K002) I must expect some deviations from real values.

Edited by postgres-ai