<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >pgstattuple</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.3.1 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Additional Supplied Modules" HREF="contrib.html"><LINK REL="PREVIOUS" TITLE="pg_standby" HREF="pgstandby.html"><LINK REL="NEXT" TITLE="pg_trgm" HREF="pgtrgm.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2008-03-15T03:19:10"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.3.1 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="pgstandby.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="contrib.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Appendix F. Additional Supplied Modules</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="contrib.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="pgtrgm.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="PGSTATTUPLE" >F.24. pgstattuple</A ></H1 ><A NAME="AEN101798" ></A ><P > The <TT CLASS="FILENAME" >pgstattuple</TT > module provides various functions to obtain tuple-level statistics. </P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN101802" >F.24.1. Functions</A ></H2 ><P ></P ><DIV CLASS="VARIABLELIST" ><DL ><DT ><CODE CLASS="FUNCTION" >pgstattuple(text) returns record</CODE ></DT ><DD ><P > <CODE CLASS="FUNCTION" >pgstattuple</CODE > returns a relation's physical length, percentage of <SPAN CLASS="QUOTE" >"dead"</SPAN > tuples, and other info. This may help users to determine whether vacuum is necessary or not. The argument is the target relation's name (optionally schema-qualified). For example: </P ><PRE CLASS="PROGRAMLISTING" >test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc'); -[ RECORD 1 ]------+------- table_len | 458752 tuple_count | 1470 tuple_len | 438896 tuple_percent | 95.67 dead_tuple_count | 11 dead_tuple_len | 3157 dead_tuple_percent | 0.69 free_space | 8932 free_percent | 1.95 </PRE ><P > The output columns are: </P ><DIV CLASS="TABLE" ><A NAME="AEN101814" ></A ><P ><B >Table F-27. <CODE CLASS="FUNCTION" >pgstattuple</CODE > output columns</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><THEAD ><TR ><TH >Column</TH ><TH >Type</TH ><TH >Description</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="STRUCTFIELD" >table_len</TT ></TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD >Physical relation length in bytes</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >tuple_count</TT ></TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD >Number of live tuples</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >tuple_len</TT ></TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD >Total length of live tuples in bytes</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >tuple_percent</TT ></TD ><TD ><TT CLASS="TYPE" >float8</TT ></TD ><TD >Percentage of live tuples</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >dead_tuple_count</TT ></TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD >Number of dead tuples</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >dead_tuple_len</TT ></TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD >Total length of dead tuples in bytes</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >dead_tuple_percent</TT ></TD ><TD ><TT CLASS="TYPE" >float8</TT ></TD ><TD >Percentage of dead tuples</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >free_space</TT ></TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD >Total free space in bytes</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >free_percent</TT ></TD ><TD ><TT CLASS="TYPE" >float8</TT ></TD ><TD >Percentage of free space</TD ></TR ></TBODY ></TABLE ></DIV ><P > <CODE CLASS="FUNCTION" >pgstattuple</CODE > acquires only a read lock on the relation. So the results do not reflect an instantaneous snapshot; concurrent updates will affect them. </P ><P > <CODE CLASS="FUNCTION" >pgstattuple</CODE > judges a tuple is <SPAN CLASS="QUOTE" >"dead"</SPAN > if <CODE CLASS="FUNCTION" >HeapTupleSatisfiesNow</CODE > returns false. </P ></DD ><DT ><CODE CLASS="FUNCTION" >pgstattuple(oid) returns record</CODE ></DT ><DD ><P > This is the same as <CODE CLASS="FUNCTION" >pgstattuple(text)</CODE >, except that the target relation is specified by OID. </P ></DD ><DT ><CODE CLASS="FUNCTION" >pgstatindex(text) returns record</CODE ></DT ><DD ><P > <CODE CLASS="FUNCTION" >pgstatindex</CODE > returns a record showing information about a btree index. For example: </P ><PRE CLASS="PROGRAMLISTING" >test=> SELECT * FROM pgstatindex('pg_cast_oid_index'); -[ RECORD 1 ]------+------ version | 2 tree_level | 0 index_size | 8192 root_block_no | 1 internal_pages | 0 leaf_pages | 1 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 50.27 leaf_fragmentation | 0 </PRE ><P > The output columns are: </P ><DIV CLASS="TABLE" ><A NAME="AEN101898" ></A ><P ><B >Table F-28. <CODE CLASS="FUNCTION" >pgstatindex</CODE > output columns</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><THEAD ><TR ><TH >Column</TH ><TH >Type</TH ><TH >Description</TH ></TR ></THEAD ><TBODY ><TR ><TD ><TT CLASS="STRUCTFIELD" >version</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Btree version number</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >tree_level</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Tree level of the root page</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >index_size</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Total number of pages in index</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >root_block_no</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Location of root block</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >internal_pages</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Number of <SPAN CLASS="QUOTE" >"internal"</SPAN > (upper-level) pages</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >leaf_pages</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Number of leaf pages</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >empty_pages</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Number of empty pages</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >deleted_pages</TT ></TD ><TD ><TT CLASS="TYPE" >integer</TT ></TD ><TD >Number of deleted pages</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >avg_leaf_density</TT ></TD ><TD ><TT CLASS="TYPE" >float8</TT ></TD ><TD >Average density of leaf pages</TD ></TR ><TR ><TD ><TT CLASS="STRUCTFIELD" >leaf_fragmentation</TT ></TD ><TD ><TT CLASS="TYPE" >float8</TT ></TD ><TD >Leaf page fragmentation</TD ></TR ></TBODY ></TABLE ></DIV ><P > As with <CODE CLASS="FUNCTION" >pgstattuple</CODE >, the results are accumulated page-by-page, and should not be expected to represent an instantaneous snapshot of the whole index. </P ></DD ><DT ><CODE CLASS="FUNCTION" >pg_relpages(text) returns integer</CODE ></DT ><DD ><P > <CODE CLASS="FUNCTION" >pg_relpages</CODE > returns the number of pages in the relation. </P ></DD ></DL ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN101977" >F.24.2. Author</A ></H2 ><P > Tatsuo Ishii </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="pgstandby.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="pgtrgm.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >pg_standby</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="contrib.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >pg_trgm</TD ></TR ></TABLE ></DIV ></BODY ></HTML >