pgstattuple.sql

PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开发团队说,该版本将加速更多企业向该数据库移植.核心开发成员之一Bruce Momjian表示,在新版PostgreSQL

-- Adjust this setting to control where the objects get created.
SET search_path = public;

CREATE TYPE pgstattuple_type AS (
	table_len BIGINT,		-- physical table length in bytes
	tuple_count BIGINT,		-- number of live tuples
	tuple_len BIGINT,		-- total tuples length in bytes
	tuple_percent FLOAT,		-- live tuples in %
	dead_tuple_count BIGINT,	-- number of dead tuples
	dead_tuple_len BIGINT,		-- total dead tuples length in bytes
	dead_tuple_percent FLOAT,	-- dead tuples in %
	free_space BIGINT,		-- free space in bytes
	free_percent FLOAT		-- free space in %
);

CREATE OR REPLACE FUNCTION pgstattuple(text)
RETURNS pgstattuple_type
AS '$libdir/pgstattuple', 'pgstattuple'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION pgstattuple(oid)
RETURNS pgstattuple_type
AS '$libdir/pgstattuple', 'pgstattuplebyid'
LANGUAGE C STRICT;

--
-- pgstatindex
--
CREATE TYPE pgstatindex_type AS (
  version int4,
  tree_level int4,
  index_size int4,
  root_block_no int4,
  internal_pages int4,
  leaf_pages int4,
  empty_pages int4,
  deleted_pages int4,
  avg_leaf_density float8,
  leaf_fragmentation float8
);

CREATE OR REPLACE FUNCTION pgstatindex(text)
RETURNS pgstatindex_type
AS '$libdir/pgstattuple', 'pgstatindex'
LANGUAGE 'C' STRICT;

--
-- bt_metap()
--
CREATE TYPE bt_metap_type AS (
  magic int4,
  version int4,
  root int4,
  level int4,
  fastroot int4,
  fastlevel int4
);

CREATE OR REPLACE FUNCTION bt_metap(text)
RETURNS bt_metap_type
AS '$libdir/pgstattuple', 'bt_metap'
LANGUAGE 'C' STRICT;

--
-- bt_page_stats()
--
CREATE TYPE bt_page_stats_type AS (
  blkno int4,
  type char,
  live_items int4,
  dead_items int4,
  avg_item_size float,
  page_size int4,
  free_size int4,
  btpo_prev int4,
  btpo_next int4,
  btpo int4,
  btpo_flags int4
);

CREATE OR REPLACE FUNCTION bt_page_stats(text, int4)
RETURNS bt_page_stats_type
AS '$libdir/pgstattuple', 'bt_page_stats'
LANGUAGE 'C' STRICT;

--
-- bt_page_items()
--
CREATE TYPE bt_page_items_type AS (
  itemoffset int4,
  ctid tid,
  itemlen int4,
  nulls bool,
  vars bool,
  data text
);

CREATE OR REPLACE FUNCTION bt_page_items(text, int4)
RETURNS SETOF bt_page_items_type
AS '$libdir/pgstattuple', 'bt_page_items'
LANGUAGE 'C' STRICT;

--
-- pg_relpages()
--
CREATE OR REPLACE FUNCTION pg_relpages(text)
RETURNS int
AS '$libdir/pgstattuple', 'pg_relpages'
LANGUAGE 'C' STRICT;