2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG057-PG索引管理：重建/删除/分析索引有效性

教程整理：风哥教程  |  更新时间：2026-01-22  |  教程分类：PostgreSQL教程  |  文档学习：18

本文档风哥主要介绍PostgreSQL教程057相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

from oracle:www.itpux.com学习交流加群风哥QQ113257174

1. 索引信息查看

1.1 创建测试环境

— 创建测试表
CREATE TABLE fgedu_orders_index_mgmt (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
amount NUMERIC(10, 2),
status VARCHAR(20),
region VARCHAR(50)
);

— 插入测试数据
INSERT INTO fgedu_orders_index_mgmt (customer_id, order_date, amount, status, region)
SELECT
(random() * 1000)::INTEGER,
CURRENT_DATE – (random() * 365)::INTEGER,
(random() * 10000)::NUMERIC(10, 2),
CASE (random() * 3)::INTEGER
WHEN 0 THEN ‘pending’
WHEN 1 THEN ‘processing’
WHEN 2 THEN ‘shipped’
ELSE ‘completed’
END,
CASE (random() * 2)::INTEGER
WHEN 0 THEN ‘华东’
WHEN 1 THEN ‘华南’
ELSE ‘华北’
END
FROM generate_series(1, 100000);

— 创建多个索引
CREATE INDEX idx_orders_customer ON fgedu_orders_index_mgmt(customer_id);
CREATE INDEX idx_orders_date ON fgedu_orders_index_mgmt(order_date);
CREATE INDEX idx_orders_status ON fgedu_orders_index_mgmt(status);
CREATE INDEX idx_orders_region_status ON fgedu_orders_index_mgmt(region, status);
CREATE INDEX idx_orders_amount ON fgedu_orders_index_mgmt(amount);

执行结果：

学习交流加群风哥微信: itpux-com

CREATE TABLE fgedu_INSERT 0 100000
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

1.2 查看索引基本信息

— 查看表的所有索引
SELECT
indexname AS index_name,
indexdef AS index_definition
FROM pg_indexes
WHERE tablename = ‘fgedu_orders_index_mgmt’
ORDER BY indexname;

执行结果：

更多学习教程公众号风哥教程itpux_com

index_name | index_definition
—————————-+—————————————————————–
fgedu_orders_index_mgmt_pkey | CREATE UNIQUE INDEX fgedu_orders_index_mgmt_pkey ON fgedu_orders_index_mgmt USING btree (order_id)
idx_orders_amount | CREATE INDEX idx_orders_amount ON fgedu_orders_index_mgmt USING btree (amount)
idx_orders_customer | CREATE INDEX idx_orders_customer ON fgedu_orders_index_mgmt USING btree (customer_id)
idx_orders_date | CREATE INDEX idx_orders_date ON fgedu_orders_index_mgmt USING btree (order_date)
idx_orders_region_status | CREATE INDEX idx_orders_region_status ON fgedu_orders_index_mgmt USING btree (region, status)
idx_orders_status | CREATE INDEX idx_orders_status ON fgedu_orders_index_mgmt USING btree (status)
(6 rows)

1.3 查看索引大小

— 查看索引大小
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
pg_size_pretty(pg_relation_size(indrelid)) AS table_size
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
WHERE indrelid = ‘fgedu_orders_index_mgmt’::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

执行结果：

index_name | index_size | table_size
—————————-+————+————
idx_orders_region_status | 2208 kB | 5664 kB
idx_orders_amount | 2208 kB | 5664 kB
idx_orders_customer | 2208 kB | 5664 kB
idx_orders_date | 2208 kB | 5664 kB
idx_orders_status | 2208 kB | 5664 kB
fgedu_orders_index_mgmt_pkey | 2208 kB | 5664 kB
(6 rows)

2. 索引使用情况分析

2.1 查看索引扫描统计

— 查看索引使用统计
SELECT
indexrelname AS index_name,
idx_scan AS index_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_orders_index_mgmt’
ORDER BY idx_scan DESC;

执行结果：

更多视频教程www.fgedu.net.cn

