Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

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

 Sign up
Appearance settings

Commitdc9f8a7

Browse files
committed
Track statement entry timestamp in contrib/pg_stat_statements
This patch adds 'stats_since' and 'minmax_stats_since' columns to thepg_stat_statements view and pg_stat_statements() function. The new min/maxreset mode for the pg_stat_stetments_reset() function is controlled by theparameter minmax_only.'stat_since' column is populated with the current timestamp when a newstatement is added to the pg_stat_statements hashtable. It provides cleaninformation about statistics collection time intervals for each statement.Besides it can be used by sampling solutions to detect situations when astatement was evicted and stored again between samples.Such a sampling solution could derive any pg_stat_statements statistic valuesfor an interval between two samples with the exception of all min/maxstatistics. To address this issue this patch adds the ability to resetmin/max statistics independently of the statement reset using the newminmax_only parameter of the pg_stat_statements_reset(userid oid, dbid oid,queryid bigint, minmax_only boolean) function. The timestamp of such resetis stored in the minmax_stats_since field for each statement.pg_stat_statements_reset() function now returns the timestamp of a reset as theresult.Discussion:https://postgr.es/m/flat/72e80e7b160a6eb189df9ef6f068cce3765d37f8.camel%40moonset.ruAuthor: Andrei ZubkovReviewed-by: Julien Rouhaud, Hayato Kuroda, Yuki Seino, Chengxi SunReviewed-by: Anton Melnikov, Darren Rush, Michael Paquier, Sergei KornilovReviewed-by: Alena Rybakina, Andrei Lepikhov
1 parent6ab1dbd commitdc9f8a7

File tree

9 files changed

+511
-96
lines changed

9 files changed

+511
-96
lines changed

‎contrib/pg_stat_statements/Makefile

Lines changed: 1 addition & 1 deletion
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 walentry_timestampcleanup 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
Lines changed: 159 additions & 0 deletions
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

Lines changed: 65 additions & 51 deletions
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+
-- Newfunctions andviews 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

Lines changed: 1 addition & 0 deletions
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

Lines changed: 17 additions & 6 deletions
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
DROPVIEW pg_stat_statements;
128
DROPFUNCTION pg_stat_statements(boolean);
9+
DROPFUNCTION pg_stat_statements_reset(Oid,Oid,bigint);
1310

1411
/* Now redefine*/
1512
CREATEFUNCTIONpg_stat_statements(IN showtextboolean,
@@ -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_sincetimestamp with time zone,
61+
OUT minmax_stats_sincetimestamp 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
GRANTSELECTON pg_stat_statements TO PUBLIC;
71+
72+
CREATEFUNCTIONpg_stat_statements_reset(IN useridOid DEFAULT0,
73+
IN dbidOid DEFAULT0,
74+
IN queryidbigint DEFAULT0,
75+
IN minmax_onlyboolean DEFAULT false
76+
)
77+
RETURNStimestamp 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 ALLON FUNCTION pg_stat_statements_reset(Oid,Oid,bigint,boolean)FROM PUBLIC;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp