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
|
-- This program is open source, licensed under the PostgreSQL License.
-- For license terms, see the LICENSE file.
--
-- Copyright (c) 2014-2017, Dalibo
-- Copyright (c) 2018-2024, The PoWA-team
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_stat_kcache" to load this file. \quit
SET client_encoding = 'UTF8';
CREATE FUNCTION pg_stat_kcache(
OUT queryid bigint,
OUT top bool,
OUT userid oid,
OUT dbid oid,
/* planning time */
OUT plan_reads bigint, /* total reads, in bytes */
OUT plan_writes bigint, /* total writes, in bytes */
OUT plan_user_time double precision, /* total user CPU time used */
OUT plan_system_time double precision, /* total system CPU time used */
OUT plan_minflts bigint, /* total page reclaims (soft page faults) */
OUT plan_majflts bigint, /* total page faults (hard page faults) */
OUT plan_nswaps bigint, /* total swaps */
OUT plan_msgsnds bigint, /* total IPC messages sent */
OUT plan_msgrcvs bigint, /* total IPC messages received */
OUT plan_nsignals bigint, /* total signals received */
OUT plan_nvcsws bigint, /* total voluntary context switches */
OUT plan_nivcsws bigint, /* total involuntary context switches */
/* execution time */
OUT exec_reads bigint, /* total reads, in bytes */
OUT exec_writes bigint, /* total writes, in bytes */
OUT exec_user_time double precision, /* total user CPU time used */
OUT exec_system_time double precision, /* total system CPU time used */
OUT exec_minflts bigint, /* total page reclaims (soft page faults) */
OUT exec_majflts bigint, /* total page faults (hard page faults) */
OUT exec_nswaps bigint, /* total swaps */
OUT exec_msgsnds bigint, /* total IPC messages sent */
OUT exec_msgrcvs bigint, /* total IPC messages received */
OUT exec_nsignals bigint, /* total signals received */
OUT exec_nvcsws bigint, /* total voluntary context switches */
OUT exec_nivcsws bigint /* total involuntary context switches */
)
RETURNS SETOF record
LANGUAGE c COST 1000
AS '$libdir/pg_stat_kcache', 'pg_stat_kcache_2_2';
GRANT ALL ON FUNCTION pg_stat_kcache() TO public;
CREATE FUNCTION pg_stat_kcache_reset()
RETURNS void
LANGUAGE c COST 1000
AS '$libdir/pg_stat_kcache', 'pg_stat_kcache_reset';
REVOKE ALL ON FUNCTION pg_stat_kcache_reset() FROM public;
CREATE VIEW pg_stat_kcache_detail AS
SELECT s.query, k.top, d.datname, r.rolname,
k.plan_user_time,
k.plan_system_time,
k.plan_minflts,
k.plan_majflts,
k.plan_nswaps,
k.plan_reads AS plan_reads,
k.plan_reads/(current_setting('block_size')::integer) AS plan_reads_blks,
k.plan_writes AS plan_writes,
k.plan_writes/(current_setting('block_size')::integer) AS plan_writes_blks,
k.plan_msgsnds,
k.plan_msgrcvs,
k.plan_nsignals,
k.plan_nvcsws,
k.plan_nivcsws,
k.exec_user_time,
k.exec_system_time,
k.exec_minflts,
k.exec_majflts,
k.exec_nswaps,
k.exec_reads AS exec_reads,
k.exec_reads/(current_setting('block_size')::integer) AS exec_reads_blks,
k.exec_writes AS exec_writes,
k.exec_writes/(current_setting('block_size')::integer) AS exec_writes_blks,
k.exec_msgsnds,
k.exec_msgrcvs,
k.exec_nsignals,
k.exec_nvcsws,
k.exec_nivcsws
FROM pg_stat_kcache() k
JOIN pg_stat_statements s
ON k.queryid = s.queryid AND k.dbid = s.dbid AND k.userid = s.userid
JOIN pg_database d
ON d.oid = s.dbid
JOIN pg_roles r
ON r.oid = s.userid;
GRANT SELECT ON pg_stat_kcache_detail TO public;
CREATE VIEW pg_stat_kcache AS
SELECT datname,
SUM(plan_user_time) AS plan_user_time,
SUM(plan_system_time) AS plan_system_time,
SUM(plan_minflts) AS plan_minflts,
SUM(plan_majflts) AS plan_majflts,
SUM(plan_nswaps) AS plan_nswaps,
SUM(plan_reads) AS plan_reads,
SUM(plan_reads_blks) AS plan_reads_blks,
SUM(plan_writes) AS plan_writes,
SUM(plan_writes_blks) AS plan_writes_blks,
SUM(plan_msgsnds) AS plan_msgsnds,
SUM(plan_msgrcvs) AS plan_msgrcvs,
SUM(plan_nsignals) AS plan_nsignals,
SUM(plan_nvcsws) AS plan_nvcsws,
SUM(plan_nivcsws) AS plan_nivcsws,
SUM(exec_user_time) AS exec_user_time,
SUM(exec_system_time) AS exec_system_time,
SUM(exec_minflts) AS exec_minflts,
SUM(exec_majflts) AS exec_majflts,
SUM(exec_nswaps) AS exec_nswaps,
SUM(exec_reads) AS exec_reads,
SUM(exec_reads_blks) AS exec_reads_blks,
SUM(exec_writes) AS exec_writes,
SUM(exec_writes_blks) AS exec_writes_blks,
SUM(exec_msgsnds) AS exec_msgsnds,
SUM(exec_msgrcvs) AS exec_msgrcvs,
SUM(exec_nsignals) AS exec_nsignals,
SUM(exec_nvcsws) AS exec_nvcsws,
SUM(exec_nivcsws) AS exec_nivcsws
FROM pg_stat_kcache_detail
WHERE top IS TRUE
GROUP BY datname;
GRANT SELECT ON pg_stat_kcache TO public;
|
