• R/O
  • HTTP
  • SSH
  • HTTPS

pg_store_plans: 提交

Record execution plans and their execution statistics.


Commit MetaInfo

修訂55c29918d2e3b256852cedb86800cbf1f5edf70e (tree)
時間2022-12-14 15:57:55
作者Kyotaro Horiguchi <horikyota.ntt@gmai...>
CommiterKyotaro Horiguchi

Log Message

Add new columns to pg_store_plans view

Likewise pg_stat_statements, add temp_blk_read_time and
temp_blk_write_time to pg_store_plans view.
Version is changed to 1.7.

Change Summary

差異

--- a/Makefile
+++ b/Makefile
@@ -1,7 +1,7 @@
11 # pg_stat_plan/Makefile
22
33 MODULES = pg_store_plans
4-STOREPLANSVER = 1.6.1
4+STOREPLANSVER = 1.7
55
66 MODULE_big = pg_store_plans
77 OBJS = pg_store_plans.o pgsp_json.o pgsp_json_text.o pgsp_explain.o
@@ -10,7 +10,7 @@ EXTENSION = pg_store_plans
1010
1111 PG_VERSION := $(shell pg_config --version | sed "s/^PostgreSQL //" | sed "s/\.[0-9]*$$//")
1212
13-DATA = pg_store_plans--1.6.sql pg_store_plans--1.6--1.6.1.sql
13+DATA = pg_store_plans--1.7.sql
1414
1515 REGRESS = convert store
1616 REGRESS_OPTS = --temp-config=regress.conf
@@ -25,8 +25,8 @@ include $(top_builddir)/src/Makefile.global
2525 include $(top_srcdir)/contrib/contrib-global.mk
2626 endif
2727
28-STARBALL14 = pg_store_plans14-$(STOREPLANSVER).tar.gz
29-STARBALLS = $(STARBALL14)
28+STARBALL15 = pg_store_plans15-$(STOREPLANSVER).tar.gz
29+STARBALLS = $(STARBALL15)
3030
3131 TARSOURCES = Makefile *.c *.h \
3232 pg_store_plans--*.sql \
@@ -40,7 +40,7 @@ endif
4040 ## These entries need running server
4141 DBNAME = postgres
4242
43-rpms: rpm14
43+rpms: rpm15
4444
4545 $(STARBALLS): $(TARSOURCES)
4646 if [ -h $(subst .tar.gz,,$@) ]; then rm $(subst .tar.gz,,$@); fi
@@ -52,8 +52,8 @@ $(STARBALLS): $(TARSOURCES)
5252 tar -chzf $@ $(addprefix $(subst .tar.gz,,$@)/, $^)
5353 rm $(subst .tar.gz,,$@)
5454
55-rpm14: $(STARBALL14)
56- MAKE_ROOT=`pwd` rpmbuild -bb SPECS/pg_store_plans14.spec
55+rpm15: $(STARBALL15)
56+ MAKE_ROOT=`pwd` rpmbuild -bb SPECS/pg_store_plans15.spec
5757
5858 testfiles: convert.out convert.sql
5959
--- a/SPECS/pg_store_plans14.spec
+++ b/SPECS/pg_store_plans15.spec
@@ -1,7 +1,7 @@
11 # SPEC file for pg_store_plans
2-# Copyright(c) 2021, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
2+# Copyright(c) 2022, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
33
4-%define _pgdir /usr/pgsql-14
4+%define _pgdir /usr/pgsql-15
55 %define _bindir %{_pgdir}/bin
66 %define _libdir %{_pgdir}/lib
77 %define _datadir %{_pgdir}/share
@@ -14,9 +14,9 @@
1414 %endif
1515
1616 ## Set general information for pg_store_plans.
17-Summary: Record executed plans on PostgreSQL 14
18-Name: pg_store_plans14
19-Version: 1.6.1
17+Summary: Record executed plans on PostgreSQL 15
18+Name: pg_store_plans15
19+Version: 1.7
2020 Release: 1%{?dist}
2121 License: BSD
2222 Group: Applications/Databases
@@ -26,8 +26,8 @@ BuildRoot: %{_tmppath}/%{name}-%{version}-%{release}-%(%{__id_u} -n)
2626 Vendor: NIPPON TELEGRAPH AND TELEPHONE CORPORATION
2727
2828 ## We use postgresql-devel package
29-BuildRequires: postgresql14-devel
30-Requires: postgresql14-libs
29+BuildRequires: postgresql15-devel
30+Requires: postgresql15-libs
3131
3232 ## Description for "pg_store_plans"
3333 %description
@@ -35,19 +35,19 @@ Requires: postgresql14-libs
3535 pg_store_plans provides capability to record statistics for every plan
3636 executed on PostgreSQL.
3737
38-Note that this package is available for only PostgreSQL 14.
38+Note that this package is available for only PostgreSQL 15.
3939
4040 %package llvmjit
41-Requires: postgresql14-server, postgresql14-llvmjit
42-Requires: pg_store_plans14 = 1.6.1
43-Summary: Just-in-time compilation support for pg_store_plans14
41+Requires: postgresql15-server, postgresql15-llvmjit
42+Requires: pg_store_plans15 = 1.7
43+Summary: Just-in-time compilation support for pg_store_plans15
4444
4545 %description llvmjit
46-Just-in-time compilation support for pg_store_plans14
46+Just-in-time compilation support for pg_store_plans15
4747
4848 ## pre work for build pg_store_plans
4949 %prep
50-PATH=/usr/pgsql-14/bin:$PATH
50+PATH=/usr/pgsql-15/bin:$PATH
5151 if [ "${MAKE_ROOT}" != "" ]; then
5252 pushd ${MAKE_ROOT}
5353 make clean %{name}-%{version}.tar.gz
@@ -58,14 +58,14 @@ if [ ! -d %{_rpmdir} ]; then mkdir -p %{_rpmdir}; fi
5858
5959 ## Set variables for build environment
6060 %build
61-PATH=/usr/pgsql-14/bin:$PATH
61+PATH=/usr/pgsql-15/bin:$PATH
6262 pg_config
6363 make USE_PGXS=1 %{?_smp_mflags}
6464
6565 ## Set variables for install
6666 %install
6767 rm -rf %{buildroot}
68-PATH=/usr/pgsql-14/bin:$PATH
68+PATH=/usr/pgsql-15/bin:$PATH
6969 make install DESTDIR=%{buildroot}
7070
7171 %clean
@@ -75,8 +75,7 @@ rm -rf %{buildroot}
7575 %defattr(0755,root,root)
7676 %{_libdir}/pg_store_plans.so
7777 %defattr(0644,root,root)
78-%{_datadir}/extension/pg_store_plans--1.6.sql
79-%{_datadir}/extension/pg_store_plans--1.6--1.6.1.sql
78+%{_datadir}/extension/pg_store_plans--1.7.sql
8079 %{_datadir}/extension/pg_store_plans.control
8180
8281 %files llvmjit
@@ -86,6 +85,8 @@ rm -rf %{buildroot}
8685
8786 # History of pg_store_plans.
8887 %changelog
88+* Wed Dec 14 2022 Kyotaro Horiguchi
89+- Version 1.7. Support PostgreSQL 15.
8990 * Thu Feb 03 2022 Kyotaro Horiguchi
9091 - Version 1.6.1.
9192 * Mon Jan 17 2022 Tatsuro Yamada, Julien Rouhaud, Kyotaro Horiguchi
--- a/docs/index.html
+++ b/docs/index.html
@@ -168,6 +168,18 @@ View</H2>
168168
169169 <TD>Total time the statement using the plan spent writing blocks, in milliseconds (if <A HREF="http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</A> is enabled, otherwise zero)</TD>
170170 </TR>
171+<TR><TD><TT CLASS="STRUCTFIELD">temp_blk_read_time</TT></TD>
172+ <TD><TT CLASS="TYPE">double precision</TT></TD>
173+ <TD>&nbsp;</TD>
174+
175+ <TD>Total time the statement using the plan spent reading temporary file blocks, in milliseconds (if <A HREF="http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</A> is enabled, otherwise zero)</TD>
176+ </TR>
177+<TR><TD><TT CLASS="STRUCTFIELD">temp_blk_write_time</TT></TD>
178+ <TD><TT CLASS="TYPE">double precision</TT></TD>
179+ <TD>&nbsp;</TD>
180+
181+ <TD>Total time the statement using the plan spent writing temporary file blocks, in milliseconds (if <A HREF="http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</A> is enabled, otherwise zero)</TD>
182+ </TR>
171183 <TR><TD><TT CLASS="STRUCTFIELD">first_call</TT></TD>
172184 <TD><TT CLASS="TYPE">timestamp with time zone</TT></TD>
173185 <TD>&nbsp;</TD>
--- a/pg_store_plans--1.6--1.6.1.sql
+++ /dev/null
@@ -1,4 +0,0 @@
1-/* pg_store_plans/pg_store_plans--1.6--1.6.1.sql */
2-
3--- complain if script is sourced in psql, rather than via CREATE EXTENSION
4-\echo Use "CREATE EXTENSION pg_store_plans" to load this file. \quit
--- a/pg_store_plans--1.6.sql
+++ b/pg_store_plans--1.7.sql
@@ -1,4 +1,4 @@
1-/* pg_store_plans/pg_store_plans--1.6.sql */
1+/* pg_store_plans/pg_store_plans--1.7.sql */
22
33 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
44 \echo Use "CREATE EXTENSION pg_store_plans" to load this file. \quit
@@ -82,11 +82,13 @@ CREATE FUNCTION pg_store_plans(
8282 OUT temp_blks_written int8,
8383 OUT blk_read_time float8,
8484 OUT blk_write_time float8,
85+ OUT temp_blk_read_time float8,
86+ OUT temp_blk_write_time float8,
8587 OUT first_call timestamptz,
8688 OUT last_call timestamptz
8789 )
8890 RETURNS SETOF record
89-AS 'MODULE_PATHNAME', 'pg_store_plans_1_6'
91+AS 'MODULE_PATHNAME', 'pg_store_plans_1_7'
9092 LANGUAGE C
9193 VOLATILE PARALLEL SAFE;
9294
--- a/pg_store_plans.c
+++ b/pg_store_plans.c
@@ -71,7 +71,7 @@ PG_MODULE_MAGIC;
7171 static const uint32 PGSP_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
7272
7373 /* This constant defines the magic number in the stats file header */
74-static const uint32 PGSP_FILE_HEADER = 0x20211125;
74+static const uint32 PGSP_FILE_HEADER = 0x20221214;
7575 static int max_plan_len = 5000;
7676
7777 /* XXX: Should USAGE_EXEC reflect execution time and/or buffer usage? */
@@ -98,7 +98,8 @@ typedef uint32 queryid_t;
9898 typedef enum pgspVersion
9999 {
100100 PGSP_V1_5 = 0,
101- PGSP_V1_6
101+ PGSP_V1_6,
102+ PGSP_V1_7
102103 } pgspVersion;
103104
104105 /*
@@ -141,6 +142,10 @@ typedef struct Counters
141142 int64 temp_blks_written; /* # of temp blocks written */
142143 double blk_read_time; /* time spent reading, in msec */
143144 double blk_write_time; /* time spent writing, in msec */
145+ double temp_blk_read_time; /* time spent reading temp blocks,
146+ in msec */
147+ double temp_blk_write_time;/* time spent writing temp blocks,
148+ in msec */
144149 TimestampTz first_call; /* timestamp of first call */
145150 TimestampTz last_call; /* timestamp of last call */
146151 double usage; /* usage factor */
@@ -313,6 +318,7 @@ PG_FUNCTION_INFO_V1(pg_store_plans_reset);
313318 PG_FUNCTION_INFO_V1(pg_store_plans_hash_query);
314319 PG_FUNCTION_INFO_V1(pg_store_plans);
315320 PG_FUNCTION_INFO_V1(pg_store_plans_1_6);
321+PG_FUNCTION_INFO_V1(pg_store_plans_1_7);
316322 PG_FUNCTION_INFO_V1(pg_store_plans_shorten);
317323 PG_FUNCTION_INFO_V1(pg_store_plans_normalize);
318324 PG_FUNCTION_INFO_V1(pg_store_plans_jsonplan);
@@ -386,7 +392,6 @@ _PG_init(void)
386392 */
387393 if (!process_shared_preload_libraries_in_progress)
388394 return;
389-
390395 #if PG_VERSION_NUM >= 140000
391396 /*
392397 * Inform the postmaster that we want to enable query_id calculation if
@@ -1348,8 +1353,12 @@ pgsp_store(char *plan, queryid_t queryId,
13481353 e->counters.local_blks_written += bufusage->local_blks_written;
13491354 e->counters.temp_blks_read += bufusage->temp_blks_read;
13501355 e->counters.temp_blks_written += bufusage->temp_blks_written;
1356+
13511357 e->counters.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
13521358 e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
1359+ e->counters.temp_blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_read_time);
1360+ e->counters.temp_blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->temp_blk_write_time);
1361+
13531362 e->counters.last_call = GetCurrentTimestamp();
13541363 e->counters.usage += USAGE_EXEC(total_time);
13551364
@@ -1380,12 +1389,21 @@ pg_store_plans_reset(PG_FUNCTION_ARGS)
13801389 /* Number of output arguments (columns) for various API versions */
13811390 #define PG_STORE_PLANS_COLS_V1_5 27
13821391 #define PG_STORE_PLANS_COLS_V1_6 26
1383-#define PG_STORE_PLANS_COLS 27 /* maximum of above */
1392+#define PG_STORE_PLANS_COLS_V1_7 28
1393+#define PG_STORE_PLANS_COLS 28 /* maximum of above */
13841394
13851395 /*
13861396 * Retrieve statement statistics.
13871397 */
13881398 Datum
1399+pg_store_plans_1_7(PG_FUNCTION_ARGS)
1400+{
1401+ pg_store_plans_internal(fcinfo, PGSP_V1_7);
1402+
1403+ return (Datum) 0;
1404+}
1405+
1406+Datum
13891407 pg_store_plans_1_6(PG_FUNCTION_ARGS)
13901408 {
13911409 pg_store_plans_internal(fcinfo, PGSP_V1_6);
@@ -1637,11 +1655,19 @@ pg_store_plans_internal(FunctionCallInfo fcinfo,
16371655 values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
16381656 values[i++] = Float8GetDatumFast(tmp.blk_read_time);
16391657 values[i++] = Float8GetDatumFast(tmp.blk_write_time);
1658+
1659+ if (api_version >= PGSP_V1_7)
1660+ {
1661+ values[i++] = Float8GetDatumFast(tmp.temp_blk_read_time);
1662+ values[i++] = Float8GetDatumFast(tmp.temp_blk_write_time);
1663+ }
1664+
16401665 values[i++] = TimestampTzGetDatum(tmp.first_call);
16411666 values[i++] = TimestampTzGetDatum(tmp.last_call);
16421667
16431668 Assert(i == (api_version == PGSP_V1_5 ? PG_STORE_PLANS_COLS_V1_5 :
16441669 api_version == PGSP_V1_6 ? PG_STORE_PLANS_COLS_V1_6 :
1670+ api_version == PGSP_V1_7 ? PG_STORE_PLANS_COLS_V1_7 :
16451671 -1 /* fail if you forget to update this assert */ ));
16461672
16471673 tuplestore_putvalues(tupstore, tupdesc, values, nulls);
--- a/pg_store_plans.control
+++ b/pg_store_plans.control
@@ -1,5 +1,5 @@
11 # pg_store_plans extension
22 comment = 'track plan statistics of all SQL statements executed'
3-default_version = '1.6.1'
3+default_version = '1.7'
44 module_pathname = '$libdir/pg_store_plans'
55 relocatable = true
Show on old repository browser