PostgreSQL 9.5 new feature - use FSM fast evaluate bloat and other table level statics(pgstattuple_approx)
背景
今天看到一个9.5的patch，是和pgstattuple插件统计有关的，正常情况下，调用pgstattuple统计时需要扫全表。
always performs a full-table scan and returns an exact count of live and dead tuples (and their sizes) and free space。
现在添加了一个函数pgstattuple_approx()，标记为VM的块通过fsm获取并转换信息，而不在VM中的块则需要扫heap page。
这样大大提高了大表输出pgstattuple统计信息的速度。
看到这里，发现这个patch和我以前写的一篇关于统计表的真实膨胀情况的方法是类似的，有兴趣的朋友可参考：
《PostgreSQL Improve fsm & vm output》
http://www.postgresql.org/docs/devel/static/pgstattuple.html
Add pgstattuple_approx() to the pgstattuple extension.
author Andres Freund <andres@anarazel.de>
Wed, 13 May 2015 05:31:04 +0000 (07:31 +0200)
committer Andres Freund <andres@anarazel.de>
Wed, 13 May 2015 05:35:06 +0000 (07:35 +0200)
commit 5850b20f58a594ac69f4f77b24cad94fc3bfd946
tree 94c2b2bbc0ee7f7313400521ce2c8b768a911dfe tree | snapshot
parent dcf5e319089e750d726a0e18a8c90b258c39362c commit | diff
Add pgstattuple_approx() to the pgstattuple extension.
The new function allows to estimate bloat and other table level statics
in a faster, but approximate, way. It does so by using information from
the free space map for pages marked as all visible in the visibility
map. The rest of the table is actually read and free space/bloat is
measured accurately. In many cases that allows to get bloat information
much quicker, causing less IO.
Author: Abhijit Menon-Sen
Reviewed-By: Andres Freund, Amit Kapila and Tomas Vondra
Discussion: 20140402214144.GA28681@kea.toroid.org
文档说明：
+ <varlistentry>
+ <term>
+ <indexterm>
+ <primary>pgstattuple_approx</primary>
+ </indexterm>
+ <function>pgstattuple_approx(regclass) returns record</>
+ </term>
+
+ <listitem>
+ <para>
+ <function>pgstattuple_approx</function> is a faster alternative to
+ <function>pgstattuple</function> that returns approximate results.
+ The argument is the target relation's OID.
+ For example:
+<programlisting>
+test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);
+-[ RECORD 1 ]--------+-------
+table_len | 573440
+scanned_percent | 2
+approx_tuple_count | 2740
+approx_tuple_len | 561210
+approx_tuple_percent | 97.87
+dead_tuple_count | 0
+dead_tuple_len | 0
+dead_tuple_percent | 0
+approx_free_space | 11996
+approx_free_percent | 2.09
+</programlisting>
+ The output columns are described in <xref linkend="pgstatapprox-columns">.
+ </para>
+
+ <para>
+ Whereas <function>pgstattuple</function> always performs a
+ full-table scan and returns an exact count of live and dead tuples
+ (and their sizes) and free space, <function>pgstattuple_approx</function>
+ tries to avoid the full-table scan and returns exact dead tuple
+ statistics along with an approximation of the number and
+ size of live tuples and free space.
+ </para>
+
+ <para>
+ It does this by skipping pages that have only visible tuples
+ according to the visibility map (if a page has the corresponding VM
+ bit set, then it is assumed to contain no dead tuples). For such
+ pages, it derives the free space value from the free space map, and
+ assumes that the rest of the space on the page is taken up by live
+ tuples.
+ </para>
+
+ <para>
+ For pages that cannot be skipped, it scans each tuple, recording its
+ presence and size in the appropriate counters, and adding up the
+ free space on the page. At the end, it estimates the total number of
+ live tuples based on the number of pages and tuples scanned (in the
+ same way that VACUUM estimates pg_class.reltuples).
+ </para>
+
+ <table id="pgstatapprox-columns">
+ <title><function>pgstattuple_approx</function> Output Columns</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Column</entry>
+ <entry>Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>table_len</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Physical relation length in bytes (exact)</entry>
+ </row>
+ <row>
+ <entry><structfield>scanned_percent</structfield></entry>
+ <entry><type>float8</type></entry>
+ <entry>Percentage of table scanned</entry>
+ </row>
+ <row>
+ <entry><structfield>tuple_count</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number of live tuples (estimated)</entry>
+ </row>
+ <row>
+ <entry><structfield>tuple_len</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Total length of live tuples in bytes (estimated)</entry>
+ </row>
+ <row>
+ <entry><structfield>tuple_percent</structfield></entry>
+ <entry><type>float8</type></entry>
+ <entry>Percentage of live tuples</entry>
+ </row>
+ <row>
+ <entry><structfield>dead_tuple_count</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number of dead tuples (exact)</entry>
+ </row>
+ <row>
+ <entry><structfield>dead_tuple_len</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Total length of dead tuples in bytes (exact)</entry>
+ </row>
+ <row>
+ <entry><structfield>dead_tuple_percent</structfield></entry>
+ <entry><type>float8</type></entry>
+ <entry>Percentage of dead tuples</entry>
+ </row>
+ <row>
+ <entry><structfield>approx_free_space</structfield></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Total free space in bytes (estimated)</entry>
+ </row>
+ <row>
+ <entry><structfield>approx_free_percent</structfield></entry>
+ <entry><type>float8</type></entry>
+ <entry>Percentage of free space</entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ In the above output, the free space figures may not match the
+ <function>pgstattuple</function> output exactly, because the free
+ space map gives us an exact figure, but is not guaranteed to be
+ accurate to the byte.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
参考
1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5850b20f58a594ac69f4f77b24cad94fc3bfd946
3. 《PostgreSQL Improve fsm & vm output》
4. http://www.postgresql.org/docs/devel/static/pgstattuple.html
5. http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/pgstattuple.sgml;h=b31fee9b0e1f48675d4013b7e3bea306aa3dedf7;hp=9cabd71166e8e948a610c30f573a7b762b0655ca;hb=5850b20f58a594ac69f4f77b24cad94fc3bfd946;hpb=dcf5e319089e750d726a0e18a8c90b258c39362c