diff options
Diffstat (limited to 'doc/src/sgml/pgstattuple.sgml')
| -rw-r--r-- | doc/src/sgml/pgstattuple.sgml | 272 |
1 files changed, 194 insertions, 78 deletions
diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml index eaa3a547035..ad52dcd4420 100644 --- a/doc/src/sgml/pgstattuple.sgml +++ b/doc/src/sgml/pgstattuple.sgml @@ -1,29 +1,35 @@ +<!-- $PostgreSQL: pgsql/doc/src/sgml/pgstattuple.sgml,v 1.3 2007/12/10 05:32:51 tgl Exp $ --> <sect1 id="pgstattuple"> <title>pgstattuple</title> - + <indexterm zone="pgstattuple"> <primary>pgstattuple</primary> </indexterm> <para> - <literal>pgstattuple</literal> modules provides various functions to obtain - tuple statistics. + The <filename>pgstattuple</filename> module provides various functions to + obtain tuple-level statistics. </para> <sect2> <title>Functions</title> - <itemizedlist> - <listitem> - <para> - <literal>pgstattuple()</literal> returns the relation length, percentage - of the "dead" tuples of a relation and other info. This may help users to - determine whether vacuum is necessary or not. Here is an example session: - </para> - <programlisting> -test=> \x -Expanded display is on. + <variablelist> + <varlistentry> + <term> + <function>pgstattuple(text) returns record</> + </term> + + <listitem> + <para> + <function>pgstattuple</function> returns a relation's physical length, + percentage of <quote>dead</> 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: + </para> + <programlisting> test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc'); -[ RECORD 1 ]------+------- table_len | 458752 @@ -35,86 +41,111 @@ dead_tuple_len | 3157 dead_tuple_percent | 0.69 free_space | 8932 free_percent | 1.95 - </programlisting> + </programlisting> + <para> - Here are explanations for each column: + The output columns are: </para> - + <table> - <title><literal>pgstattuple()</literal> column descriptions</title> - <tgroup cols="2"> + <title><function>pgstattuple</function> output columns</title> + <tgroup cols="3"> <thead> <row> <entry>Column</entry> + <entry>Type</entry> <entry>Description</entry> </row> </thead> + <tbody> <row> - <entry>table_len</entry> - <entry>physical relation length in bytes</entry> + <entry><structfield>table_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Physical relation length in bytes</entry> + </row> + <row> + <entry><structfield>tuple_count</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of live tuples</entry> </row> <row> - <entry>tuple_count</entry> - <entry>number of live tuples</entry> + <entry><structfield>tuple_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total length of live tuples in bytes</entry> </row> <row> - <entry>tuple_len</entry> - <entry>total tuples length in bytes</entry> + <entry><structfield>tuple_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of live tuples</entry> </row> <row> - <entry>tuple_percent</entry> - <entry>live tuples in %</entry> + <entry><structfield>dead_tuple_count</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Number of dead tuples</entry> </row> <row> - <entry>dead_tuple_len</entry> - <entry>total dead tuples length in bytes</entry> + <entry><structfield>dead_tuple_len</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total length of dead tuples in bytes</entry> </row> <row> - <entry>dead_tuple_percent</entry> - <entry>dead tuples in %</entry> + <entry><structfield>dead_tuple_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of dead tuples</entry> </row> <row> - <entry>free_space</entry> - <entry>free space in bytes</entry> + <entry><structfield>free_space</structfield></entry> + <entry><type>bigint</type></entry> + <entry>Total free space in bytes</entry> </row> <row> - <entry>free_percent</entry> - <entry>free space in %</entry> + <entry><structfield>free_percent</structfield></entry> + <entry><type>float8</type></entry> + <entry>Percentage of free space</entry> </row> + </tbody> </tgroup> </table> - <para> - <note> - <para> - <literal>pgstattuple</literal> acquires only a read lock on the relation. So - concurrent update may affect the result. - </para> - </note> - <note> - <para> - <literal>pgstattuple</literal> judges a tuple is "dead" if HeapTupleSatisfiesNow() - returns false. - </para> - </note> - </para> - </listitem> - - <listitem> <para> - <literal>pg_relpages()</literal> returns the number of pages in the relation. + <function>pgstattuple</function> acquires only a read lock on the + relation. So the results do not reflect an instantaneous snapshot; + concurrent updates will affect them. </para> - </listitem> - <listitem> <para> - <literal>pgstatindex()</literal> returns an array showing the information about an index: + <function>pgstattuple</function> judges a tuple is <quote>dead</> if + <function>HeapTupleSatisfiesNow</> returns false. </para> - <programlisting> -test=> \x -Expanded display is on. + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>pgstattuple(oid) returns record</> + </term> + + <listitem> + <para> + This is the same as <function>pgstattuple(text)</function>, except + that the target relation is specified by OID. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>pgstatindex(text) returns record</> + </term> + + <listitem> + <para> + <function>pgstatindex</function> returns a record showing information + about a btree index. For example: + </para> + <programlisting> test=> SELECT * FROM pgstatindex('pg_cast_oid_index'); -[ RECORD 1 ]------+------ version | 2 @@ -128,31 +159,116 @@ deleted_pages | 0 avg_leaf_density | 50.27 leaf_fragmentation | 0 </programlisting> - </listitem> - </itemizedlist> + + <para> + The output columns are: + </para> + + <table> + <title><function>pgstatindex</function> output columns</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>version</structfield></entry> + <entry><type>integer</type></entry> + <entry>Btree version number</entry> + </row> + + <row> + <entry><structfield>tree_level</structfield></entry> + <entry><type>integer</type></entry> + <entry>Tree level of the root page</entry> + </row> + + <row> + <entry><structfield>index_size</structfield></entry> + <entry><type>integer</type></entry> + <entry>Total number of pages in index</entry> + </row> + + <row> + <entry><structfield>root_block_no</structfield></entry> + <entry><type>integer</type></entry> + <entry>Location of root block</entry> + </row> + + <row> + <entry><structfield>internal_pages</structfield></entry> + <entry><type>integer</type></entry> + <entry>Number of <quote>internal</> (upper-level) pages</entry> + </row> + + <row> + <entry><structfield>leaf_pages</structfield></entry> + <entry><type>integer</type></entry> + <entry>Number of leaf pages</entry> + </row> + + <row> + <entry><structfield>empty_pages</structfield></entry> + <entry><type>integer</type></entry> + <entry>Number of empty pages</entry> + </row> + + <row> + <entry><structfield>deleted_pages</structfield></entry> + <entry><type>integer</type></entry> + <entry>Number of deleted pages</entry> + </row> + + <row> + <entry><structfield>avg_leaf_density</structfield></entry> + <entry><type>float8</type></entry> + <entry>Average density of leaf pages</entry> + </row> + + <row> + <entry><structfield>leaf_fragmentation</structfield></entry> + <entry><type>float8</type></entry> + <entry>Leaf page fragmentation</entry> + </row> + + </tbody> + </tgroup> + </table> + + <para> + As with <function>pgstattuple</>, the results are accumulated + page-by-page, and should not be expected to represent an + instantaneous snapshot of the whole index. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>pg_relpages(text) returns integer</> + </term> + + <listitem> + <para> + <function>pg_relpages</function> returns the number of pages in the + relation. + </para> + </listitem> + </varlistentry> + </variablelist> </sect2> <sect2> - <title>Usage</title> - <para> - <literal>pgstattuple</literal> may be called as a relation function and is - defined as follows: - </para> - <programlisting> - CREATE OR REPLACE FUNCTION pgstattuple(text) RETURNS pgstattuple_type - AS 'MODULE_PATHNAME', 'pgstattuple' - LANGUAGE C STRICT; - - CREATE OR REPLACE FUNCTION pgstattuple(oid) RETURNS pgstattuple_type - AS 'MODULE_PATHNAME', 'pgstattuplebyid' - LANGUAGE C STRICT; - </programlisting> + <title>Author</title> + <para> - The argument is the relation name (optionally it may be qualified) - or the OID of the relation. Note that pgstattuple only returns - one row. + Tatsuo Ishii </para> </sect2> </sect1> - |