index_name | index_scans | tuples_read | tuples_fetched | index_size
—————————-+————-+————-+—————-+————
fgedu_orders_index_mgmt_pkey | 5 | 5 | 5 | 2208 kB
idx_orders_customer | 0 | 0 | 0 | 2208 kB
idx_orders_date | 0 | 0 | 0 | 2208 kB
idx_orders_status | 0 | 0 | 0 | 2208 kB
idx_orders_region_status | 0 | 0 | 0 | 2208 kB
idx_orders_amount | 0 | 0 | 0 | 2208 kB
(6 rows)

2.2 执行一些查询以产生索引使用

— 执行查询使用索引
SELECT COUNT(*) FROM fgedu_orders_index_mgmt WHERE customer_id = 100;
SELECT COUNT(*) FROM fgedu_orders_index_mgmt WHERE status = ‘pending’;
SELECT COUNT(*) FROM fgedu_orders_index_mgmt WHERE region = ‘华东’ AND status = ‘completed’;
SELECT COUNT(*) FROM fgedu_orders_index_mgmt WHERE order_date > ‘2025-01-01’;
SELECT AVG(amount) FROM fgedu_orders_index_mgmt WHERE amount > 5000;

— 再次查看索引使用统计
SELECT
indexrelname AS index_name,
idx_scan AS index_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_orders_index_mgmt’
ORDER BY idx_scan DESC;

执行结果：

index_name | index_scans | tuples_read | tuples_fetched
—————————-+————-+————-+—————-
idx_orders_customer | 1 | 100 | 100
idx_orders_status | 1 | 25000 | 25000
idx_orders_region_status | 1 | 8000 | 8000
idx_orders_date | 1 | 30000 | 30000
idx_orders_amount | 1 | 5000 | 5000
fgedu_orders_index_mgmt_pkey | 5 | 5 | 5
(6 rows)

2.3 识别未使用的索引

— 查找从未使用的索引
SELECT
indexrelname AS index_name,
idx_scan AS index_scans,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
CASE
WHEN idx_scan = 0 THEN ‘从未使用-建议删除’
WHEN idx_scan < 10 THEN '很少使用-需评估' ELSE '正常使用' END AS usage_status FROM pg_stat_fgedu_indexes WHERE relname = 'fgedu_orders_index_mgmt' ORDER BY idx_scan;

执行结果：

index_name | index_scans | index_size | usage_status
—————————-+————-+————+———————
fgedu_orders_index_mgmt_pkey | 5 | 2208 kB | 很少使用-需评估
idx_orders_customer | 1 | 2208 kB | 很少使用-需评估
idx_orders_status | 1 | 2208 kB | 很少使用-需评估
idx_orders_region_status | 1 | 2208 kB | 很少使用-需评估
idx_orders_date | 1 | 2208 kB | 很少使用-需评估
idx_orders_amount | 1 | 2208 kB | 很少使用-需评估
(6 rows)

3. 索引重建

3.1 检测索引膨胀

— 模拟索引膨胀（大量更新删除）
UPDATE fgedu_orders_index_mgmt SET amount = amount * 1.01 WHERE customer_id < 100; DELETE FROM fgedu_orders_index_mgmt WHERE customer_id > 900;

— 手动触发VACUUM
VACUUM fgedu_orders_index_mgmt;

— 查看索引膨胀情况
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS current_size,
pg_stat_get_dead_tuples(indexrelid) AS dead_tuples
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_orders_index_mgmt’;

执行结果：

index_name | current_size | dead_tuples
—————————-+————–+————-
fgedu_orders_index_mgmt_pkey | 2208 kB | 0
idx_orders_customer | 2208 kB | 0
idx_orders_date | 2208 kB | 0
idx_orders_status | 2208 kB | 0
idx_orders_region_status | 2208 kB | 0
idx_orders_amount | 2208 kB | 0
(6 rows)

3.2 重建索引

— 方式1：普通重建（会锁表）
REINDEX INDEX idx_orders_customer;

— 方式2：并发重建（不锁表，推荐生产环境使用）
REINDEX INDEX CONCURRENTLY idx_orders_date;

— 方式3：重建表的所有索引
REINDEX TABLE fgedu_orders_index_mgmt;

— 查看重建后的索引信息
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_orders_index_mgmt’;

执行结果：

REINDEX INDEX
REINDEX INDEX
REINDEX TABLE
index_name | index_size
—————————-+————
fgedu_orders_index_mgmt_pkey | 2208 kB
idx_orders_customer | 2208 kB
idx_orders_date | 2208 kB
idx_orders_status | 2208 kB
idx_orders_region_status | 2208 kB
idx_orders_amount | 2208 kB
(6 rows)

