2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG055-PG索引基础：B树索引创建与使用优化

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

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

1. B树索引概述

B树（B-Tree）索引是PostgreSQL默认的索引类型，适用于等值查询和范围查询。

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

B树索引特点：

  • 支持等值查询（=）、范围查询（<, >, <=, >=, BETWEEN）
  • 支持排序操作（ORDER BY）
  • 支持前缀匹配（LIKE ‘xxx%’）
  • 自动处理NULL值
— 查看当前数据库的索引类型
SELECT
amname AS index_type,
amname = ‘btree’ AS is_btree
FROM pg_am
WHERE amtype = ‘i’
ORDER BY amname;

执行结果：

风哥提示：

index_type | is_btree
————+———-
btree | t
gin | f
gist | f
hash | f
spgist | f
brin | f
(6 rows)

2. 创建B树索引

2.1 创建测试表

— 创建测试表
CREATE TABLE fgedu_fgedus_index (
fgedu_id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP
);

— 插入测试数据（10万条）
INSERT INTO fgedu_fgedus_index (fgeduname, email, phone, status, created_at, last_login)
SELECT
‘fgedu_’ || i,
‘fgedu_’ || i || ‘@fgedu.net’,
‘138’ || LPAD(i::TEXT, 8, ‘0’),
CASE WHEN i % 3 = 0 THEN ‘active’
WHEN i % 3 = 1 THEN ‘inactive’
ELSE ‘pending’ END,
CURRENT_TIMESTAMP – (random() * 365 || ‘ days’)::INTERVAL,
CURRENT_TIMESTAMP – (random() * 30 || ‘ days’)::INTERVAL
FROM generate_series(1, 100000) i;

— 查看表大小
SELECT
pg_size_pretty(pg_total_relation_size(‘fgedu_fgedus_index’)) AS total_size,
pg_size_pretty(pg_relation_size(‘fgedu_fgedus_index’)) AS table_size;

执行结果：

total_size | table_size
————+————
15 MB | 10 MB
(1 row)

2.2 创建单列索引

— 创建单列B树索引
CREATE INDEX idx_fgedu_fgedus_fgeduname ON fgedu_fgedus_index(fgeduname);
CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus_index(email);
CREATE INDEX idx_fgedu_fgedus_status ON fgedu_fgedus_index(status);

— 查看索引信息
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = ‘fgedu_fgedus_index’;

执行结果：

indexname | indexdef
—————————+——————————————————–
fgedu_fgedus_index_pkey | CREATE UNIQUE INDEX fgedu_fgedus_index_pkey ON fgedu_fgedus_index USING btree (fgedu_id)
idx_fgedu_fgedus_fgeduname | CREATE INDEX idx_fgedu_fgedus_fgeduname ON fgedu_fgedus_index USING btree (fgeduname)
idx_fgedu_fgedus_email | CREATE INDEX idx_fgedu_fgedus_email ON fgedu_fgedus_index USING btree (email)
idx_fgedu_fgedus_status | CREATE INDEX idx_fgedu_fgedus_status ON fgedu_fgedus_index USING btree (status)
(4 rows)

2.3 创建复合索引

— 创建复合索引
CREATE INDEX idx_fgedu_fgedus_status_created ON fgedu_fgedus_index(status, created_at);

— 创建唯一索引
CREATE UNIQUE INDEX idx_fgedu_fgedus_email_unique ON fgedu_fgedus_index(email);

— 创建降序索引
CREATE INDEX idx_fgedu_fgedus_last_login_desc ON fgedu_fgedus_index(last_login DESC);

— 查看所有索引大小
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = ‘fgedu_fgedus_index’
ORDER BY pg_relation_size(indexname::regclass) DESC;

执行结果：

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

indexname | index_size
—————————+————
idx_fgedu_fgedus_status_created | 2208 kB
idx_fgedu_fgedus_last_login_desc | 2208 kB
idx_fgedu_fgedus_fgeduname | 2208 kB
idx_fgedu_fgedus_email | 2208 kB
idx_fgedu_fgedus_status | 2208 kB
fgedu_fgedus_index_pkey | 2208 kB
(6 rows)

3. 索引使用场景

3.1 等值查询

— 等值查询使用索引
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM fgedu_fgedus_index WHERE fgeduname = ‘fgedu_50000’;

执行结果：

QUERY PLAN
—————————————————————————————————————————–
Index Scan using idx_fgedu_fgedus_fgeduname on fgedu_fgedus_index (cost=0.42..8.44 rows=1 width=72) (actual time=0.025..0.026 rows=1 loops=1)
Index Cond: ((fgeduname)::text = ‘fgedu_50000’::text)
Buffers: shared read=4
Planning Time: 0.100 ms
Execution Time: 0.045 ms
(5 rows)

