postgresql 查看page, index， tuple 详细信息-优快云博客

本文详细介绍如何使用Postgres内置扩展pgstattuple和pageinspect来深入理解表和索引的存储结构，包括页面、元组等关键概念，并提供具体SQL命令帮助读者查询表和索引的详细信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

下面的内容需要理解postgres术语 page, tuple, regclass, relname. sql command 需要用到 pgstattuple,pageinspect extension.

  create extension pgstatuple
  create extension pageinspect

show how many pages in one table
select pg_relpages(regclass)
show one table tuple information
select * from pgstattuple(regclass)
show one table index information
select * from pgstatindex(regclass)
show one page information
select * from page_header(get_raw_page(relname text, 'main', page number))
show one page all tuples information
select * from heap_page_items(get_raw_page(relname text, 'main', page number))
show one index information
select * from bt_metap(relname text);
show one index page information
select * from bt_page_stats(relname text, page number)
show one index page all tuples information
select * from bt_page_items(relname text, page number)
查看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'