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

Commit717f709

Browse files
committed
Add stats for min, max, mean, stddev times to pg_stat_statements.
The new fields are min_time, max_time, mean_time and stddev_time.Based on an original patch from Mitsumasa KONDO, modified by me. Reviewed by Petr Jelínek.
1 parent8816af6 commit717f709

File tree

6 files changed

+170
-7
lines changed

6 files changed

+170
-7
lines changed

‎contrib/pg_stat_statements/Makefile‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,9 @@ MODULE_big = pg_stat_statements
44
OBJS = pg_stat_statements.o$(WIN32RES)
55

66
EXTENSION = pg_stat_statements
7-
DATA = pg_stat_statements--1.2.sql pg_stat_statements--1.1--1.2.sql\
8-
pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql
7+
DATA = pg_stat_statements--1.3.sql pg_stat_statements--1.2--1.3.sql\
8+
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql\
9+
pg_stat_statements--unpackaged--1.0.sql
910
PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
1011

1112
ifdefUSE_PGXS
Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.2--1.3.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pg_stat_statements UPDATE TO '1.3'" to load this file. \quit
5+
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*/
11+
DROPVIEW pg_stat_statements;
12+
DROPFUNCTION pg_stat_statements(boolean);
13+
14+
/* Now redefine*/
15+
CREATEFUNCTIONpg_stat_statements(IN showtextboolean,
16+
OUT useridoid,
17+
OUT dbidoid,
18+
OUT queryidbigint,
19+
OUT querytext,
20+
OUT calls int8,
21+
OUT total_time float8,
22+
OUT min_time float8,
23+
OUT max_time float8,
24+
OUT mean_time float8,
25+
OUT stddev_time float8,
26+
OUT rows int8,
27+
OUT shared_blks_hit int8,
28+
OUT shared_blks_read int8,
29+
OUT shared_blks_dirtied int8,
30+
OUT shared_blks_written int8,
31+
OUT local_blks_hit int8,
32+
OUT local_blks_read int8,
33+
OUT local_blks_dirtied int8,
34+
OUT local_blks_written int8,
35+
OUT temp_blks_read int8,
36+
OUT temp_blks_written int8,
37+
OUT blk_read_time float8,
38+
OUT blk_write_time float8
39+
)
40+
RETURNS SETOF record
41+
AS'MODULE_PATHNAME','pg_stat_statements_1_3'
42+
LANGUAGE C STRICT VOLATILE;
43+
44+
CREATEVIEWpg_stat_statementsAS
45+
SELECT*FROM pg_stat_statements(true);
46+
47+
GRANTSELECTON pg_stat_statements TO PUBLIC;

contrib/pg_stat_statements/pg_stat_statements--1.2.sql renamed to contrib/pg_stat_statements/pg_stat_statements--1.3.sql

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* contrib/pg_stat_statements/pg_stat_statements--1.2.sql*/
1+
/* contrib/pg_stat_statements/pg_stat_statements--1.3.sql*/
22

33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use"CREATE EXTENSION pg_stat_statements" to load this file. \quit
@@ -16,6 +16,10 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
1616
OUT querytext,
1717
OUT calls int8,
1818
OUT total_time float8,
19+
OUT min_time float8,
20+
OUT max_time float8,
21+
OUT mean_time float8,
22+
OUT stddev_time float8,
1923
OUT rows int8,
2024
OUT shared_blks_hit int8,
2125
OUT shared_blks_read int8,
@@ -31,7 +35,7 @@ CREATE FUNCTION pg_stat_statements(IN showtext boolean,
3135
OUT blk_write_time float8
3236
)
3337
RETURNS SETOF record
34-
AS'MODULE_PATHNAME','pg_stat_statements_1_2'
38+
AS'MODULE_PATHNAME','pg_stat_statements_1_3'
3539
LANGUAGE C STRICT VOLATILE;
3640

3741
-- Register a view on the function for ease of use.

‎contrib/pg_stat_statements/pg_stat_statements.c‎

Lines changed: 85 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -115,7 +115,8 @@ typedef enum pgssVersion
115115
{
116116
PGSS_V1_0=0,
117117
PGSS_V1_1,
118-
PGSS_V1_2
118+
PGSS_V1_2,
119+
PGSS_V1_3
119120
}pgssVersion;
120121