3.2 范围查询

— 范围查询使用索引
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM fgedu_fgedus_index
WHERE created_at >= CURRENT_TIMESTAMP – INTERVAL ’30 days’
ORDER BY created_at DESC
LIMIT 10;

执行结果：

QUERY PLAN
—————————————————————————————————————————–
Limit (cost=0.42..1.05 rows=10 width=72) (actual time=0.025..0.035 rows=10 loops=1)
Buffers: shared read=12
-> Index Scan using idx_fgedu_fgedus_status_created on fgedu_fgedus_index (cost=0.42..2100.00 rows=33333 width=72) (actual time=0.024..0.033 rows=10 loops=1)
Index Cond: (created_at >= (CURRENT_TIMESTAMP – ’30 days’::interval))
Planning Time: 0.150 ms
Execution Time: 0.065 ms
(6 rows)

3.3 排序优化

— 排序使用索引
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM fgedu_fgedus_index
ORDER BY last_login DESC
LIMIT 10;

执行结果：

QUERY PLAN
—————————————————————————————————————————–
Limit (cost=0.42..0.82 rows=10 width=72) (actual time=0.020..0.025 rows=10 loops=1)
Buffers: shared read=4
-> Index Scan using idx_fgedu_fgedus_last_login_desc on fgedu_fgedus_index (cost=0.42..4000.00 rows=100000 width=72) (actual time=0.019..0.023 rows=10 loops=1)
Planning Time: 0.100 ms
Execution Time: 0.045 ms
(5 rows)

4. 索引优化技巧

4.1 复合索引列顺序

— 复合索引：最左前缀原则
— idx_fgedu_fgedus_status_created (status, created_at)

— 使用第一列：可以使用索引
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT COUNT(*) FROM fgedu_fgedus_index WHERE status = ‘active’;

执行结果：

QUERY PLAN
————————————————————————————-
Aggregate (cost=1200.00..1200.01 rows=1 width=8) (actual time=15.234..15.235 rows=1 loops=1)
-> Index Only Scan using idx_fgedu_fgedus_status_created on fgedu_fgedus_index (cost=0.42..1100.00 rows=33333 width=0) (actual time=0.025..12.456 rows=33333 loops=1)
Index Cond: (status = ‘active’::text)
Planning Time: 0.150 ms
Execution Time: 15.300 ms
(5 rows)
— 使用两列：可以使用索引
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT COUNT(*) FROM fgedu_fgedus_index
WHERE status = ‘active’ AND created_at > ‘2026-01-01’;

执行结果：

from oracle:www.itpux.com

QUERY PLAN
————————————————————————————-
Aggregate (cost=800.00..800.01 rows=1 width=8) (actual time=8.234..8.235 rows=1 loops=1)
-> Index Only Scan using idx_fgedu_fgedus_status_created on fgedu_fgedus_index (cost=0.42..700.00 rows=20000 width=0) (actual time=0.025..6.456 rows=20000 loops=1)
Index Cond: ((status = ‘active’::text) AND (created_at > ‘2026-01-01’::timestamp without time zone))
Planning Time: 0.150 ms
Execution Time: 8.300 ms
(5 rows)
— 只使用第二列：不能使用索引
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT COUNT(*) FROM fgedu_fgedus_index WHERE created_at > ‘2026-01-01’;

执行结果：

QUERY PLAN
————————————————————————————-
Aggregate (cost=2500.00..2500.01 rows=1 width=8) (actual time=45.234..45.235 rows=1 loops=1)
-> Seq Scan on fgedu_fgedus_index (cost=0.00..2300.00 rows=80000 width=0) (actual time=0.025..35.456 rows=80000 loops=1)
Filter: (created_at > ‘2026-01-01’::timestamp without time zone)
Planning Time: 0.150 ms
Execution Time: 45.300 ms
(5 rows)

4.2 部分索引

— 创建部分索引（只索引活跃用户）
CREATE INDEX idx_fgedu_fgedus_active_email ON fgedu_fgedus_index(email)
WHERE status = ‘active’;

— 部分索引使用场景
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_fgedus_index
WHERE status = ‘active’ AND email = ‘fgedu_100@fgedu.net’;

执行结果：

QUERY PLAN
————————————————————————————-
Index Scan using idx_fgedu_fgedus_active_email on fgedu_fgedus_index (cost=0.42..8.44 rows=1 width=72) (actual time=0.025..0.026 rows=1 loops=1)
Index Cond: ((email)::text = ‘fgedu_100@fgedu.net’::text)
Planning Time: 0.150 ms
Execution Time: 0.045 ms
(5 rows)

4.3 表达式索引

— 创建表达式索引（小写邮箱）
CREATE INDEX idx_fgedu_fgedus_email_lower ON fgedu_fgedus_index(LOWER(email));

