Debian Codegraph @

doc/src/sgml/pgstattuple.sgml

raw
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>