Postgresql 中文操作指南

F.29. pg_freespacemap — examine the free space map #

pg_freespacemap 模块提供了一种检查 free space map （FSM） 的方式。确切地说，它提供了一个名为 pg_freespace 的函数，或两个重载函数。这些函数显示在自由空间映射中记录的值，针对某个页面或关系中的所有页面。

The pg_freespacemap module provides a means for examining the free space map (FSM). It provides a function called pg_freespace, or two overloaded functions, to be precise. The functions show the value recorded in the free space map for a given page, or for all pages in the relation.

默认情况下，仅限具有 pg_stat_scan_tables 角色权限的超级用户和角色使用此功能。使用 GRANT 可以向其他人授予访问权限。

By default use is restricted to superusers and roles with privileges of the pg_stat_scan_tables role. Access may be granted to others using GRANT.

F.29.1. Functions #

  • pg_freespace(rel regclass IN, blkno bigint IN) returns int2

    • Returns the amount of free space on the page of the relation, specified by blkno, according to the FSM.

  • pg_freespace(rel regclass IN, blkno OUT bigint, avail OUT int2)

    • Displays the amount of free space on each page of the relation, according to the FSM. A set of (blkno bigint, avail int2) tuples is returned, one tuple for each page in the relation.

存储在空闲空间映射中的值并不精确。其精度被四舍五入至 BLCKSZ 的 1/256（具有默认 BLCKSZ 的 32 个字节），并且在插入和更新元组时，并未完全保持最新。

The values stored in the free space map are not exact. They’re rounded to precision of 1/256th of BLCKSZ (32 bytes with default BLCKSZ), and they’re not kept fully up-to-date as tuples are inserted and updated.

对于索引，跟踪的是未完全使用的页，而不是页内的空闲空间。因此，值没有意义，只表示一个页是满的还是空的。

For indexes, what is tracked is entirely-unused pages, rather than free space within pages. Therefore, the values are not meaningful, just whether a page is full or empty.

F.29.2. Sample Output #

postgres=# SELECT * FROM pg_freespace('foo');
 blkno | avail
-------+-------
     0 |     0
     1 |     0
     2 |     0
     3 |    32
     4 |   704
     5 |   704
     6 |   704
     7 |  1216
     8 |   704
     9 |   704
    10 |   704
    11 |   704
    12 |   704
    13 |   704
    14 |   704
    15 |   704
    16 |   704
    17 |   704
    18 |   704
    19 |  3648
(20 rows)

postgres=# SELECT * FROM pg_freespace('foo', 7);
 pg_freespace
--------------
         1216
(1 row)

F.29.3. Author #

原版本由 Mark Kirkwood < link:mailto:markir@paradise.net.nz[markir@paradise.net.nz]> 编写。在版本 8.4 中根据 Heikki Linnakangas < link:mailto:heikki@enterprisedb.com[heikki@enterprisedb.com]> 的新 FSM 实现重写。

Original version by Mark Kirkwood <link:mailto:markir@paradise.net.nz[markir@paradise.net.nz]>. Rewritten in version 8.4 to suit new FSM implementation by Heikki Linnakangas <link:mailto:heikki@enterprisedb.com[heikki@enterprisedb.com]>