PostgreSQL 元组统计与 pgstattuple 优化

postgres=# SELECT relname, oid, relowner,postgres-#   (pgstattuple(oid)).* postgres-# FROM pg_class postgres-# WHERE relkind = 'r' order by dead_tuple_percent desc limit 5;-[ RECORD 1 ]------+--------------relname            | pg_init_privsoid                | 3394relowner           | 10table_len          | 24576tuple_count        | 222tuple_len          | 17316tuple_percent      | 70.46dead_tuple_count   | 9dead_tuple_len     | 630dead_tuple_percent | 2.56free_space         | 4568free_percent       | 18.59-[ RECORD 2 ]------+--------------relname            | pg_extensionoid                | 3079relowner           | 10table_len          | 8192tuple_count        | 2tuple_len          | 210tuple_percent      | 2.56dead_tuple_count   | 1dead_tuple_len     | 105dead_tuple_percent | 1.28free_space         | 7816free_percent       | 95.41-[ RECORD 3 ]------+--------------relname            | pg_classoid                | 1259relowner           | 10table_len          | 114688tuple_count        | 412tuple_len          | 79912tuple_percent      | 69.68dead_tuple_count   | 5dead_tuple_len     | 860dead_tuple_percent | 0.75free_space         | 28656free_percent       | 24.99-[ RECORD 4 ]------+--------------relname            | pg_procoid                | 1255relowner           | 10table_len          | 786432tuple_count        | 3253tuple_len          | 731586tuple_percent      | 93.03dead_tuple_count   | 12dead_tuple_len     | 4790dead_tuple_percent | 0.61free_space         | 22564free_percent       | 2.87-[ RECORD 5 ]------+--------------relname            | pg_dependoid                | 2608relowner           | 10table_len          | 114688tuple_count        | 1854tuple_len          | 90846tuple_percent      | 79.21dead_tuple_count   | 11dead_tuple_len     | 539dead_tuple_percent | 0.47free_space         | 2288free_percent       | 1.99

2.3.4 pgstatindex(regclass)

2.3.4.1 这个函数返回一条显示b树索引信息的记录

# 1. 例如

SELECT * FROM pgstatindex ('workshop_index');

## 我的查询输出类似如下：

postgres=# SELECT * FROM pgstatindex ('workshop_index');-[ RECORD 1 ]------+--------version            | 4tree_level         | 1index_size         | 1589248root_block_no      | 3internal_pages     | 1leaf_pages         | 192empty_pages        | 0deleted_pages      | 0avg_leaf_density   | 89.74leaf_fragmentation | 0

2.3.4.2 输出列及其描述

-------------------------------------------------------------------|        字段名       |   类型   |              说明                |-------------------------------------------------------------------| version           | integer  | b树版本号                        |-------------------------------------------------------------------| tree_level        | integer  | 根页面的树级别                    |-------------------------------------------------------------------| index_size        | bigint   | 总索引大小(以字节为单位)          |-------------------------------------------------------------------| root_block_no     | bigint   | 根页面的位置(如果没有则为零)       |-------------------------------------------------------------------| internal_pages    | bigint   | “内部”(上层)页面的数量            |-------------------------------------------------------------------| leaf_pages        | bigint   | 叶子结点的个数                    |-------------------------------------------------------------------| empty_pages       | bigint   | 空页的个数                       |-------------------------------------------------------------------| deleted_pages     | bigint   | 删除页面数                       |-------------------------------------------------------------------

2.3.5 pgstatginindex(regclass)

2.3.5.1 这个函数返回一条显示GIN索引信息的记录

  GIN索引参考： https://pganalyze.com/blog/gin-index

# 1. 例1

## 1.1 前期 建表、索引等相关 SQL

-- DROP TABLE IF EXISTS test;CREATE TABLE test (  id bigserial PRIMARY KEY,  data jsonb);INSERT INTO test(data) VALUES ('{"field": "value1"}');INSERT INTO test(data) VALUES ('{"field": "value2"}');INSERT INTO test(data) VALUES ('{"other_field": "value42"}');-- CREATE INDEX ON test USING gin(data jsonb_path_ops);CREATE INDEX test_data_gin_idx ON test USING gin(data jsonb_path_ops);EXPLAIN SELECT * FROM test WHERE data @> '{"field": "value1"}';

## 1.2 测量GIN挂起列表开销和大小

### 1.2.1 首先，我们可以将pgstatginindex函数与类似psql的\watch命令一起使用，以密切关注特定索引

SELECT * FROM pgstatginindex('test_data_gin_idx');

# 我的操作输出类似如下：

