doc/src/sgml/pgstattuple.sgml

   1 <!-- doc/src/sgml/pgstattuple.sgml -->
   2
   3 <sect1 id="pgstattuple" xreflabel="pgstattuple">
   4  <title>pgstattuple &mdash; obtain tuple-level statistics</title>
   5
   6  <indexterm zone="pgstattuple">
   7   <primary>pgstattuple</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>pgstattuple</filename> module provides various functions to
  12   obtain tuple-level statistics.
  13  </para>
  14
  15  <para>
  16   Because these functions return detailed page-level information, access is
  17   restricted by default.  By default, only the
  18   role <literal>pg_stat_scan_tables</literal> has <literal>EXECUTE</literal>
  19   privilege.  Superusers of course bypass this restriction.  After the
  20   extension has been installed, users may issue <command>GRANT</command>
  21   commands to change the privileges on the functions to allow others to
  22   execute them.  However, it might be preferable to add those users to
  23   the <literal>pg_stat_scan_tables</literal> role instead.
  24  </para>
  25
  26  <sect2 id="pgstattuple-funcs">
  27   <title>Functions</title>
  28
  29   <variablelist>
  30    <varlistentry>
  31     <term>
  32      <indexterm>
  33       <primary>pgstattuple</primary>
  34      </indexterm>
  35      <function>pgstattuple(regclass) returns record</function>
  36     </term>
  37
  38     <listitem>
  39      <para>
  40       <function>pgstattuple</function> returns a relation's physical length,
  41       percentage of <quote>dead</quote> tuples, and other info. This may help users
  42       to determine whether vacuum is necessary or not.  The argument is the
  43       target relation's name (optionally schema-qualified) or OID.
  44       For example:
  45 <programlisting>
  46 test=&gt; SELECT * FROM pgstattuple('pg_catalog.pg_proc');
  47 -[ RECORD 1 ]------+-------
  48 table_len          | 458752
  49 tuple_count        | 1470
  50 tuple_len          | 438896
  51 tuple_percent      | 95.67
  52 dead_tuple_count   | 11
  53 dead_tuple_len     | 3157
  54 dead_tuple_percent | 0.69
  55 free_space         | 8932
  56 free_percent       | 1.95
  57 </programlisting>
  58      The output columns are described in <xref linkend="pgstattuple-columns"/>.
  59     </para>
  60
  61     <table id="pgstattuple-columns">
  62      <title><function>pgstattuple</function> Output Columns</title>
  63      <tgroup cols="3">
  64       <thead>
  65        <row>
  66         <entry>Column</entry>
  67         <entry>Type</entry>
  68         <entry>Description</entry>
  69        </row>
  70       </thead>
  71
  72       <tbody>
  73        <row>
  74         <entry><structfield>table_len</structfield></entry>
  75         <entry><type>bigint</type></entry>
  76         <entry>Physical relation length in bytes</entry>
  77        </row>
  78        <row>
  79         <entry><structfield>tuple_count</structfield></entry>
  80         <entry><type>bigint</type></entry>
  81         <entry>Number of live tuples</entry>
  82        </row>
  83        <row>
  84         <entry><structfield>tuple_len</structfield></entry>
  85         <entry><type>bigint</type></entry>
  86         <entry>Total length of live tuples in bytes</entry>
  87        </row>
  88        <row>
  89         <entry><structfield>tuple_percent</structfield></entry>
  90         <entry><type>float8</type></entry>
  91         <entry>Percentage of live tuples</entry>
  92        </row>
  93        <row>
  94         <entry><structfield>dead_tuple_count</structfield></entry>
  95         <entry><type>bigint</type></entry>
  96         <entry>Number of dead tuples</entry>
  97        </row>
  98        <row>
  99         <entry><structfield>dead_tuple_len</structfield></entry>
 100         <entry><type>bigint</type></entry>
 101         <entry>Total length of dead tuples in bytes</entry>
 102        </row>
 103        <row>
 104         <entry><structfield>dead_tuple_percent</structfield></entry>
 105         <entry><type>float8</type></entry>
 106         <entry>Percentage of dead tuples</entry>
 107        </row>
 108        <row>
 109         <entry><structfield>free_space</structfield></entry>
 110         <entry><type>bigint</type></entry>
 111         <entry>Total free space in bytes</entry>
 112        </row>
 113        <row>
 114         <entry><structfield>free_percent</structfield></entry>
 115         <entry><type>float8</type></entry>
 116         <entry>Percentage of free space</entry>
 117        </row>
 118
 119       </tbody>
 120      </tgroup>
 121     </table>
 122
 123     <note>
 124      <para>
 125       The <literal>table_len</literal> will always be greater than the sum
 126       of the <literal>tuple_len</literal>, <literal>dead_tuple_len</literal>
 127       and <literal>free_space</literal>. The difference is accounted for by
 128       fixed page overhead, the per-page table of pointers to tuples, and
 129       padding to ensure that tuples are correctly aligned.
 130      </para>
 131     </note>
 132
 133     <para>
 134      <function>pgstattuple</function> acquires only a read lock on the
 135      relation. So the results do not reflect an instantaneous snapshot;
 136      concurrent updates will affect them.
 137     </para>
 138
 139     <para>
 140      <function>pgstattuple</function> judges a tuple is <quote>dead</quote> if
 141      <function>HeapTupleSatisfiesDirty</function> returns false.
 142     </para>
 143     </listitem>
 144    </varlistentry>
 145
 146    <varlistentry>
 147     <term>
 148      <function>pgstattuple(text) returns record</function>
 149     </term>
 150
 151     <listitem>
 152      <para>
 153       This is the same as <function>pgstattuple(regclass)</function>, except
 154       that the target relation is specified as TEXT. This function is kept
 155       because of backward-compatibility so far, and will be deprecated in
 156       some future release.
 157      </para>
 158     </listitem>
 159    </varlistentry>
 160
 161    <varlistentry>
 162     <term>
 163     <indexterm>
 164      <primary>pgstatindex</primary>
 165     </indexterm>
 166      <function>pgstatindex(regclass) returns record</function>
 167     </term>
 168
 169     <listitem>
 170      <para>
 171       <function>pgstatindex</function> returns a record showing information
 172       about a B-tree index.  For example:
 173 <programlisting>
 174 test=&gt; SELECT * FROM pgstatindex('pg_cast_oid_index');
 175 -[ RECORD 1 ]------+------
 176 version            | 2
 177 tree_level         | 0
 178 index_size         | 16384
 179 root_block_no      | 1
 180 internal_pages     | 0
 181 leaf_pages         | 1
 182 empty_pages        | 0
 183 deleted_pages      | 0
 184 avg_leaf_density   | 54.27
 185 leaf_fragmentation | 0
 186 </programlisting>
 187      </para>
 188
 189     <para>
 190      The output columns are:
 191
 192     <informaltable>
 193      <tgroup cols="3">
 194       <thead>
 195        <row>
 196         <entry>Column</entry>
 197         <entry>Type</entry>
 198         <entry>Description</entry>
 199        </row>
 200       </thead>
 201
 202       <tbody>
 203        <row>
 204         <entry><structfield>version</structfield></entry>
 205         <entry><type>integer</type></entry>
 206         <entry>B-tree version number</entry>
 207        </row>
 208
 209        <row>
 210         <entry><structfield>tree_level</structfield></entry>
 211         <entry><type>integer</type></entry>
 212         <entry>Tree level of the root page</entry>
 213        </row>
 214
 215        <row>
 216         <entry><structfield>index_size</structfield></entry>
 217         <entry><type>bigint</type></entry>
 218         <entry>Total index size in bytes</entry>
 219        </row>
 220
 221        <row>
 222         <entry><structfield>root_block_no</structfield></entry>
 223         <entry><type>bigint</type></entry>
 224         <entry>Location of root page (zero if none)</entry>
 225        </row>
 226
 227        <row>
 228         <entry><structfield>internal_pages</structfield></entry>
 229         <entry><type>bigint</type></entry>
 230         <entry>Number of <quote>internal</quote> (upper-level) pages</entry>
 231        </row>
 232
 233        <row>
 234         <entry><structfield>leaf_pages</structfield></entry>
 235         <entry><type>bigint</type></entry>
 236         <entry>Number of leaf pages</entry>
 237        </row>
 238
 239        <row>
 240         <entry><structfield>empty_pages</structfield></entry>
 241         <entry><type>bigint</type></entry>
 242         <entry>Number of empty pages</entry>
 243        </row>
 244
 245        <row>
 246         <entry><structfield>deleted_pages</structfield></entry>
 247         <entry><type>bigint</type></entry>
 248         <entry>Number of deleted pages</entry>
 249        </row>
 250
 251        <row>
 252         <entry><structfield>avg_leaf_density</structfield></entry>
 253         <entry><type>float8</type></entry>
 254         <entry>Average density of leaf pages</entry>
 255        </row>
 256
 257        <row>
 258         <entry><structfield>leaf_fragmentation</structfield></entry>
 259         <entry><type>float8</type></entry>
 260         <entry>Leaf page fragmentation</entry>
 261        </row>
 262
 263       </tbody>
 264      </tgroup>
 265     </informaltable>
 266     </para>
 267
 268     <para>
 269      The reported <literal>index_size</literal> will normally correspond to one more
 270      page than is accounted for by <literal>internal_pages + leaf_pages +
 271      empty_pages + deleted_pages</literal>, because it also includes the
 272      index's metapage.
 273     </para>
 274
 275     <para>
 276      As with <function>pgstattuple</function>, the results are accumulated
 277      page-by-page, and should not be expected to represent an
 278      instantaneous snapshot of the whole index.
 279     </para>
 280     </listitem>
 281    </varlistentry>
 282
 283    <varlistentry>
 284     <term>
 285      <function>pgstatindex(text) returns record</function>
 286     </term>
 287
 288     <listitem>
 289      <para>
 290       This is the same as <function>pgstatindex(regclass)</function>, except
 291       that the target index is specified as TEXT. This function is kept
 292       because of backward-compatibility so far, and will be deprecated in
 293       some future release.
 294      </para>
 295     </listitem>
 296    </varlistentry>
 297
 298    <varlistentry>
 299     <term>
 300      <indexterm>
 301       <primary>pgstatginindex</primary>
 302      </indexterm>
 303      <function>pgstatginindex(regclass) returns record</function>
 304     </term>
 305
 306     <listitem>
 307      <para>
 308       <function>pgstatginindex</function> returns a record showing information
 309       about a GIN index.  For example:
 310 <programlisting>
 311 test=&gt; SELECT * FROM pgstatginindex('test_gin_index');
 312 -[ RECORD 1 ]--+--
 313 version        | 1
 314 pending_pages  | 0
 315 pending_tuples | 0
 316 </programlisting>
 317      </para>
 318
 319     <para>
 320      The output columns are:
 321
 322     <informaltable>
 323      <tgroup cols="3">
 324       <thead>
 325        <row>
 326         <entry>Column</entry>
 327         <entry>Type</entry>
 328         <entry>Description</entry>
 329        </row>
 330       </thead>
 331
 332       <tbody>
 333        <row>
 334         <entry><structfield>version</structfield></entry>
 335         <entry><type>integer</type></entry>
 336         <entry>GIN version number</entry>
 337        </row>
 338
 339        <row>
 340         <entry><structfield>pending_pages</structfield></entry>
 341         <entry><type>integer</type></entry>
 342         <entry>Number of pages in the pending list</entry>
 343        </row>
 344
 345        <row>
 346         <entry><structfield>pending_tuples</structfield></entry>
 347         <entry><type>bigint</type></entry>
 348         <entry>Number of tuples in the pending list</entry>
 349        </row>
 350
 351       </tbody>
 352      </tgroup>
 353     </informaltable>
 354     </para>
 355     </listitem>
 356    </varlistentry>
 357
 358    <varlistentry>
 359     <term>
 360      <indexterm>
 361       <primary>pgstathashindex</primary>
 362      </indexterm>
 363      <function>pgstathashindex(regclass) returns record</function>
 364     </term>
 365
 366     <listitem>
 367      <para>
 368       <function>pgstathashindex</function> returns a record showing information
 369       about a HASH index.  For example:
 370 <programlisting>
 371 test=&gt; select * from pgstathashindex('con_hash_index');
 372 -[ RECORD 1 ]--+-----------------
 373 version        | 4
 374 bucket_pages   | 33081
 375 overflow_pages | 0
 376 bitmap_pages   | 1
 377 unused_pages   | 32455
 378 live_items     | 10204006
 379 dead_items     | 0
 380 free_percent   | 61.8005949100872
 381 </programlisting>
 382      </para>
 383
 384     <para>
 385      The output columns are:
 386
 387     <informaltable>
 388      <tgroup cols="3">
 389       <thead>
 390        <row>
 391         <entry>Column</entry>
 392         <entry>Type</entry>
 393         <entry>Description</entry>
 394        </row>
 395       </thead>
 396
 397       <tbody>
 398        <row>
 399         <entry><structfield>version</structfield></entry>
 400         <entry><type>integer</type></entry>
 401         <entry>HASH version number</entry>
 402        </row>
 403
 404        <row>
 405         <entry><structfield>bucket_pages</structfield></entry>
 406         <entry><type>bigint</type></entry>
 407         <entry>Number of bucket pages</entry>
 408        </row>
 409
 410        <row>
 411         <entry><structfield>overflow_pages</structfield></entry>
 412         <entry><type>bigint</type></entry>
 413         <entry>Number of overflow pages</entry>
 414        </row>
 415
 416        <row>
 417         <entry><structfield>bitmap_pages</structfield></entry>
 418         <entry><type>bigint</type></entry>
 419         <entry>Number of bitmap pages</entry>
 420        </row>
 421
 422        <row>
 423         <entry><structfield>unused_pages</structfield></entry>
 424         <entry><type>bigint</type></entry>
 425         <entry>Number of unused pages</entry>
 426        </row>
 427
 428        <row>
 429         <entry><structfield>live_items</structfield></entry>
 430         <entry><type>bigint</type></entry>
 431         <entry>Number of live tuples</entry>
 432        </row>
 433
 434        <row>
 435         <entry><structfield>dead_tuples</structfield></entry>
 436         <entry><type>bigint</type></entry>
 437         <entry>Number of dead tuples</entry>
 438        </row>
 439
 440        <row>
 441         <entry><structfield>free_percent</structfield></entry>
 442         <entry><type>float</type></entry>
 443         <entry>Percentage of free space</entry>
 444        </row>
 445
 446       </tbody>
 447      </tgroup>
 448     </informaltable>
 449     </para>
 450     </listitem>
 451    </varlistentry>
 452
 453    <varlistentry>
 454     <term>
 455      <indexterm>
 456       <primary>pg_relpages</primary>
 457      </indexterm>
 458      <function>pg_relpages(regclass) returns bigint</function>
 459     </term>
 460
 461     <listitem>
 462      <para>
 463       <function>pg_relpages</function> returns the number of pages in the
 464       relation.
 465      </para>
 466     </listitem>
 467    </varlistentry>
 468
 469    <varlistentry>
 470     <term>
 471      <function>pg_relpages(text) returns bigint</function>
 472     </term>
 473
 474     <listitem>
 475      <para>
 476       This is the same as <function>pg_relpages(regclass)</function>, except
 477       that the target relation is specified as TEXT. This function is kept
 478       because of backward-compatibility so far, and will be deprecated in
 479       some future release.
 480      </para>
 481     </listitem>
 482    </varlistentry>
 483
 484    <varlistentry>
 485     <term>
 486      <indexterm>
 487       <primary>pgstattuple_approx</primary>
 488      </indexterm>
 489      <function>pgstattuple_approx(regclass) returns record</function>
 490     </term>
 491
 492     <listitem>
 493      <para>
 494       <function>pgstattuple_approx</function> is a faster alternative to
 495       <function>pgstattuple</function> that returns approximate results.
 496       The argument is the target relation's name or OID.
 497       For example:
 498 <programlisting>
 499 test=&gt; SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);
 500 -[ RECORD 1 ]--------+-------
 501 table_len            | 573440
 502 scanned_percent      | 2
 503 approx_tuple_count   | 2740
 504 approx_tuple_len     | 561210
 505 approx_tuple_percent | 97.87
 506 dead_tuple_count     | 0
 507 dead_tuple_len       | 0
 508 dead_tuple_percent   | 0
 509 approx_free_space    | 11996
 510 approx_free_percent  | 2.09
 511 </programlisting>
 512       The output columns are described in <xref linkend="pgstatapprox-columns"/>.
 513      </para>
 514
 515      <para>
 516       Whereas <function>pgstattuple</function> always performs a
 517       full-table scan and returns an exact count of live and dead tuples
 518       (and their sizes) and free space, <function>pgstattuple_approx</function>
 519       tries to avoid the full-table scan and returns exact dead tuple
 520       statistics along with an approximation of the number and
 521       size of live tuples and free space.
 522      </para>
 523
 524      <para>
 525       It does this by skipping pages that have only visible tuples
 526       according to the visibility map (if a page has the corresponding VM
 527       bit set, then it is assumed to contain no dead tuples). For such
 528       pages, it derives the free space value from the free space map, and
 529       assumes that the rest of the space on the page is taken up by live
 530       tuples.
 531      </para>
 532
 533      <para>
 534       For pages that cannot be skipped, it scans each tuple, recording its
 535       presence and size in the appropriate counters, and adding up the
 536       free space on the page. At the end, it estimates the total number of
 537       live tuples based on the number of pages and tuples scanned (in the
 538       same way that VACUUM estimates pg_class.reltuples).
 539      </para>
 540
 541      <table id="pgstatapprox-columns">
 542       <title><function>pgstattuple_approx</function> Output Columns</title>
 543       <tgroup cols="3">
 544        <thead>
 545         <row>
 546          <entry>Column</entry>
 547          <entry>Type</entry>
 548          <entry>Description</entry>
 549         </row>
 550        </thead>
 551
 552        <tbody>
 553         <row>
 554          <entry><structfield>table_len</structfield></entry>
 555          <entry><type>bigint</type></entry>
 556          <entry>Physical relation length in bytes (exact)</entry>
 557         </row>
 558         <row>
 559          <entry><structfield>scanned_percent</structfield></entry>
 560          <entry><type>float8</type></entry>
 561          <entry>Percentage of table scanned</entry>
 562         </row>
 563         <row>
 564          <entry><structfield>approx_tuple_count</structfield></entry>
 565          <entry><type>bigint</type></entry>
 566          <entry>Number of live tuples (estimated)</entry>
 567         </row>
 568         <row>
 569          <entry><structfield>approx_tuple_len</structfield></entry>
 570          <entry><type>bigint</type></entry>
 571          <entry>Total length of live tuples in bytes (estimated)</entry>
 572         </row>
 573         <row>
 574          <entry><structfield>approx_tuple_percent</structfield></entry>
 575          <entry><type>float8</type></entry>
 576          <entry>Percentage of live tuples</entry>
 577         </row>
 578         <row>
 579          <entry><structfield>dead_tuple_count</structfield></entry>
 580          <entry><type>bigint</type></entry>
 581          <entry>Number of dead tuples (exact)</entry>
 582         </row>
 583         <row>
 584          <entry><structfield>dead_tuple_len</structfield></entry>
 585          <entry><type>bigint</type></entry>
 586          <entry>Total length of dead tuples in bytes (exact)</entry>
 587         </row>
 588         <row>
 589          <entry><structfield>dead_tuple_percent</structfield></entry>
 590          <entry><type>float8</type></entry>
 591          <entry>Percentage of dead tuples</entry>
 592         </row>
 593         <row>
 594          <entry><structfield>approx_free_space</structfield></entry>
 595          <entry><type>bigint</type></entry>
 596          <entry>Total free space in bytes (estimated)</entry>
 597         </row>
 598         <row>
 599          <entry><structfield>approx_free_percent</structfield></entry>
 600          <entry><type>float8</type></entry>
 601          <entry>Percentage of free space</entry>
 602         </row>
 603
 604        </tbody>
 605       </tgroup>
 606      </table>
 607
 608      <para>
 609       In the above output, the free space figures may not match the
 610       <function>pgstattuple</function> output exactly, because the free
 611       space map gives us an exact figure, but is not guaranteed to be
 612       accurate to the byte.
 613      </para>
 614
 615     </listitem>
 616    </varlistentry>
 617
 618   </variablelist>
 619  </sect2>
 620
 621  <sect2 id="pgstattuple-authors">
 622   <title>Authors</title>
 623
 624   <para>
 625    Tatsuo Ishii, Satoshi Nagayasu and Abhijit Menon-Sen
 626   </para>
 627  </sect2>
 628
 629 </sect1>