121122
/*
@@ -136,6 +137,10 @@ typedef struct Counters
136137
{
137138
int64calls;/* # of times executed */
138139
doubletotal_time;/* total execution time, in msec */
140+
doublemin_time;/* minimim execution time in msec */
141+
doublemax_time;/* maximum execution time in msec */
142+
doublemean_time;/* mean execution time in msec */
143+
doublesum_var_time;/* sum of variances in execution time in msec */
139144
int64rows;/* total # of retrieved or affected rows */
140145
int64shared_blks_hit;/* # of shared buffer hits */
141146
int64shared_blks_read;/* # of shared disk blocks read */
@@ -274,6 +279,7 @@ void_PG_fini(void);
274279

275280
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
276281
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
282+
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
277283
PG_FUNCTION_INFO_V1(pg_stat_statements);
278284

279285
staticvoidpgss_shmem_startup(void);
@@ -320,6 +326,7 @@ static char *generate_normalized_query(pgssJumbleState *jstate, const char *quer
320326
int*query_len_p,intencoding);
321327
staticvoidfill_in_constant_lengths(pgssJumbleState*jstate,constchar*query);
322328
staticintcomp_location(constvoid*a,constvoid*b);
329+
staticinlinedoublesqrtd(constdoublex);
323330

324331

325332
/*
@@ -1215,6 +1222,32 @@ pgss_store(const char *query, uint32 queryId,
12151222

12161223
e->counters.calls+=1;
12171224
e->counters.total_time+=total_time;
1225+
if (e->counters.calls==1)
1226+
{
1227+
e->counters.min_time=total_time;
1228+
e->counters.max_time=total_time;
1229+
e->counters.mean_time=total_time;
1230+
}
1231+
else
1232+
{
1233+
/*
1234+
* Welford's method for accurately computing variance.
1235+
* See <http://www.johndcook.com/blog/standard_deviation/>
1236+
*/
1237+
doubleold_mean=e->counters.mean_time;
1238+
1239+
e->counters.mean_time+=
1240+
(total_time-old_mean) /e->counters.calls;
1241+
e->counters.sum_var_time+=
1242+
(total_time-old_mean)* (total_time-e->counters.mean_time);
1243+
1244+
/* calculate min and max time */
1245+
if (e->counters.min_time>total_time)
1246+
e->counters.min_time=total_time;
1247+
if (e->counters.max_time<total_time)
1248+
e->counters.max_time=total_time;
1249+
1250+
}
12181251
e->counters.rows+=rows;
12191252
e->counters.shared_blks_hit+=bufusage->shared_blks_hit;
12201253
e->counters.shared_blks_read+=bufusage->shared_blks_read;
@@ -1259,7 +1292,8 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
12591292
#definePG_STAT_STATEMENTS_COLS_V1_014
12601293
#definePG_STAT_STATEMENTS_COLS_V1_118
12611294
#definePG_STAT_STATEMENTS_COLS_V1_219
1262-
#definePG_STAT_STATEMENTS_COLS19/* maximum of above */
1295+
#definePG_STAT_STATEMENTS_COLS_V1_323
1296+
#definePG_STAT_STATEMENTS_COLS23/* maximum of above */
12631297

12641298
/*
12651299
* Retrieve statement statistics.
@@ -1271,6 +1305,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
12711305
* expected API version is identified by embedding it in the C name of the
12721306
* function. Unfortunately we weren't bright enough to do that for 1.1.
12731307
*/
1308+
Datum
1309+
pg_stat_statements_1_3(PG_FUNCTION_ARGS)
1310+
{
1311+
boolshowtext=PG_GETARG_BOOL(0);
1312+
1313+
pg_stat_statements_internal(fcinfo,PGSS_V1_3,showtext);
1314+
1315+
return (Datum)0;
1316+
}
1317+
12741318
Datum
12751319
pg_stat_statements_1_2(PG_FUNCTION_ARGS)
12761320
{
@@ -1360,6 +1404,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
13601404
if (api_version!=PGSS_V1_2)
13611405
elog(ERROR,"incorrect number of output arguments");
13621406
break;
1407+
casePG_STAT_STATEMENTS_COLS_V1_3:
1408+
if (api_version!=PGSS_V1_3)
1409+
elog(ERROR,"incorrect number of output arguments");
1410+
break;
13631411
default:
13641412
elog(ERROR,"incorrect number of output arguments");
13651413
}
@@ -1519,6 +1567,23 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
15191567

