Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit dc9f8a7

Browse filesBrowse files
akorotkovakorotkov
committed
Track statement entry timestamp in contrib/pg_stat_statements
This patch adds 'stats_since' and 'minmax_stats_since' columns to the pg_stat_statements view and pg_stat_statements() function. The new min/max reset mode for the pg_stat_stetments_reset() function is controlled by the parameter minmax_only. 'stat_since' column is populated with the current timestamp when a new statement is added to the pg_stat_statements hashtable. It provides clean information about statistics collection time intervals for each statement. Besides it can be used by sampling solutions to detect situations when a statement was evicted and stored again between samples. Such a sampling solution could derive any pg_stat_statements statistic values for an interval between two samples with the exception of all min/max statistics. To address this issue this patch adds the ability to reset min/max statistics independently of the statement reset using the new minmax_only parameter of the pg_stat_statements_reset(userid oid, dbid oid, queryid bigint, minmax_only boolean) function. The timestamp of such reset is stored in the minmax_stats_since field for each statement. pg_stat_statements_reset() function now returns the timestamp of a reset as the result. Discussion: https://postgr.es/m/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ru Author: Andrei Zubkov Reviewed-by: Julien Rouhaud, Hayato Kuroda, Yuki Seino, Chengxi Sun Reviewed-by: Anton Melnikov, Darren Rush, Michael Paquier, Sergei Kornilov Reviewed-by: Alena Rybakina, Andrei Lepikhov
1 parent 6ab1dbd commit dc9f8a7
Copy full SHA for dc9f8a7

9 files changed

+511
-96
lines changed

‎contrib/pg_stat_statements/Makefile

Copy file name to clipboardExpand all lines: contrib/pg_stat_statements/Makefile
+1-1
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
1919

2020
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
2121
REGRESS = select dml cursors utility level_tracking planning \
22-
user_activity wal cleanup oldextversions
22+
user_activity wal entry_timestamp cleanup oldextversions
2323
# Disabled because these tests require "shared_preload_libraries=pg_stat_statements",
2424
# which typical installcheck users do not have (e.g. buildfarm clients).
2525
NO_INSTALLCHECK = 1

‎contrib/pg_stat_statements/expected/entry_timestamp.out

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

‎contrib/pg_stat_statements/expected/oldextversions.out

Copy file name to clipboardExpand all lines: contrib/pg_stat_statements/expected/oldextversions.out
+65-51
Original file line numberDiff line numberDiff line change
@@ -250,64 +250,78 @@ SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
250250
t
251251
(1 row)
252252

253-
-- New views for pg_stat_statements in 1.11
253+
-- New functions and views for pg_stat_statements in 1.11
254254
AlTER EXTENSION pg_stat_statements UPDATE TO '1.11';
255255
\d pg_stat_statements
256-
View "public.pg_stat_statements"
257-
Column | Type | Collation | Nullable | Default
258-
------------------------+------------------+-----------+----------+---------
259-
userid | oid | | |
260-
dbid | oid | | |
261-
toplevel | boolean | | |
262-
queryid | bigint | | |
263-
query | text | | |
264-
plans | bigint | | |
265-
total_plan_time | double precision | | |
266-
min_plan_time | double precision | | |
267-
max_plan_time | double precision | | |
268-
mean_plan_time | double precision | | |
269-
stddev_plan_time | double precision | | |
270-
calls | bigint | | |
271-
total_exec_time | double precision | | |
272-
min_exec_time | double precision | | |
273-
max_exec_time | double precision | | |
274-
mean_exec_time | double precision | | |
275-
stddev_exec_time | double precision | | |
276-
rows | bigint | | |
277-
shared_blks_hit | bigint | | |
278-
shared_blks_read | bigint | | |
279-
shared_blks_dirtied | bigint | | |
280-
shared_blks_written | bigint | | |
281-
local_blks_hit | bigint | | |
282-
local_blks_read | bigint | | |
283-
local_blks_dirtied | bigint | | |
284-
local_blks_written | bigint | | |
285-
temp_blks_read | bigint | | |
286-
temp_blks_written | bigint | | |
287-
shared_blk_read_time | double precision | | |
288-
shared_blk_write_time | double precision | | |
289-
local_blk_read_time | double precision | | |
290-
local_blk_write_time | double precision | | |
291-
temp_blk_read_time | double precision | | |
292-
temp_blk_write_time | double precision | | |
293-
wal_records | bigint | | |
294-
wal_fpi | bigint | | |
295-
wal_bytes | numeric | | |
296-
jit_functions | bigint | | |
297-
jit_generation_time | double precision | | |
298-
jit_inlining_count | bigint | | |
299-
jit_inlining_time | double precision | | |
300-
jit_optimization_count | bigint | | |
301-
jit_optimization_time | double precision | | |
302-
jit_emission_count | bigint | | |
303-
jit_emission_time | double precision | | |
304-
jit_deform_count | bigint | | |
305-
jit_deform_time | double precision | | |
256+
View "public.pg_stat_statements"
257+
Column | Type | Collation | Nullable | Default
258+
------------------------+--------------------------+-----------+----------+---------
259+
userid | oid | | |
260+
dbid | oid | | |
261+
toplevel | boolean | | |
262+
queryid | bigint | | |
263+
query | text | | |
264+
plans | bigint | | |
265+
total_plan_time | double precision | | |
266+
min_plan_time | double precision | | |
267+
max_plan_time | double precision | | |
268+
mean_plan_time | double precision | | |
269+
stddev_plan_time | double precision | | |
270+
calls | bigint | | |
271+
total_exec_time | double precision | | |
272+
min_exec_time | double precision | | |
273+
max_exec_time | double precision | | |
274+
mean_exec_time | double precision | | |
275+
stddev_exec_time | double precision | | |
276+
rows | bigint | | |
277+
shared_blks_hit | bigint | | |
278+
shared_blks_read | bigint | | |
279+
shared_blks_dirtied | bigint | | |
280+
shared_blks_written | bigint | | |
281+
local_blks_hit | bigint | | |
282+
local_blks_read | bigint | | |
283+
local_blks_dirtied | bigint | | |
284+
local_blks_written | bigint | | |
285+
temp_blks_read | bigint | | |
286+
temp_blks_written | bigint | | |
287+
shared_blk_read_time | double precision | | |
288+
shared_blk_write_time | double precision | | |
289+
local_blk_read_time | double precision | | |
290+
local_blk_write_time | double precision | | |
291+
temp_blk_read_time | double precision | | |
292+
temp_blk_write_time | double precision | | |
293+
wal_records | bigint | | |
294+
wal_fpi | bigint | | |
295+
wal_bytes | numeric | | |
296+
jit_functions | bigint | | |
297+
jit_generation_time | double precision | | |
298+
jit_inlining_count | bigint | | |
299+
jit_inlining_time | double precision | | |
300+
jit_optimization_count | bigint | | |
301+
jit_optimization_time | double precision | | |
302+
jit_emission_count | bigint | | |
303+
jit_emission_time | double precision | | |
304+
jit_deform_count | bigint | | |
305+
jit_deform_time | double precision | | |
306+
stats_since | timestamp with time zone | | |
307+
minmax_stats_since | timestamp with time zone | | |
306308

