(2013/11/18 20:16), Haribabu kommi wrote:
On 18 October 2013 13:35 KONDO Mitsumasa wrote:
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.


This patch adds another column "shared_blks_hit_percent" to pg_stat_statements 
view
Which is very beneficial to the user to know how much percentage of blks are 
hit.

All changes are fine and working as described. Marked as ready for committer.
Thank you for your reviewing!

However, I'd like to add average time in each statement, too. Attached patch is my latest one. Adding shared_blks_hit_percent and ave_time. This is the adding main code.
+         total_time / calls::float AS avg_time,

If this patch and min/max and stddev patch will be commited, we can see more detail and simple information in pg_stat_statements, by light-weight coding.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
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.2.sql b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
new file mode 100644
index 0000000..41dc16b
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
@@ -0,0 +1,65 @@
+/* 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,
+         total_time / calls::float AS avg_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/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
index f0a8e0f..bb27649 100644
--- 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
@@ -42,6 +42,7 @@ CREATE VIEW pg_stat_statements AS
          query,
          calls,
          total_time,
+         total_time / calls::float AS avg_time,
          rows,
          CASE WHEN shared_blks_hit + shared_blks_read > 0
            THEN 100.0 * (shared_blks_hit::float / (shared_blks_hit + shared_blks_read))
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.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
index c02fdf4..75cd4ff 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -79,6 +79,13 @@
      </row>
 
      <row>
+      <entry><structfield>avg_time</structfield></entry>
+      <entry><type>double precision</type></entry>
+      <entry></entry>
+      <entry>Average time spent in the statement, in milliseconds</entry>
+     </row>
+
+     <row>
       <entry><structfield>rows</structfield></entry>
       <entry><type>bigint</type></entry>
       <entry></entry>
@@ -86,6 +93,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 +378,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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to