【PostgreSQL如何查看page、index的详细信息】

一、创建插件

为了更好地查看page页的内容，需要安装两个插件

create extension pgstatuple
create extension pageinspect

二、相关函数举例

1.查看表占用多少个page

postgres=# select * from pg_relpages( 't' );
 pg_relpages
-------------
  54055
(1 row)

2.查看一个表的tuple信息

postgres=# select * from pgstattuple('t');
-[ RECORD 1 ]------+----------
table_len | 765607936
tuple_count | 10000000
tuple_len | 720000000
tuple_percent | 94.04
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 2991112
free_percent | 0.39

3.查看一个表上的索引信息

select * from pgstatindex('t'); 
-[ RECORD 1 ]------+--------- 
version            | 4 
tree_level         | 2 
index_size         | 22487040 
root_block_no      | 412 
internal_pages     | 11 
leaf_pages         | 2733 
empty_pages        | 0 
deleted_pages      | 0 
avg_leaf_density   | 90.06 
leaf_fragmentation | 0 

4.查看表t的第0号页的页头信息

select * from page_header(get_raw_page(relname text, 'main', page number));
=====>>eg.

postgres=# select * from page_header(get_raw_page('t','main',0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 1/51015D20 |    29528 |     1 |   776 |   792 |    8192 |     8192 |       4 |      1689
(1 row)

5.查看表t的第0号页中行数据相关信息

SELECT * FROM heap_page_items(get_raw_page(relname text,page number));
=====>>eg.

postgres=# SELECT * FROM heap_page_items(get_raw_page('t',0));
 lp  | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid  | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |           t_data
-----+--------+----------+--------+--------+--------+----------+---------+-------------+------------+--------+--------+-------+----------------------------
   1 |     16 |        2 |      0 |        |        |          |         |             |            |        |        |       |
   2 |    832 |        1 |     36 |   1685 |      0 |        0 | (0,2)   |           2 |       2050 |     24 |        |       | \x0f7570646174650030010000
   3 |   2032 |        1 |     36 |   1665 |      0 |        0 | (0,3)   |           2 |       2306 |     24 |        |       | \x0f757064617465002d010000
   4 |   8112 |        1 |     36 |   1655 |      0 |        0 | (0,4)   |           2 |       2306 |     24 |        |       | \x0f7570646174650058010000
   5 |   8072 |        1 |     36 |   1655 |      0 |        0 | (0,5)   |           2 |       2306 |     24 |        |       | \x0f7570646174650059010000
   6 |   8032 |        1 |     36 |   1655 |      0 |        0 | (0,6)   |           2 |       2306 |     24 |        |       | \x0f757064617465005a010000

6.查看一个B树索引元页的信息

select * from bt_metap(relname text);
=====>>eg.

postgres=# select * from bt_metap('t');
-[ RECORD 1 ]-----------+-------
magic                   | 340322
version                 | 4
root                    | 1
level                   | 0
fastroot                | 1
fastlevel               | 0
oldest_xact             | 0
last_cleanup_num_tuples | 3

7.查看B-树索引单一页面的摘要信息

select * from bt_page_stats(relname text, page number)
=====>>eg.

postgres=# SELECT * FROM bt_page_stats('t', 1);
-[ RECORD 1 ]-+-----
blkno         | 1
type          | l
live_items    | 256
dead_items    | 0
avg_item_size | 12
page_size     | 8192
free_size     | 4056
btpo_prev     | 0
btpo_next     | 0
btpo          | 0
btpo_flags    | 3

8.查看关于B-树索引页面上所有项的详细信息

select * from bt_page_items(relname text, page number)
=====>>eg.

test=# SELECT * FROM bt_page_items('pg_cast_oid_index', 1);
 itemoffset |  ctid   | itemlen | nulls | vars |    data
------------+---------+---------+-------+------+-------------
          1 | (0,1)   |      12 | f     | f    | 23 27 00 00
          2 | (0,2)   |      12 | f     | f    | 24 27 00 00
          3 | (0,3)   |      12 | f     | f    | 25 27 00 00
          4 | (0,4)   |      12 | f     | f    | 26 27 00 00
          5 | (0,5)   |      12 | f     | f    | 27 27 00 00
          6 | (0,6)   |      12 | f     | f    | 28 27 00 00
          7 | (0,7)   |      12 | f     | f    | 29 27 00 00
          8 | (0,8)   |      12 | f     | f    | 2a 27 00 00

9.查看database 所有的含有需要toast子段的table

select t1.relid, t1.schemaname, t1.relname, t2.relid, t2.schemaname, t2.relname from pg_stat_all_tables t1 inner join pg_stat_all_tables t2 on 'pg_toast_'|| t1.relid = t2.relname and t1.schemaname = 'public'
=====>>eg.

postgres=# select t1.relid, t1.schemaname, t1.relname, t2.relid, t2.schemaname, t2.relname from pg_stat_all_tables t1 inner join pg_stat_all_tables t2 on 'pg_toast_'|| t1.relid = t2.relname and t1.schemaname = 'public';
 relid | schemaname |               relname                | relid | schemaname |    relname     
-------+------------+--------------------------------------+-------+------------+----------------
 33222 | public     | datahub_fact_scm_materialgeneral     | 33225 | pg_toast   | pg_toast_33222
 33229 | public     | etl_errorlog                         | 33232 | pg_toast   | pg_toast_33229
 16395 | public     | test                                 | 32781 | pg_toast   | pg_toast_16395
 33216 | public     | datahub_fact_scm_materialgeneral_etl | 33220 | pg_toast   | pg_toast_33216
(4 rows)

10.查看FSM页面的内部节点结构，查看free space map的信息。

select * from fsm_page_contents(page bytea) returns text
=====>>eg.

SELECT fsm_page_contents(get_raw_page('t', 'fsm', 0));
 fsm_page_contents
-------------------
 0: 251           +
 1: 251           +
 3: 251           +
 7: 251           +
 15: 251          +
 31: 251          +
 63: 251          +
 127: 251         +
 255: 251         +
 511: 251         +
 1023: 251        +
 2047: 251        +
 4095: 251        +
 8191: 251        +
 16383: 251       +
 fp_next_slot: 0  +
(1 row)