15201568
values[i++]=Int64GetDatumFast(tmp.calls);
15211569
values[i++]=Float8GetDatumFast(tmp.total_time);
1570+
if (api_version >=PGSS_V1_3)
1571+
{
1572+
values[i++]=Float8GetDatumFast(tmp.min_time);
1573+
values[i++]=Float8GetDatumFast(tmp.max_time);
1574+
values[i++]=Float8GetDatumFast(tmp.mean_time);
1575+
/*
1576+
* Note we are calculating the population variance here, not the
1577+
* sample variance, as we have data for the whole population,
1578+
* so Bessel's correction is not used, and we don't divide by
1579+
* tmp.calls - 1.
1580+
*/
1581+
if (tmp.calls>1)
1582+
values[i++]=
1583+
Float8GetDatumFast(sqrtd(tmp.sum_var_time /tmp.calls));
1584+
else
1585+
values[i++]=Float8GetDatumFast(0.0);
1586+
}
15221587
values[i++]=Int64GetDatumFast(tmp.rows);
15231588
values[i++]=Int64GetDatumFast(tmp.shared_blks_hit);
15241589
values[i++]=Int64GetDatumFast(tmp.shared_blks_read);
@@ -1541,6 +1606,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
15411606
Assert(i== (api_version==PGSS_V1_0 ?PG_STAT_STATEMENTS_COLS_V1_0 :
15421607
api_version==PGSS_V1_1 ?PG_STAT_STATEMENTS_COLS_V1_1 :
15431608
api_version==PGSS_V1_2 ?PG_STAT_STATEMENTS_COLS_V1_2 :
1609+
api_version==PGSS_V1_3 ?PG_STAT_STATEMENTS_COLS_V1_3 :
15441610
-1/* fail if you forget to update this assert */ ));
15451611

15461612
tuplestore_putvalues(tupstore,tupdesc,values,nulls);
@@ -2899,3 +2965,20 @@ comp_location(const void *a, const void *b)
28992965
else
29002966
return0;
29012967
}
2968+
2969+
/*
2970+
* fast sqrt algorithm: reference from Fast inverse square root algorithms.
2971+
*/
2972+
staticinlinedouble
2973+
sqrtd(constdoublex)
2974+
{
2975+
doublex_half=0.5*x;
2976+
long longinttmp=0x5FE6EB50C7B537AAl- (*(long longint*)&x >>1);
2977+
doublex_result=* (double*)&tmp;
2978+
2979+
x_result *= (1.5- (x_half*x_result*x_result));
2980+
/* If retry this calculation, it becomes higher precision at sqrt */
2981+
x_result *= (1.5- (x_half*x_result*x_result));
2982+
2983+
returnx_result*x;
2984+
}
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pg_stat_statements extension
22
comment = 'track execution statistics of all SQL statements executed'
3-
default_version = '1.2'
3+
default_version = '1.3'
44
module_pathname = '$libdir/pg_stat_statements'
55
relocatable = true

‎doc/src/sgml/pgstatstatements.sgml‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,34 @@
8686
<entry>Total time spent in the statement, in milliseconds</entry>
8787
</row>
8888

89+
<row>
90+
<entry><structfield>min_time</structfield></entry>
91+
<entry><type>double precision</type></entry>
92+
<entry></entry>
93+
<entry>Minimum time spent in the statement, in milliseconds</entry>
94+
</row>
95+
96+
<row>
97+
<entry><structfield>max_time</structfield></entry>
98+
<entry><type>double precision</type></entry>
99+
<entry></entry>
100+
<entry>Maximum time spent in the statement, in milliseconds</entry>
101+
</row>
102+
103+
<row>
104+
<entry><structfield>mean_time</structfield></entry>
105+
<entry><type>double precision</type></entry>
106+
<entry></entry>
107+
<entry>Mean time spent in the statement, in milliseconds</entry>
108+
</row>
109+
110+
<row>
111+
<entry><structfield>stddev_time</structfield></entry>
112+
<entry><type>double precision</type></entry>
113+
<entry></entry>
114+
<entry>Population standard deviation of time spent in the statement, in milliseconds</entry>
115+
</row>
116+
89117
<row>
90118
<entry><structfield>rows</structfield></entry>
91119
<entry><type>bigint</type></entry>

0 commit comments

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