307309
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
308310
has_data
309311
----------
310312
t
311313
(1 row)
312314

315+
-- New parameter minmax_only of pg_stat_statements_reset function
316+
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
317+
pg_get_functiondef
318+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
319+
CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0, minmax_only boolean DEFAULT false)+
320+
RETURNS timestamp with time zone +
321+
LANGUAGE c +
322+
PARALLEL SAFE STRICT +
323+
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_11$function$ +
324+
325+
(1 row)
326+
313327
DROP EXTENSION pg_stat_statements;

‎contrib/pg_stat_statements/meson.build

Copy file name to clipboardExpand all lines: contrib/pg_stat_statements/meson.build
+1
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,7 @@ tests += {
4949
'planning',
5050
'user_activity',
5151
'wal',
52+
'entry_timestamp',
5253
'cleanup',
5354
'oldextversions',
5455
],

‎contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql

Copy file name to clipboardExpand all lines: contrib/pg_stat_statements/pg_stat_statements--1.10--1.11.sql
+17-6
Original file line numberDiff line numberDiff line change
@@ -3,13 +3,10 @@
33
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
44
\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.11'" to load this file. \quit
55

6-
/* First we have to remove them from the extension */
7-
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
8-
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements(boolean);
9-
10-
/* Then we can drop them */
6+
/* Drop old versions */
117
DROP VIEW pg_stat_statements;
128
DROP FUNCTION pg_stat_statements(boolean);
9+
DROP FUNCTION pg_stat_statements_reset(Oid, Oid, bigint);
1310

1411
/* Now redefine */
1512
CREATE FUNCTION pg_stat_statements(IN showtext boolean,
@@ -59,7 +56,9 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
5956
OUT jit_emission_count int8,
6057
OUT jit_emission_time float8,
6158
OUT jit_deform_count int8,
62-
OUT jit_deform_time float8
59+
OUT jit_deform_time float8,
60+
OUT stats_since timestamp with time zone,
61+
OUT minmax_stats_since timestamp with time zone
6362
)
6463
RETURNS SETOF record
6564
AS 'MODULE_PATHNAME', 'pg_stat_statements_1_11'
@@ -69,3 +68,15 @@ CREATE VIEW pg_stat_statements AS
6968
SELECT * FROM pg_stat_statements(true);
7069

7170
GRANT SELECT ON pg_stat_statements TO PUBLIC;
71+
72+
CREATE FUNCTION pg_stat_statements_reset(IN userid Oid DEFAULT 0,
73+
IN dbid Oid DEFAULT 0,
74+
IN queryid bigint DEFAULT 0,
75+
IN minmax_only boolean DEFAULT false
76+
)
77+
RETURNS timestamp with time zone
78+
AS 'MODULE_PATHNAME', 'pg_stat_statements_reset_1_11'
79+
LANGUAGE C STRICT PARALLEL SAFE;
80+
81+
-- Don't want this to be available to non-superusers.
82+
REVOKE ALL ON FUNCTION pg_stat_statements_reset(Oid, Oid, bigint, boolean) FROM PUBLIC;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.