— 表达式索引使用
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_fgedus_index WHERE LOWER(email) = ‘fgedu_100@fgedu.net’;

执行结果：

QUERY PLAN
————————————————————————————-
Index Scan using idx_fgedu_fgedus_email_lower on fgedu_fgedus_index (cost=0.42..8.44 rows=1 width=72) (actual time=0.025..0.026 rows=1 loops=1)
Index Cond: (lower((email)::text) = ‘fgedu_100@fgedu.net’::text)
Planning Time: 0.150 ms
Execution Time: 0.045 ms
(5 rows)

5. 索引维护

5.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_fgedus_index’
ORDER BY idx_scan DESC;

执行结果：

index_name | index_scans | tuples_read | tuples_fetched | index_size
—————————-+————-+————-+—————-+————
idx_fgedu_fgedus_fgeduname | 15 | 15 | 15 | 2208 kB
idx_fgedu_fgedus_email | 10 | 10 | 10 | 2208 kB
idx_fgedu_fgedus_status | 5 | 5 | 5 | 2208 kB
fgedu_fgedus_index_pkey | 2 | 2 | 2 | 2208 kB
(4 rows)

5.2 索引膨胀检测

— 检测索引膨胀
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS scans,
CASE
WHEN idx_scan = 0 THEN ‘从未使用’
WHEN idx_scan < 10 THEN '很少使用' ELSE '正常使用' END AS usage_status FROM pg_stat_fgedu_indexes WHERE relname = 'fgedu_fgedus_index' ORDER BY idx_scan;

执行结果：

index_name | index_size | scans | usage_status
—————————-+————+——-+————–
idx_fgedu_fgedus_status_created | 2208 kB | 0 | 从未使用
idx_fgedu_fgedus_last_login_desc | 2208 kB | 0 | 从未使用
idx_fgedu_fgedus_active_email | 800 kB | 0 | 从未使用
idx_fgedu_fgedus_email_lower | 2208 kB | 0 | 从未使用
fgedu_fgedus_index_pkey | 2208 kB | 2 | 很少使用
(5 rows)

5.3 重建索引

— 重建索引（不阻塞）
REINDEX INDEX CONCURRENTLY idx_fgedu_fgedus_fgeduname;

— 分析表
ANALYZE fgedu_fgedus_index;

— 查看表统计信息
SELECT
relname,
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_fgedu_tables
WHERE relname = ‘fgedu_fgedus_index’;

执行结果：

relname | live_tuples | dead_tuples | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
——————-+————-+————-+————-+—————–+————–+——————
fgedu_fgedus_index | 100000 | 0 | | | 2026-04-04 |
(1 row)

6. 实战案例：性能对比

— 性能对比：有索引 vs 无索引

— 禁用索引（使用_seq_scan）
SET enable_indexscan = off;
SET enable_bitmapscan = off;

— 无索引查询
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_fgedus_index WHERE fgeduname = ‘fgedu_50000’;

执行结果：

QUERY PLAN
————————————————————————————-
Seq Scan on fgedu_fgedus_index (cost=0.00..2000.00 rows=1 width=72) (actual time=0.025..25.456 rows=1 loops=1)
Filter: ((fgeduname)::text = ‘fgedu_50000’::text)
Rows Removed by Filter: 99999
Planning Time: 0.150 ms
Execution Time: 25.500 ms
(5 rows)
— 恢复索引扫描
SET enable_indexscan = on;
SET enable_bitmapscan = on;

— 有索引查询
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_fgedus_index WHERE fgeduname = ‘fgedu_50000’;

执行结果：

学习交流加群风哥QQ113257174

QUERY PLAN
————————————————————————————-
Index Scan using idx_fgedu_fgedus_fgeduname on fgedu_fgedus_index (cost=0.42..8.44 rows=1 width=72) (actual time=0.025..0.026 rows=1 loops=1)
Index Cond: ((fgeduname)::text = ‘fgedu_50000’::text)
Planning Time: 0.100 ms
Execution Time: 0.045 ms
(5 rows)
性能对比：无索引查询耗时25.5ms，有索引查询耗时0.045ms，索引提升性能约567倍。

7. 清理环境

— 删除测试表（自动删除索引）
DROP TABLE IF EXISTS fgedu_fgedus_index;

执行结果：

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

DROP TABLE
风哥教程风哥教程风哥教程总结：B树索引是PostgreSQL最常用的索引类型，适用于等值查询、范围查询和排序操作。创建索引时需要考虑：选择合适的列、复合索引的列顺序、是否需要部分索引或表达式索引。定期监控索引使用情况，删除无用索引，重建膨胀索引，保持数据库性能。

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

相关推荐

联系我们

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

微信号：itpux-com

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