postgres=# SELECT * FROM pgstatginindex('test_data_gin_idx'); version | pending_pages | pending_tuples ---------+---------------+----------------       2 |             0 |              0(1 row)

## 注意：如果在创建索引的时候不指定”索引名称”，类似语句 "CREATE INDEX ON test USING gin(data jsonb_path_ops);"，需要先执行类似如下查询，查到其信息（包括索引名称），再根据其查询到的索引名称，将其传参到上面查询（当然：其实如果不指定索引名称的话，其索引命名也是有规律的）。

select *from pg_indexeswhere tablename = 'test';

### 1.2.2 其次，如果您运行自己的数据库服务器，您可以使用 “perf”动态跟踪点 来测量对 Postgres 中 ginInsertCleanup 函数的调用

dnf install perfsudo perf probe -x /opt/pgsql/bin/postgres ginInsertCleanupsudo perf stat -a -e probe_postgres:ginInsertCleanup -- sleep 60

# 2. 例2

## 2.1 前期 建表、索引等相关 SQL

-- DROP TABLE IF EXISTS records;CREATE TABLE records (  id bigserial PRIMARY KEY,  customer_id int4,  data jsonb); CREATE EXTENSION btree_gin;CREATE INDEX ON records USING gin (data, customer_id); EXPLAIN SELECT * FROM records WHERE customer_id = 123 AND data @> '{ "location": "New York" }';EXPLAIN SELECT * FROM records WHERE customer_id = 123; select *from pg_indexeswhere tablename =  'records';

## 注意：如下查询中的 "records_data_customer_id_idx" 就是 上面查询结果的 indexname 字段值

SELECT * FROM pgstatginindex('records_data_customer_id_idx');

# 3. pganalyze索引顾问中的GIN索引支持

## 注意：在 https://pganalyze.com/index-advisor 页面测试

## 现在，我们已经向pganalyze index Advisor添加了对GIN和GIST索引建议的初始支持

## 下面是一个为现有的tsvector列推荐GIN索引的示例

CREATE TABLE post(  id SERIAL PRIMARY KEY,  title TEXT NOT NULL,  content TEXT NOT NULL,  author_id INT NOT NULL,  language text NOT NULL DEFAULT('english'),  document tsvector); SELECT id, title FROM postWHERE post.document @@ to_tsquery('english', 'Endangered & Species')ORDER BY ts_rank(post.document, to_tsquery('english', 'Endangered & Species')) DESC

2.3.6 pgstathashindex(regclass)

2.3.6.1 参考文档

https://postgrespro.com/blog/pgsql/4161321

https://postgrespro.com/docs/postgrespro/15/demodb-bookings-installation

2.3.6.2 测试

# 1. 例如

## 1.1 前期准备

### 1.1.1 从 https://postgrespro.com/docs/postgrespro/15/demodb-bookings-installation 下载 demo-small-en.zip 文件到 /data/software/pg/demo-data/ 并解压（解压后生成 demo-small-en-20170815.sql 文件

### 1.1.2 postgres 用户下执行如下命令

cd /data/software/pg/demo-data/;

psql -f demo-small-en-20170815.sql

## 1.2 为 demo 数据库的 flights 表的 flight_no 字段 创建 hash 索引 & 相关测试

### 1.2.1 创建 hash 索引

psql

\c demo

create index on flights using hash(flight_no);

### 1.2.2 创建 pgstattuple 扩展

#### 因为切换到 demo 数据库了，所以需要重新创建 pgstattuple 扩展

create extension pgstattuple;select * from pg_available_extensions where name='pgstattuple';

### 1.2.3 查询刚才创建的索引

select *from pg_indexeswhere tablename =  'flights';

### 1.2.4 查看相关 sql 执行计划 & 查看相关索引信息

explain (costs off) select * from flights where flight_no = 'PG0001';select * from pgstathashindex('flights_flight_no_idx');

# 最后：我的查询输出类似如下

demo=# explain (costs off) select * from flights where flight_no = 'PG0001';                     QUERY PLAN                     ---------------------------------------------------- Bitmap Heap Scan on flights   Recheck Cond: (flight_no = 'PG0001'::bpchar)   ->  Bitmap Index Scan on flights_flight_no_idx         Index Cond: (flight_no = 'PG0001'::bpchar)(4 rows) demo=# select * from pgstathashindex('flights_flight_no_idx'); version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items |   free_percent    ---------+--------------+----------------+--------------+--------------+------------+------------+-------------------       4 |          128 |             13 |            1 |            0 |      33121 |          0 | 42.36979656038809(1 row)

#PG数据库工程师的摇篮#PostgreSQL考试#PostgreSQL培训

添加图片注释，不超过 140 字（可选）

添加图片注释，不超过 140 字（可选）