diff --git a/contrib/pgstattuple/Makefile b/contrib/pgstattuple/Makefile index fc893d8..e842afc 100644 --- a/contrib/pgstattuple/Makefile +++ b/contrib/pgstattuple/Makefile @@ -4,7 +4,7 @@ MODULE_big = pgstattuple OBJS = pgstattuple.o pgstatindex.o EXTENSION = pgstattuple -DATA = pgstattuple--1.1.sql pgstattuple--1.0--1.1.sql pgstattuple--unpackaged--1.0.sql +DATA = pgstattuple--1.2.sql pgstattuple--1.1--1.2.sql pgstattuple--1.0--1.2.sql pgstattuple--unpackaged--1.0.sql REGRESS = pgstattuple diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out index ab28f50..44c324d 100644 --- a/contrib/pgstattuple/expected/pgstattuple.out +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -6,15 +6,15 @@ CREATE EXTENSION pgstattuple; -- create table test (a int primary key, b int[]); select * from pgstattuple('test'::text); - table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent ------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- - 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent | all_visible_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+--------------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (1 row) select * from pgstattuple('test'::regclass); - table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent ------------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- - 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 + table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent | all_visible_percent +-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+--------------------- + 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 (1 row) select * from pgstatindex('test_pkey'); diff --git a/contrib/pgstattuple/pgstattuple--1.0--1.1.sql b/contrib/pgstattuple/pgstattuple--1.0--1.1.sql deleted file mode 100644 index cf582a0..0000000 --- a/contrib/pgstattuple/pgstattuple--1.0--1.1.sql +++ /dev/null @@ -1,11 +0,0 @@ -/* contrib/pgstattuple/pgstattuple--1.0--1.1.sql */ - --- complain if script is sourced in psql, rather than via ALTER EXTENSION -\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.1'" to load this file. \quit - -CREATE FUNCTION pgstatginindex(IN relname regclass, - OUT version INT4, - OUT pending_pages INT4, - OUT pending_tuples BIGINT) -AS 'MODULE_PATHNAME', 'pgstatginindex' -LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple--1.0--1.2.sql b/contrib/pgstattuple/pgstattuple--1.0--1.2.sql new file mode 100644 index 0000000..051a04b --- /dev/null +++ b/contrib/pgstattuple/pgstattuple--1.0--1.2.sql @@ -0,0 +1,41 @@ +/* contrib/pgstattuple/pgstattuple--1.0--1.2.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.2'" to load this file. \quit + +CREATE FUNCTION pgstatginindex(IN relname regclass, + OUT version INT4, + OUT pending_pages INT4, + OUT pending_tuples BIGINT) +AS 'MODULE_PATHNAME', 'pgstatginindex' +LANGUAGE C STRICT; + +DROP FUNCTION pgstattuple(text); +CREATE OR REPLACE FUNCTION pgstattuple(IN relname text, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8, -- free space in % + OUT all_visible_percent FLOAT8) -- all visible blocks in % +AS 'MODULE_PATHNAME', 'pgstattuple' +LANGUAGE C STRICT; + +DROP FUNCTION pgstattuple(oid); +CREATE OR REPLACE FUNCTION pgstattuple(IN reloid oid, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8, -- free space in % + OUT all_visible_percent FLOAT8) -- all visible blocks in % +AS 'MODULE_PATHNAME', 'pgstattuplebyid' +LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple--1.1--1.2.sql b/contrib/pgstattuple/pgstattuple--1.1--1.2.sql new file mode 100644 index 0000000..124ae3e --- /dev/null +++ b/contrib/pgstattuple/pgstattuple--1.1--1.2.sql @@ -0,0 +1,34 @@ +/* contrib/pgstattuple/pgstattuple--1.1--1.2.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pgstattuple UPDATE TO '1.2'" to load this file. \quit + +DROP FUNCTION pgstattuple(text); +CREATE OR REPLACE FUNCTION pgstattuple(IN relname text, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8, -- free space in % + OUT all_visible_percent FLOAT8) -- all visible blocks in % +AS 'MODULE_PATHNAME', 'pgstattuple' +LANGUAGE C STRICT; + +DROP FUNCTION pgstattuple(oid); +CREATE OR REPLACE FUNCTION pgstattuple(IN reloid oid, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8, -- free space in % + OUT all_visible_percent FLOAT8) -- all visible blocks in % +AS 'MODULE_PATHNAME', 'pgstattuplebyid' +LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple--1.1.sql b/contrib/pgstattuple/pgstattuple--1.1.sql deleted file mode 100644 index b21fbf8..0000000 --- a/contrib/pgstattuple/pgstattuple--1.1.sql +++ /dev/null @@ -1,58 +0,0 @@ -/* contrib/pgstattuple/pgstattuple--1.1.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit - -CREATE FUNCTION pgstattuple(IN relname text, - OUT table_len BIGINT, -- physical table length in bytes - OUT tuple_count BIGINT, -- number of live tuples - OUT tuple_len BIGINT, -- total tuples length in bytes - OUT tuple_percent FLOAT8, -- live tuples in % - OUT dead_tuple_count BIGINT, -- number of dead tuples - OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes - OUT dead_tuple_percent FLOAT8, -- dead tuples in % - OUT free_space BIGINT, -- free space in bytes - OUT free_percent FLOAT8) -- free space in % -AS 'MODULE_PATHNAME', 'pgstattuple' -LANGUAGE C STRICT; - -CREATE FUNCTION pgstattuple(IN reloid oid, - OUT table_len BIGINT, -- physical table length in bytes - OUT tuple_count BIGINT, -- number of live tuples - OUT tuple_len BIGINT, -- total tuples length in bytes - OUT tuple_percent FLOAT8, -- live tuples in % - OUT dead_tuple_count BIGINT, -- number of dead tuples - OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes - OUT dead_tuple_percent FLOAT8, -- dead tuples in % - OUT free_space BIGINT, -- free space in bytes - OUT free_percent FLOAT8) -- free space in % -AS 'MODULE_PATHNAME', 'pgstattuplebyid' -LANGUAGE C STRICT; - -CREATE FUNCTION pgstatindex(IN relname text, - OUT version INT, - OUT tree_level INT, - OUT index_size BIGINT, - OUT root_block_no BIGINT, - OUT internal_pages BIGINT, - OUT leaf_pages BIGINT, - OUT empty_pages BIGINT, - OUT deleted_pages BIGINT, - OUT avg_leaf_density FLOAT8, - OUT leaf_fragmentation FLOAT8) -AS 'MODULE_PATHNAME', 'pgstatindex' -LANGUAGE C STRICT; - -CREATE FUNCTION pg_relpages(IN relname text) -RETURNS BIGINT -AS 'MODULE_PATHNAME', 'pg_relpages' -LANGUAGE C STRICT; - -/* New stuff in 1.1 begins here */ - -CREATE FUNCTION pgstatginindex(IN relname regclass, - OUT version INT4, - OUT pending_pages INT4, - OUT pending_tuples BIGINT) -AS 'MODULE_PATHNAME', 'pgstatginindex' -LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple--1.2.sql b/contrib/pgstattuple/pgstattuple--1.2.sql new file mode 100644 index 0000000..51fed69 --- /dev/null +++ b/contrib/pgstattuple/pgstattuple--1.2.sql @@ -0,0 +1,58 @@ +/* contrib/pgstattuple/pgstattuple--1.2.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pgstattuple" to load this file. \quit + +CREATE FUNCTION pgstattuple(IN relname text, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8, -- free space in % + OUT all_visible_percent FLOAT8) -- all visible blocks in % +AS 'MODULE_PATHNAME', 'pgstattuple' +LANGUAGE C STRICT; + +CREATE FUNCTION pgstattuple(IN reloid oid, + OUT table_len BIGINT, -- physical table length in bytes + OUT tuple_count BIGINT, -- number of live tuples + OUT tuple_len BIGINT, -- total tuples length in bytes + OUT tuple_percent FLOAT8, -- live tuples in % + OUT dead_tuple_count BIGINT, -- number of dead tuples + OUT dead_tuple_len BIGINT, -- total dead tuples length in bytes + OUT dead_tuple_percent FLOAT8, -- dead tuples in % + OUT free_space BIGINT, -- free space in bytes + OUT free_percent FLOAT8, -- free space in % + OUT all_visible_percent FLOAT8) -- all visible blocks in % +AS 'MODULE_PATHNAME', 'pgstattuplebyid' +LANGUAGE C STRICT; + +CREATE FUNCTION pgstatindex(IN relname text, + OUT version INT, + OUT tree_level INT, + OUT index_size BIGINT, + OUT root_block_no BIGINT, + OUT internal_pages BIGINT, + OUT leaf_pages BIGINT, + OUT empty_pages BIGINT, + OUT deleted_pages BIGINT, + OUT avg_leaf_density FLOAT8, + OUT leaf_fragmentation FLOAT8) +AS 'MODULE_PATHNAME', 'pgstatindex' +LANGUAGE C STRICT; + +CREATE FUNCTION pg_relpages(IN relname text) +RETURNS BIGINT +AS 'MODULE_PATHNAME', 'pg_relpages' +LANGUAGE C STRICT; + +CREATE FUNCTION pgstatginindex(IN relname regclass, + OUT version INT4, + OUT pending_pages INT4, + OUT pending_tuples BIGINT) +AS 'MODULE_PATHNAME', 'pgstatginindex' +LANGUAGE C STRICT; diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c index 7f41ec3..1e5d7a5 100644 --- a/contrib/pgstattuple/pgstattuple.c +++ b/contrib/pgstattuple/pgstattuple.c @@ -35,6 +35,7 @@ #include "storage/lmgr.h" #include "utils/builtins.h" #include "utils/tqual.h" +#include "access/visibilitymap.h" PG_MODULE_MAGIC; @@ -59,6 +60,7 @@ typedef struct pgstattuple_type uint64 dead_tuple_count; uint64 dead_tuple_len; uint64 free_space; /* free/reusable space in bytes */ + double all_visible_ratio; } pgstattuple_type; typedef void (*pgstat_page) (pgstattuple_type *, Relation, BlockNumber, @@ -88,7 +90,7 @@ static void pgstat_index_page(pgstattuple_type *stat, Page page, static Datum build_pgstattuple_type(pgstattuple_type *stat, FunctionCallInfo fcinfo) { -#define NCOLUMNS 9 +#define NCOLUMNS 10 #define NCHARS 32 HeapTuple tuple; @@ -97,7 +99,7 @@ build_pgstattuple_type(pgstattuple_type *stat, FunctionCallInfo fcinfo) int i; double tuple_percent; double dead_tuple_percent; - double free_percent; /* free/reusable space in % */ + double free_percent; /* free/reusable space in % */ TupleDesc tupdesc; AttInMetadata *attinmeta; @@ -141,6 +143,7 @@ build_pgstattuple_type(pgstattuple_type *stat, FunctionCallInfo fcinfo) snprintf(values[i++], NCHARS, "%.2f", dead_tuple_percent); snprintf(values[i++], NCHARS, INT64_FORMAT, stat->free_space); snprintf(values[i++], NCHARS, "%.2f", free_percent); + snprintf(values[i++], NCHARS, "%.2f", stat->all_visible_ratio * 100.0); /* build a tuple */ tuple = BuildTupleFromCStrings(attinmeta, values); @@ -275,7 +278,9 @@ pgstat_heap(Relation rel, FunctionCallInfo fcinfo) BlockNumber nblocks; BlockNumber block = 0; /* next block to count free space in */ BlockNumber tupblock; + BlockNumber all_visible_pages = 0; Buffer buffer; + Buffer vmbuffer = InvalidBuffer; pgstattuple_type stat = {0}; BufferAccessStrategy bstrategy; @@ -341,6 +346,26 @@ pgstat_heap(Relation rel, FunctionCallInfo fcinfo) block++; } + /* + * The only reason to check for visibilitymap here is the consistency of + * all_visible_pages against other stats. + */ + for (block = 0 ; block < nblocks ; block++) + { + if (visibilitymap_test(rel, block, &vmbuffer)) + all_visible_pages++; + } + if (vmbuffer != InvalidBuffer) + { + ReleaseBuffer(vmbuffer); + vmbuffer = InvalidBuffer; + } + + if (nblocks > 0) + stat.all_visible_ratio = (double)all_visible_pages / nblocks; + else + stat.all_visible_ratio = 0.0; + relation_close(rel, AccessShareLock); stat.table_len = (uint64) nblocks *BLCKSZ; diff --git a/contrib/pgstattuple/pgstattuple.control b/contrib/pgstattuple/pgstattuple.control index fcfd36f..a7cf47f 100644 --- a/contrib/pgstattuple/pgstattuple.control +++ b/contrib/pgstattuple/pgstattuple.control @@ -1,5 +1,5 @@ # pgstattuple extension comment = 'show tuple-level statistics' -default_version = '1.1' +default_version = '1.2' module_pathname = '$libdir/pgstattuple' relocatable = true