doc/src/sgml/pgfreespacemap.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="pgfreespacemap">
   4  <title>pg_freespacemap</title>
   5
   6  <indexterm zone="pgfreespacemap">
   7   <primary>pg_freespacemap</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>pg_freespacemap</> module provides a means for examining the
  12   free space map (FSM). It provides two C functions:
  13   <function>pg_freespacemap_relations</function> and
  14   <function>pg_freespacemap_pages</function> that each return a set of
  15   records, plus two views <structname>pg_freespacemap_relations</structname>
  16   and <structname>pg_freespacemap_pages</structname> that wrap the functions
  17   for convenient use.
  18  </para>
  19
  20  <para>
  21   By default public access is revoked from the functions and views, just in
  22   case there are security issues lurking.
  23  </para>
  24
  25  <sect2>
  26   <title>The <filename>pg_freespacemap</> views</title>
  27
  28   <para>
  29    The definitions of the columns exposed by the views are:
  30   </para>
  31
  32   <table>
  33    <title><structname>pg_freespacemap_relations</> Columns</title>
  34
  35    <tgroup cols="4">
  36     <thead>
  37      <row>
  38       <entry>Name</entry>
  39       <entry>Type</entry>
  40       <entry>References</entry>
  41       <entry>Description</entry>
  42      </row>
  43     </thead>
  44     <tbody>
  45
  46      <row>
  47       <entry><structfield>reltablespace</structfield></entry>
  48       <entry><type>oid</type></entry>
  49       <entry><literal>pg_tablespace.oid</literal></entry>
  50       <entry>Tablespace OID of the relation</entry>
  51      </row>
  52      <row>
  53       <entry><structfield>reldatabase</structfield></entry>
  54       <entry><type>oid</type></entry>
  55       <entry><literal>pg_database.oid</literal></entry>
  56       <entry>Database OID of the relation</entry>
  57      </row>
  58      <row>
  59       <entry><structfield>relfilenode</structfield></entry>
  60       <entry><type>oid</type></entry>
  61       <entry><literal>pg_class.relfilenode</literal></entry>
  62       <entry>Relfilenode of the relation</entry>
  63      </row>
  64      <row>
  65       <entry><structfield>avgrequest</structfield></entry>
  66       <entry><type>integer</type></entry>
  67       <entry></entry>
  68       <entry>Moving average of free space requests (NULL for indexes)</entry>
  69      </row>
  70      <row>
  71       <entry><structfield>interestingpages</structfield></entry>
  72       <entry><type>integer</type></entry>
  73       <entry></entry>
  74       <entry>Count of pages last reported as containing useful free space</entry>
  75      </row>
  76      <row>
  77       <entry><structfield>storedpages</structfield></entry>
  78       <entry><type>integer</type></entry>
  79       <entry></entry>
  80       <entry>Count of pages actually stored in free space map</entry>
  81      </row>
  82      <row>
  83       <entry><structfield>nextpage</structfield></entry>
  84       <entry><type>integer</type></entry>
  85       <entry></entry>
  86       <entry>Page index (from 0) to start next search at</entry>
  87      </row>
  88
  89     </tbody>
  90    </tgroup>
  91   </table>
  92
  93   <table>
  94    <title><structname>pg_freespacemap_pages</> Columns</title>
  95
  96    <tgroup cols="4">
  97     <thead>
  98      <row>
  99       <entry>Name</entry>
 100       <entry>Type</entry>
 101       <entry>References</entry>
 102       <entry>Description</entry>
 103      </row>
 104     </thead>
 105     <tbody>
 106
 107      <row>
 108       <entry><structfield>reltablespace</structfield></entry>
 109       <entry><type>oid</type></entry>
 110       <entry><literal>pg_tablespace.oid</literal></entry>
 111       <entry>Tablespace OID of the relation</entry>
 112      </row>
 113      <row>
 114       <entry><structfield>reldatabase</structfield></entry>
 115       <entry><type>oid</type></entry>
 116       <entry><literal>pg_database.oid</literal></entry>
 117       <entry>Database OID of the relation</entry>
 118      </row>
 119      <row>
 120       <entry><structfield>relfilenode</structfield></entry>
 121       <entry><type>oid</type></entry>
 122       <entry><literal>pg_class.relfilenode</literal></entry>
 123       <entry>Relfilenode of the relation</entry>
 124      </row>
 125      <row>
 126       <entry><structfield>relblocknumber</structfield></entry>
 127       <entry><type>bigint</type></entry>
 128       <entry></entry>
 129       <entry>Page number within the relation</entry>
 130      </row>
 131      <row>
 132       <entry><structfield>bytes</structfield></entry>
 133       <entry><type>integer</type></entry>
 134       <entry></entry>
 135       <entry>Free bytes in the page, or NULL for an index page (see below)</entry>
 136      </row>
 137
 138     </tbody>
 139    </tgroup>
 140   </table>
 141
 142   <para>
 143    For <structname>pg_freespacemap_relations</structname>, there is one row
 144    for each relation in the free space map.
 145    <structfield>storedpages</structfield> is the number of pages actually
 146    stored in the map, while <structfield>interestingpages</structfield> is the
 147    number of pages the last <command>VACUUM</> thought had useful amounts of
 148    free space.
 149   </para>
 150
 151   <para>
 152    If <structfield>storedpages</structfield> is consistently less than
 153    <structfield>interestingpages</> then it'd be a good idea to increase
 154    <varname>max_fsm_pages</varname>.  Also, if the number of rows in
 155    <structname>pg_freespacemap_relations</structname> is close to
 156    <varname>max_fsm_relations</varname>, then you should consider increasing
 157    <varname>max_fsm_relations</varname>.
 158   </para>
 159
 160   <para>
 161    For <structname>pg_freespacemap_pages</structname>, there is one row for
 162    each page in the free space map. The number of rows for a relation will
 163    match the <structfield>storedpages</structfield> column in
 164    <structname>pg_freespacemap_relations</structname>.
 165   </para>
 166
 167   <para>
 168    For indexes, what is tracked is entirely-unused pages, rather than free
 169    space within pages.  Therefore, the average request size and free bytes
 170    within a page are not meaningful, and are shown as NULL.
 171   </para>
 172
 173   <para>
 174    Because the map is shared by all the databases, there will normally be
 175    entries for relations not belonging to the current database.  This means
 176    that there may not be matching join rows in <structname>pg_class</> for
 177    some rows, or that there could even be incorrect joins.  If you are
 178    trying to join against <structname>pg_class</>, it's a good idea to
 179    restrict the join to rows having <structfield>reldatabase</> equal to
 180    the current database's OID or zero.
 181   </para>
 182
 183   <para>
 184    When either of the views is accessed, internal free space map locks are
 185    taken for long enough to copy all the state data that the view will display.
 186    This ensures that the views produce a consistent set of results, while not
 187    blocking normal activity longer than necessary.  Nonetheless there
 188    could be some impact on database performance if they are read often.
 189   </para>
 190  </sect2>
 191
 192  <sect2>
 193   <title>Sample output</title>
 194
 195   <programlisting>
 196 regression=# SELECT c.relname, r.avgrequest, r.interestingpages, r.storedpages
 197              FROM pg_freespacemap_relations r INNER JOIN pg_class c
 198              ON r.relfilenode = c.relfilenode AND
 199                 r.reldatabase IN (0, (SELECT oid FROM pg_database
 200                                       WHERE datname = current_database()))
 201              ORDER BY r.storedpages DESC LIMIT 10;
 202              relname             | avgrequest | interestingpages | storedpages
 203 ---------------------------------+------------+------------------+-------------
 204  onek                            |        256 |              109 |         109
 205  pg_attribute                    |        167 |               93 |          93
 206  pg_class                        |        191 |               49 |          49
 207  pg_attribute_relid_attnam_index |            |               48 |          48
 208  onek2                           |        256 |               37 |          37
 209  pg_depend                       |         95 |               26 |          26
 210  pg_type                         |        199 |               16 |          16
 211  pg_rewrite                      |       1011 |               13 |          13
 212  pg_class_relname_nsp_index      |            |               10 |          10
 213  pg_proc                         |        302 |                8 |           8
 214 (10 rows)
 215
 216 regression=# SELECT c.relname, p.relblocknumber, p.bytes
 217              FROM pg_freespacemap_pages p INNER JOIN pg_class c
 218              ON p.relfilenode = c.relfilenode AND
 219                 p.reldatabase IN (0, (SELECT oid FROM pg_database
 220                                       WHERE datname = current_database()))
 221              ORDER BY c.relname LIMIT 10;
 222    relname    | relblocknumber | bytes
 223 --------------+----------------+-------
 224  a_star       |              0 |  8040
 225  abstime_tbl  |              0 |  7908
 226  aggtest      |              0 |  8008
 227  altinhoid    |              0 |  8128
 228  altstartwith |              0 |  8128
 229  arrtest      |              0 |  7172
 230  b_star       |              0 |  7976
 231  box_tbl      |              0 |  7912
 232  bt_f8_heap   |             54 |  7728
 233  bt_i4_heap   |             49 |  8008
 234 (10 rows)
 235   </programlisting>
 236  </sect2>
 237
 238  <sect2>
 239   <title>Author</title>
 240
 241   <para>
 242    Mark Kirkwood <email>markir@paradise.net.nz</email>
 243   </para>
 244  </sect2>
 245
 246 </sect1>