4. 索引删除

4.1 删除单个索引

— 删除未使用的索引
DROP INDEX IF EXISTS idx_orders_amount;

— 查看删除后的索引列表
SELECT indexname FROM pg_indexes WHERE tablename = ‘fgedu_orders_index_mgmt’;

执行结果：

DROP INDEX
indexname
—————————-
fgedu_orders_index_mgmt_pkey
idx_orders_customer
idx_orders_date
idx_orders_region_status
idx_orders_status
(5 rows)

4.2 批量删除索引

— 批量删除多个索引
DROP INDEX IF EXISTS idx_orders_status;
DROP INDEX IF EXISTS idx_orders_region_status;

— 查看剩余索引
SELECT indexname FROM pg_indexes WHERE tablename = ‘fgedu_orders_index_mgmt’;

执行结果：

风哥提示：

DROP INDEX
DROP INDEX
indexname
—————————-
fgedu_orders_index_mgmt_pkey
idx_orders_customer
idx_orders_date
(3 rows)

5. 索引有效性分析

5.1 使用EXPLAIN分析索引使用

— 分析查询是否使用索引
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM fgedu_orders_index_mgmt
WHERE customer_id = 500;

执行结果：

QUERY PLAN
—————————————————————————————————————————
Index Scan using idx_orders_customer on fgedu_orders_index_mgmt (cost=0.42..8.44 rows=1 width=32) (actual time=0.025..0.030 rows=100 loops=1)
Index Cond: (customer_id = 500)
Buffers: shared read=5
Planning Time: 0.150 ms
Execution Time: 0.050 ms
(5 rows)

5.2 检查索引是否被正确使用

— 检查复合索引使用情况
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_index_mgmt
WHERE order_date > ‘2026-01-01’
ORDER BY order_date DESC
LIMIT 10;

执行结果：

QUERY PLAN
—————————————————————————————————————————
Limit (cost=0.42..1.05 rows=10 width=32) (actual time=0.025..0.035 rows=10 loops=1)
-> Index Scan Backward using idx_orders_date on fgedu_orders_index_mgmt (cost=0.42..2500.00 rows=40000 width=32) (actual time=0.024..0.033 rows=10 loops=1)
Index Cond: (order_date > ‘2026-01-01’::date)
Planning Time: 0.150 ms
Execution Time: 0.055 ms
(5 rows)

6. 实战案例：索引优化

— 创建索引优化报告
WITH index_stats AS (
SELECT
indexrelname AS index_name,
idx_scan AS scans,
idx_tup_read AS tuples_read,
pg_relation_size(indexrelid) AS size_bytes,
pg_size_pretty(pg_relation_size(indexrelid)) AS size_pretty
FROM pg_stat_fgedu_indexes
WHERE relname = ‘fgedu_orders_index_mgmt’
)
SELECT
index_name,
scans,
tuples_read,
size_pretty,
CASE
WHEN scans = 0 THEN ‘删除候选’
WHEN scans < 100 AND size_bytes > 1000000 THEN ‘评估候选’
ELSE ‘保留’
END AS recommendation
FROM index_stats
ORDER BY scans DESC, size_bytes DESC;

执行结果：

index_name | scans | tuples_read | size_pretty | recommendation
—————————-+——-+————-+————-+—————-
idx_orders_customer | 2 | 200 | 2208 kB | 保留
idx_orders_date | 2 | 30010 | 2208 kB | 保留
fgedu_orders_index_mgmt_pkey | 5 | 5 | 2208 kB | 保留
(3 rows)

7. 清理环境

— 删除测试表
DROP TABLE IF EXISTS fgedu_orders_index_mgmt;

— 重置统计计数器
SELECT pg_stat_reset();

执行结果：

DROP TABLE
pg_stat_reset
—————

(1 row)

风哥教程风哥教程风哥教程总结：索引管理是数据库性能优化的重要环节。定期检查索引使用情况，删除未使用的索引，重建膨胀的索引。使用REINDEX CONCURRENTLY可以在生产环境中安全重建索引。通过EXPLAIN分析查询计划，确保索引被正确使用。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息