Hi,
I submit improvement of pg_stat_statement usage patch in CF3.
In pg_stat_statement, I think buffer hit ratio is very important value. However,
it is difficult to calculate it, and it need complicated SQL. This patch makes
it
more simple usage and documentation.
> -bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
> - nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
> +bench=# SELECT query, calls, total_time, rows, shared_blks_hit_percent
> FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
It will be very simple:-)
This patch conflicts pg_stat_statement_min_max_exectime patch which I submitted,
and pg_stat_statement_min_max_exectime patch also adds new columns which are
min_time and max_time. So I'd like to change it in this opportunity.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index e8aed61..5c63940 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,8 +4,10 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \
- pg_stat_statements--unpackaged--1.0.sql
+DATA = pg_stat_statements--1.2.sql \
+ pg_stat_statements--1.0--1.1.sql \
+ pg_stat_statements--1.1--1.2.sql \
+ pg_stat_statements--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
index 5be281e..5662273 100644
--- a/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql
@@ -1,7 +1,7 @@
/* contrib/pg_stat_statements/pg_stat_statements--1.0--1.1.sql */
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
-\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.1'" to load this file. \quit
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE" to load this file. \quit
/* First we have to remove them from the extension */
ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
new file mode 100644
index 0000000..f0a8e0f
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
@@ -0,0 +1,63 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE" to load this file. \quit
+
+/* First we have to remove them from the extension */
+ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements();
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements();
+
+/* Now redefine */
+CREATE FUNCTION pg_stat_statements(
+ OUT userid oid,
+ OUT dbid oid,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
+CREATE VIEW pg_stat_statements AS
+ SELECT userid,
+ dbid,
+ query,
+ calls,
+ total_time,
+ rows,
+ CASE WHEN shared_blks_hit + shared_blks_read > 0
+ THEN 100.0 * (shared_blks_hit::float / (shared_blks_hit + shared_blks_read))
+ ELSE 0 END AS shared_blks_hit_percent,
+ shared_blks_hit,
+ shared_blks_read,
+ shared_blks_dirtied,
+ shared_blks_written,
+ local_blks_hit,
+ local_blks_read,
+ local_blks_dirtied,
+ local_blks_written,
+ temp_blks_read,
+ temp_blks_written,
+ blk_read_time,
+ blk_write_time
+ FROM pg_stat_statements();
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
deleted file mode 100644
index 42e4d68..0000000
--- a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
+++ /dev/null
@@ -1,43 +0,0 @@
-/* contrib/pg_stat_statements/pg_stat_statements--1.1.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
-
--- Register functions.
-CREATE FUNCTION pg_stat_statements_reset()
-RETURNS void
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
-CREATE FUNCTION pg_stat_statements(
- OUT userid oid,
- OUT dbid oid,
- OUT query text,
- OUT calls int8,
- OUT total_time float8,
- OUT rows int8,
- OUT shared_blks_hit int8,
- OUT shared_blks_read int8,
- OUT shared_blks_dirtied int8,
- OUT shared_blks_written int8,
- OUT local_blks_hit int8,
- OUT local_blks_read int8,
- OUT local_blks_dirtied int8,
- OUT local_blks_written int8,
- OUT temp_blks_read int8,
- OUT temp_blks_written int8,
- OUT blk_read_time float8,
- OUT blk_write_time float8
-)
-RETURNS SETOF record
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
--- Register a view on the function for ease of use.
-CREATE VIEW pg_stat_statements AS
- SELECT * FROM pg_stat_statements();
-
-GRANT SELECT ON pg_stat_statements TO PUBLIC;
-
--- Don't want this to be available to non-superusers.
-REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.2.sql b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
new file mode 100644
index 0000000..e2cc951
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
@@ -0,0 +1,64 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
+
+-- Register functions.
+CREATE FUNCTION pg_stat_statements_reset()
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
+CREATE FUNCTION pg_stat_statements(
+ OUT userid oid,
+ OUT dbid oid,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME'
+LANGUAGE C;
+
+-- Register a view on the function for ease of use.
+CREATE VIEW pg_stat_statements AS
+ SELECT userid,
+ dbid,
+ query,
+ calls,
+ total_time,
+ rows,
+ CASE WHEN shared_blks_hit + shared_blks_read > 0
+ THEN 100.0 * (shared_blks_hit::float / (shared_blks_hit + shared_blks_read))
+ ELSE 0 END AS shared_blks_hit_percent,
+ shared_blks_hit,
+ shared_blks_read,
+ shared_blks_dirtied,
+ shared_blks_written,
+ local_blks_hit,
+ local_blks_read,
+ local_blks_dirtied,
+ local_blks_written,
+ temp_blks_read,
+ temp_blks_written,
+ blk_read_time,
+ blk_write_time
+ FROM pg_stat_statements();
+
+GRANT SELECT ON pg_stat_statements TO PUBLIC;
+
+-- Don't want this to be available to non-superusers.
+REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 428fbb2..6ecf2b6 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track execution statistics of all SQL statements executed'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
old mode 100644
new mode 100755
index c02fdf4..58748d6
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -86,6 +86,13 @@
</row>
<row>
+ <entry><structfield>shared_blks_hit_percent</structfield></entry>
+ <entry><type>double precision</type></entry>
+ <entry></entry>
+ <entry>Shared block cache hit percentage by the statement (shared_blks_hit / (shared_blks_hit + shared_blks_read))</entry>
+ </row>
+
+ <row>
<entry><structfield>shared_blks_hit</structfield></entry>
<entry><type>bigint</type></entry>
<entry></entry>
@@ -364,39 +371,38 @@ $ pgbench -i bench
$ pgbench -c10 -t300 bench
bench=# \x
-bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
- nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
+bench=# SELECT query, calls, total_time, rows, shared_blks_hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
--[ RECORD 1 ]---------------------------------------------------------------------
-query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
-calls | 3000
-total_time | 9609.00100000002
-rows | 2836
-hit_percent | 99.9778970000200936
--[ RECORD 2 ]---------------------------------------------------------------------
-query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
-calls | 3000
-total_time | 8015.156
-rows | 2990
-hit_percent | 99.9731126579631345
--[ RECORD 3 ]---------------------------------------------------------------------
-query | copy pgbench_accounts from stdin
-calls | 1
-total_time | 310.624
-rows | 100000
-hit_percent | 0.30395136778115501520
--[ RECORD 4 ]---------------------------------------------------------------------
-query | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?;
-calls | 3000
-total_time | 271.741999999997
-rows | 3000
-hit_percent | 93.7968855088209426
--[ RECORD 5 ]---------------------------------------------------------------------
-query | alter table pgbench_accounts add primary key (aid)
-calls | 1
-total_time | 81.42
-rows | 0
-hit_percent | 34.4947735191637631
+-[ RECORD 1 ]-----------+-------------------------------------------------------------------
+query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
+calls | 3000
+total_time | 9609.00100000002
+rows | 2836
+shared_blks_hit_percent | 99.9778970000200936
+-[ RECORD 2 ]-----------+-------------------------------------------------------------------
+query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
+calls | 3000
+total_time | 8015.156
+rows | 2990
+shared_blks_hit_percent | 99.9731126579631345
+-[ RECORD 3 ]-----------+-------------------------------------------------------------------
+query | copy pgbench_accounts from stdin
+calls | 1
+total_time | 310.624
+rows | 100000
+shared_blks_hit_percent | 0.30395136778115501520
+-[ RECORD 4 ]-----------+-------------------------------------------------------------------
+query | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?;
+calls | 3000
+total_time | 271.741999999997
+rows | 3000
+shared_blks_hit_percent | 93.7968855088209426
+-[ RECORD 5 ]-----------+-------------------------------------------------------------------
+query | alter table pgbench_accounts add primary key (aid)
+calls | 1
+total_time | 81.42
+rows | 0
+shared_blks_hit_percent | 34.4947735191637631
</screen>
</sect2>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
