Once installed, the pgpro_stats_2 extension starts collecting statistics on the executed statements. The collected data is similar to the one provided by pg_stat_statements, but also includes information on query plans and wait events for each query type. The statistics is saved into memory and is accessible through the pgpro_stats_statements view.

To collect statistics on wait events, pgpro_stats_2 uses time-based sampling. Wait events are sampled at the time interval specified by the pgpro_stats.profile_period parameter, which is set to 10ms by default. If the sampling shows that the process is waiting, the pgpro_stats.profile_period value is added to the wait event duration. Thus, time estimation for each wait event remains valid even if the pgpro_stats.profile_period parameter value has changed. If you are not interested in wait event statistics, you can disable wait event sampling by setting the pgpro_stats.pg_wait_sampling parameter to false.

pgpro_stats_statements.plans and pgpro_stats_statements.calls are not always expected to match because planning and execution statistics are updated at their respective end phase, and only for successful operations. For example, if a statement is successfully planned but fails during the execution phase, only its planning statistics will be updated. If planning is skipped because a cached plan is used, only its execution statistics will be updated.

As an example, let's create a table with some random data and build an index on this table:

CREATE TABLE test AS (SELECT i, random() x FROM generate_series(1,1000000) i) ON CONFLICT DO NOTHING;
CREATE INDEX test_x_idx ON test (x) ON CONFLICT DO NOTHING;

Now run the following query several times using different values for :x_min and :x_max:

select * from test where x >= :x_min and x <= :x_max;

The collected statistics should appear in the pgpro_stats_statements view:

postgres=# SELECT queryid, query, planid, plan FROM pg_stats_statements WHERE query LIKE 'select * from test where%';
-[ RECORD 1 ]----------------------------------------------------
queryid | -1730632209943640345
query   | select * from test where x >= $1 AND x <= $2
planid  | -4215043083291098626
plan    | Seq Scan on public.test                                +
        |   Output: i, x                                         +
        |   Filter: ((test.x >= $1) AND (test.x <= $2))          +
        | Query Identifier: -1730632209943640345                 +
        |
-[ RECORD 2 ]----------------------------------------------------
queryid | -1730632209943640345
query   | select * from test where x >= $1 AND x <= $2
planid  | -8025285020772903731
plan    | Bitmap Heap Scan on public.test                        +
        |   Output: i, x                                         +
        |   Recheck Cond: ((test.x >= $1) AND (test.x <= $2))    +
        |   ->  Bitmap Index Scan on test_x_idx                  +
        |         Index Cond: ((test.x >= $1) AND (test.x <= $2))+
        | Query Identifier: -1730632209943640345                 +
        |
-[ RECORD 3 ]----------------------------------------------------
queryid | -1730632209943640345
query   | select * from test where x >= $1 AND x <= $2
planid  | 6075533348212423849
plan    | Index Scan using test_x_idx on public.test             +
        |   Output: i, x                                         +
        |   Index Cond: ((test.x >= $1) AND (test.x <= $2))      +
        | Query Identifier: -1730632209943640345                 +

The statistics gathered by the module are available in the pgpro_stats_statements view. This view contains one row for each distinct database ID, user ID, query ID, and plan ID (up to the maximum number of distinct statements that the module can track). The columns of the view are shown in Table F.29.

Take into account that like pg_stat_statements, pgpro_stats_2 normalizes into one record those DML queries (containing SELECT, INSERT, UPDATE, DELETE and MERGE commands) that have equivalent structures according to some internal hash value. Being compared this way, two queries are normally considered equal if they are semantically equivalent up to constants included in the queries. When the value of a constant in a query is ignored for comparison with other queries, this constant is replaced in the pgpro_stats_2 output with a symbol of a parameter, such as, $k, where k is a positive integer. If a query already contains parameters, the initial value of k equals the number following the last number of a $n parameter in the original query text. If there are no parameters, the initial value of k equals 1. Note that sometimes hidden parameter symbols affect this numbering. For example, PL/pgSQL uses such hidden symbols to insert values of function local variables into queries, so a PL/pgSQL statement SELECT i + 1 INTO j will be represented as SELECT i + $2 in the normalized query text.

In some cases, queries with visibly different texts might get merged into a single pgpro_stats_statements entry; as explained above, this is expected to happen for semantically equivalent queries. In addition, if the only difference between queries is the number of elements in a list of constants, the list will get squashed down to a single element but shown with a commented-out list indicator:

=# SELECT pgpro_stats_statements_reset() ON CONFLICT DO NOTHING;
=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7) ON CONFLICT DO NOTHING;
=# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8) ON CONFLICT DO NOTHING;
=# SELECT query, calls FROM pgpro_stats_statements
   WHERE query LIKE 'SELECT%';
-[ RECORD 1 ]------------------------------
query | SELECT * FROM test WHERE a IN ($1 /*, ... */)
calls | 2

In addition to these cases, there is a small chance of hash collisions causing unrelated queries to be merged into one entry. (This cannot happen for queries belonging to different users or databases, however.)

pgpro_stats_2 uses a similar technique to normalize plan texts. When doing so, an attempt is made to associate numbers of constants in the plan text with the corresponding numbers of constants in the query text. If such an attempt appears unsuccessful for a certain constant in the plan text, it is assigned the number following the maximum number of a constant replaced in the query text. For example, consider the query:

SELECT 1::int, 'abc'::VARCHAR(3), 2::int;

pgpro_stats_2 will replace numbers of constants in the query text and in the text of the corresponding plan as follows:

postgres=# SELECT query, plan FROM pgpro_stats_statements;
                     query                      |                                plan
------------------------------------------------+--------------------------------------------------
SELECT $1::int, $2::VARCHAR(3), $3::int         | Result                                           +
                                                |   Output: $1, $4, $3                             +

In this plan text, it appeared possible to associate constants numbered 1 and 3 from the query text, but not the constant numbered 2, and the latter was replaced with the number following the maximum number in the query text, that is, number 4.

Replacement of numbers in plan texts has an exception for version numbers of XML documents. If in the original query such a number is represented with a constant, e.g., '1.0', it is retained as is in the plan text rather than replaced with $k. If the version number of an XML document is represented with an expression, replacement of constants follows usual rules.

The statistics of the pgpro_stats_2 module itself are tracked and made available in the pgpro_stats_info view. This view contains only a single row. The columns of the view are shown in Table F.30.