1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
--
-- statement timestamps
--
-- planning time is needed during tests
SET pg_stat_statements.track_planning = TRUE;
SELECT 1 AS "STMTTS1";
STMTTS1
---------
1
(1 row)
SELECT now() AS ref_ts \gset
SELECT 1,2 AS "STMTTS2";
?column? | STMTTS2
----------+---------
1 | 2
(1 row)
SELECT stats_since >= :'ref_ts', count(*) FROM pg_stat_statements
WHERE query LIKE '%STMTTS%'
GROUP BY stats_since >= :'ref_ts'
ORDER BY stats_since >= :'ref_ts';
?column? | count
----------+-------
f | 1
t | 1
(2 rows)
SELECT now() AS ref_ts \gset
SELECT
count(*) as total,
count(*) FILTER (
WHERE min_plan_time + max_plan_time = 0
) as minmax_plan_zero,
count(*) FILTER (
WHERE min_exec_time + max_exec_time = 0
) as minmax_exec_zero,
count(*) FILTER (
WHERE minmax_stats_since >= :'ref_ts'
) as minmax_stats_since_after_ref,
count(*) FILTER (
WHERE stats_since >= :'ref_ts'
) as stats_since_after_ref
FROM pg_stat_statements
WHERE query LIKE '%STMTTS%';
total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
-------+------------------+------------------+------------------------------+-----------------------
2 | 0 | 0 | 0 | 0
(1 row)
-- Perform single min/max reset
SELECT pg_stat_statements_reset(0, 0, queryid, true) AS minmax_reset_ts
FROM pg_stat_statements
WHERE query LIKE '%STMTTS1%' \gset
-- check
SELECT
count(*) as total,
count(*) FILTER (
WHERE min_plan_time + max_plan_time = 0
) as minmax_plan_zero,
count(*) FILTER (
WHERE min_exec_time + max_exec_time = 0
) as minmax_exec_zero,
count(*) FILTER (
WHERE minmax_stats_since >= :'ref_ts'
) as minmax_stats_since_after_ref,
count(*) FILTER (
WHERE stats_since >= :'ref_ts'
) as stats_since_after_ref
FROM pg_stat_statements
WHERE query LIKE '%STMTTS%';
total | minmax_plan_zero | minmax_exec_zero | minmax_stats_since_after_ref | stats_since_after_ref
-------+------------------+------------------+------------------------------+-----------------------
2 | 1 | 1 | 1 | 0
(1 row)
-- check minmax reset timestamps
SELECT
query, minmax_stats_since = :'minmax_reset_ts' AS reset_ts_match
FROM pg_stat_statements
WHERE query LIKE '%STMTTS%'
ORDER BY query COLLATE "C";
query | reset_ts_match
---------------------------+----------------
SELECT $1 AS "STMTTS1" | t
SELECT $1,$2 AS "STMTTS2" | f
(2 rows)
-- check that minmax reset does not set stats_reset
SELECT
stats_reset = :'minmax_reset_ts' AS stats_reset_ts_match
FROM pg_stat_statements_info;
stats_reset_ts_match
----------------------
f
(1 row)
-- Perform common min/max reset
SELECT pg_stat_statements_reset(0, 0, 0, true) AS minmax_reset_ts \gset
-- check again
SELECT
count(*) as total,
count(*) FILTER (
WHERE min_plan_time + max_plan_time = 0
) as minmax_plan_zero,
count(*) FILTER (
WHERE min_exec_time + max_exec_time = 0
) as minmax_exec_zero,
count(*) FILTER (
WHERE minmax_stats_since >= :'ref_ts'
) as minmax_ts_after_ref,
count(*) FILTER (
WHERE minmax_stats_since = :'minmax_reset_ts'
) as minmax_ts_match,
count(*) FILTER (
WHERE stats_since >= :'ref_ts'
) as stats_since_after_ref
FROM pg_stat_statements
WHERE query LIKE '%STMTTS%';
total | minmax_plan_zero | minmax_exec_zero | minmax_ts_after_ref | minmax_ts_match | stats_since_after_ref
-------+------------------+------------------+---------------------+-----------------+-----------------------
2 | 2 | 2 | 2 | 2 | 0
(1 row)
-- Execute first query once more to check stats update
SELECT 1 AS "STMTTS1";
STMTTS1
---------
1
(1 row)
-- check
-- we don't check planing times here to be independent of
-- plan caching approach
SELECT
count(*) as total,
count(*) FILTER (
WHERE min_exec_time + max_exec_time = 0
) as minmax_exec_zero,
count(*) FILTER (
WHERE minmax_stats_since >= :'ref_ts'
) as minmax_ts_after_ref,
count(*) FILTER (
WHERE stats_since >= :'ref_ts'
) as stats_since_after_ref
FROM pg_stat_statements
WHERE query LIKE '%STMTTS%';
total | minmax_exec_zero | minmax_ts_after_ref | stats_since_after_ref
-------+------------------+---------------------+-----------------------
2 | 1 | 2 | 0
(1 row)
-- Cleanup
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
---
t
(1 row)
|
