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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers