1<!-- doc/src/sgml/pgstattuple.sgml --> 2 3<sect1 id="pgstattuple" xreflabel="pgstattuple"> 4 <title>pgstattuple</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> 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> 46test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc'); 47-[ RECORD 1 ]------+------- 48table_len | 458752 49tuple_count | 1470 50tuple_len | 438896 51tuple_percent | 95.67 52dead_tuple_count | 11 53dead_tuple_len | 3157 54dead_tuple_percent | 0.69 55free_space | 8932 56free_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> 174test=> SELECT * FROM pgstatindex('pg_cast_oid_index'); 175-[ RECORD 1 ]------+------ 176version | 2 177tree_level | 0 178index_size | 16384 179root_block_no | 1 180internal_pages | 0 181leaf_pages | 1 182empty_pages | 0 183deleted_pages | 0 184avg_leaf_density | 54.27 185leaf_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> 311test=> SELECT * FROM pgstatginindex('test_gin_index'); 312-[ RECORD 1 ]--+-- 313version | 1 314pending_pages | 0 315pending_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> 371test=> select * from pgstathashindex('con_hash_index'); 372-[ RECORD 1 ]--+----------------- 373version | 4 374bucket_pages | 33081 375overflow_pages | 0 376bitmap_pages | 1 377unused_pages | 32455 378live_items | 10204006 379dead_items | 0 380free_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> 499test=> SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass); 500-[ RECORD 1 ]--------+------- 501table_len | 573440 502scanned_percent | 2 503approx_tuple_count | 2740 504approx_tuple_len | 561210 505approx_tuple_percent | 97.87 506dead_tuple_count | 0 507dead_tuple_len | 0 508dead_tuple_percent | 0 509approx_free_space | 11996 510approx_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> 622 <title>Authors</title> 623 624 <para> 625 Tatsuo Ishii, Satoshi Nagayasu and Abhijit Menon-Sen 626 </para> 627 </sect2> 628 629</sect1> 630