PostgreSQL: PostgreSQL索引原理与优化

Q: 索引越多越好吗？

不是。每个索引增加写入开销（INSERT/UPDATE/DELETE 需维护索引）和存储空间。只为高频查询建索引，定期用 pg_stat_user_indexes 检查使用率。

Q: CONCURRENTLY 建索引失败怎么办？

CONCURRENTLY 失败会留下 INVALID 索引，用 SELECT indexname FROM pg_indexes WHERE indexdef LIKE '%INVALID%' 或 \d+ tbl 查看，然后 DROP INDEX 删除。

Q: 为什么我的 LIKE 查询不用索引？

LIKE 'abc%' 可以用 B-Tree； LIKE '%abc' 前缀通配不行，需 GIN + pg_trgm 扩展支持。

Q: BRIN 索引适合什么场景？

物理有序的大表（如按时间追加写入的日志表）。如果数据与物理顺序无关，BRIN 近似过滤效果差，不推荐。

Q: 部分索引对写入性能有影响吗？

影响比全量索引小。不满足 WHERE 条件的行写入时不需要更新部分索引，节省了 I/O。

Q: 复合索引(a, b)能否支持 WHERE a=1 ORDER BY b？

可以。复合索引按 a 排序后按 b 排序，能同时满足 WHERE 过滤和 ORDER BY，避免额外排序。

最后更新：2026-08-26

1. 你将学到

PostgreSQL 6 种索引类型：B-Tree / Hash / GIN / GiST / BRIN / SP-GiST
CREATE INDEX / UNIQUE INDEX / CONCURRENTLY
部分索引（Partial Index，WHERE 条件）——PostgreSQL 特色
表达式索引（Expression Index）
复合索引与最左前缀原则
EXPLAIN / EXPLAIN ANALYZE 解读执行计划
REINDEX 索引维护
索引失效的常见场景

2. 故事

Bob 是电商平台的 DBA。用户反馈商品搜索页面响应越来越慢——JSONB 字段的全量扫描导致查询耗时 3 秒。

Bob 给 JSONB 列加上 GIN 索引后，搜索降到 300 毫秒。接着他发现活跃用户查询也慢，但 90% 的用户已经注销，给全表建索引浪费空间。他用部分索引只索引 status = 'active' 的行，索引体积缩小 80%，查询更快。

3. Concept：索引类型概览

(1) 六种索引类型

索引类型	全称	适用数据类型	典型场景
B-Tree	Balanced Tree	所有可排序类型	等值、范围、排序、前缀LIKE
Hash	Hash Table	所有类型	简单等值查询
GIN	Generalized Inverted Index	数组、JSONB、全文搜索	包含查询、全文检索
GiST	Generalized Search Tree	几何、范围、全文	空间查询、最近邻
BRIN	Block Range Index	大表有序列	时序数据、按时间范围
SP-GiST	Space-Partitioned GiST	不规则分区结构	电话号码、路由

▶ 示例：查看表的已有索引

SQL

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

TEXT 📖 仅展示

 indexname              | indexdef
------------------------+--------------------------------------------------
 orders_pkey            | CREATE UNIQUE INDEX ... ON orders USING btree (order_id)
 idx_orders_customer_id | CREATE INDEX ... ON orders USING btree (customer_id)

(2) 索引类型选择决策

flowchart TD
    A{查询类型?} -->|等值/范围/排序| B[B-Tree]
    A -->|简单等值| C[Hash]
    A -->|数组/JSONB包含| D[GIN]
    A -->|空间/几何/最近邻| E[GiST]
    A -->|大表有序列扫描| F[BRIN]
    A -->|不规则分区结构| G[SP-GiST]

    B --> B1["默认索引类型<br/>适用90%场景"]
    C --> C1["少用<br/>B-Tree通常更好"]
    D --> D1["JSONB @> ?<br/>array @> <br/>tsvector @@ "]
    E --> E1["PostGIS<br/>range overlap"]
    F --> F1["千万级时序表<br/>体积极小"]
    G --> G1["电话前缀<br/>IP路由"]

    style A fill:#e1f5fe
    style B fill:#c8e6c9
    style D fill:#fff9c4
    style F fill:#fff9c4

4. Concept：B-Tree 索引

(1) B-Tree 特性与适用场景

B-Tree 是 PostgreSQL 的默认索引类型，支持等值、范围、排序、IS NULL、前缀 LIKE 查询。

支持的操作符	示例
等值	`WHERE col = 100`
范围	`WHERE col > 100 AND col < 200`
排序	`ORDER BY col`
IS NULL	`WHERE col IS NULL`
前缀 LIKE	`WHERE col LIKE 'abc%'`
BETWEEN	`WHERE col BETWEEN 1 AND 10`

不支持	原因
`LIKE '%abc'`	前缀通配符无法利用 B-Tree 排序
`col::text = '100'`	类型不匹配，需表达式索引
`LOWER(col) = 'abc'`	函数结果未索引，需表达式索引

▶ 示例：创建 B-Tree 索引

SQL

CREATE INDEX idx_orders_amount ON orders (amount);

CREATE INDEX idx_orders_date_amount ON orders (order_date, amount DESC);

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：UNIQUE 索引

SQL

CREATE UNIQUE INDEX idx_users_email ON users (email);

TEXT 📖 仅展示

CREATE TABLE

UNIQUE 索引同时保证数据唯一性和查询性能。

5. Concept：GIN 索引

(1) GIN 索引原理

GIN（Generalized Inverted Index）是倒排索引：从元素到包含该元素的行的映射。适用于"包含"类查询。

适用类型	操作符	示例
JSONB	`@>` `?` `?	?&`
Array	`@>` `<@` `&&`	`WHERE tags @> ARRAY['sale']`
tsvector	`@@`	`WHERE body @@ to_tsquery('postgres')`

▶ 示例：JSONB 字段 GIN 索引

SQL

CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

SELECT product_id, name
FROM products
WHERE attrs @> '{"category": "electronics"}';

TEXT 📖 仅展示

 product_id |    name
------------+------------
        101 | Laptop Pro
        205 | Smart Watch

▶ 示例：数组字段 GIN 索引

SQL

CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT product_id, name
FROM products
WHERE tags @> ARRAY['summer', 'sale'];

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

▶ 示例：全文搜索 GIN 索引

SQL

CREATE INDEX idx_articles_body ON articles USING GIN (to_tsvector('english', body));

SELECT id, title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('postgresql & index');

TEXT 📖 仅展示

CREATE TABLE

(2) GIN vs B-Tree 对比

维度	B-Tree	GIN
查询类型	等值/范围	包含/搜索
写入速度	快	慢（需更新倒排列表）
索引体积	中等	较大
适用类型	标量	数组/JSONB/全文
排序支持	是	否

6. Concept：GiST / BRIN / SP-GiST / Hash

(1) GiST 索引

GiST 是通用搜索树框架，支持自定义分区策略。典型用途是空间数据。

用途	操作符	扩展
几何数据	`&&` `@` `<@`	PostGIS
范围类型	`&&` `@>` `<@`	内置
全文搜索	`@@`	内置

▶ 示例：范围类型 GiST 索引

SQL

CREATE INDEX idx_events_time_range ON events USING GiST (time_range);

SELECT event_id, title
FROM events
WHERE time_range && daterange('2025-01-01', '2025-03-01');

TEXT 📖 仅展示

CREATE TABLE

(2) BRIN 索引

BRIN（Block Range Index）存储每个数据块的摘要信息（最小值、最大值），体积极小，适合物理有序的大表。

维度	B-Tree	BRIN
索引体积	大	极小（约1/1000）
精确度	精确	近似（可能多扫一些块）
维护成本	高	极低
适用场景	随机查询	时序数据范围扫描

▶ 示例：时序表 BRIN 索引

SQL

CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at)
  WITH (pages_per_range = 32);

SELECT count(*) FROM logs
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30';

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

(3) SP-GiST 索引

SP-GiST 适合非平衡分区结构，如电话号码前缀、IP 路由。

▶ 示例：电话前缀 SP-GiST 索引

SQL

-- 需要先启用 btree_gist 扩展：CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX idx_customers_phone ON customers USING SP-GiST (phone prefix_range);

TEXT 📖 仅展示

CREATE TABLE

(4) Hash 索引

Hash 索引只支持简单等值查询，不支持范围、排序。PostgreSQL 10 之前 Hash 索引有 WAL 问题，现已修复，但 B-Tree 通常更好。

维度	B-Tree	Hash
等值查询	快	快
范围查询	支持	不支持
排序	支持	不支持
WAL	完整	PostgreSQL 10+ 完整
推荐	默认选择	极少使用

7. Concept：高级索引特性

(1) 部分索引（Partial Index）

部分索引只包含满足 WHERE 条件的行，减少索引体积和维护成本。这是 PostgreSQL 特色功能。

维度	全量索引	部分索引
包含行	全部行	满足条件的行
索引体积	大	小
维护成本	每次写入都更新	仅相关行更新
适用场景	通用查询	查询只关心子集

▶ 示例：只索引活跃用户

SQL

CREATE INDEX idx_users_active_email ON users (email)
WHERE status = 'active';

TEXT 📖 仅展示

CREATE TABLE

SQL

SELECT email FROM users WHERE status = 'active' AND email = 'alice@example.com';

该查询命中部分索引。不加 status = 'active' 条件的查询不会命中。

▶ 示例：只索引未发货订单

SQL

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE shipped = false;

TEXT 📖 仅展示

CREATE TABLE

(2) 表达式索引

当查询条件包含函数或计算时，普通索引无法命中。表达式索引对计算结果建索引。

▶ 示例：不区分大小写查询

SQL

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

TEXT 📖 仅展示

CREATE TABLE

▶ 示例：日期截断查询

SQL

CREATE INDEX idx_orders_date_trunc ON orders (DATE_TRUNC('day', created_at));

SELECT COUNT(*) FROM orders
WHERE DATE_TRUNC('day', created_at) = '2025-06-15'::date;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

(3) 复合索引与最左前缀

复合索引 (a, b, c) 可支持的查询模式：

查询条件	是否命中	原因
`WHERE a = 1`	是	最左前缀
`WHERE a = 1 AND b = 2`	是	最左前缀
`WHERE a = 1 AND b = 2 AND c = 3`	是	完全匹配
`WHERE b = 2`	否	缺少最左列
`WHERE b = 2 AND c = 3`	否	缺少最左列
`WHERE a = 1 AND c = 3`	部分	只用 a 列，c 不连续

▶ 示例：创建复合索引

SQL

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

TEXT 📖 仅展示

CREATE TABLE

(4) CONCURRENTLY 在线建索引

建索引默认加排他锁，阻塞写入。CONCURRENTLY 不阻塞写入，但构建更慢。

方式	锁	阻塞写入	速度	能否在事务内
CREATE INDEX	排他锁	阻塞	快	能
CREATE INDEX CONCURRENTLY	共享锁	不阻塞	慢	不能

▶ 示例：在线建索引

SQL

CREATE INDEX CONCURRENTLY idx_orders_region
ON orders (region);

TEXT 📖 仅展示

CREATE TABLE

注意：CONCURRENTLY 不能在事务块内执行。

8. Concept：EXPLAIN 执行计划

(1) EXPLAIN 基础

命令	说明	执行查询？
EXPLAIN	显示执行计划	否
EXPLAIN ANALYZE	执行并显示实际耗时	是
EXPLAIN BUFFERS	显示缓冲区命中	是
EXPLAIN (FORMAT JSON)	JSON 格式输出	否

▶ 示例：查看执行计划

SQL

EXPLAIN
SELECT * FROM orders WHERE customer_id = 1;

TEXT 📖 仅展示

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=1 width=72)
   Index Cond: (customer_id = 1)

▶ 示例：EXPLAIN ANALYZE

SQL

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1;

TEXT 📖 仅展示

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders
   (cost=0.29..8.31 rows=1 width=72) (actual time=0.015..0.016 rows=2 loops=1)
   Index Cond: (customer_id = 1)
 Planning Time: 0.085 ms
 Execution Time: 0.032 ms

(2) 关键扫描类型

扫描类型	含义	索引命中？
Seq Scan	全表顺序扫描	否
Index Scan	索引扫描（取回表）	是
Index Only Scan	仅索引扫描（不回表）	是（覆盖索引）
Bitmap Scan	位图扫描（大批量）	部分
Parallel Seq Scan	并行全表扫描	否

▶ 示例：Index Only Scan

SQL

EXPLAIN
SELECT customer_id FROM orders WHERE customer_id = 1;

TEXT 📖 仅展示

 Index Only Scan using idx_orders_customer_id on orders
   Index Cond: (customer_id = 1)

只需读索引，不回表查询数据行——性能最优。

9. 索引维护与失效

(1) REINDEX 重建索引

长期增删后索引可能膨胀（bloat），REINDEX 重建可回收空间。

方式	说明	锁
REINDEX INDEX idx	重建单个索引	排他锁
REINDEX TABLE tbl	重建表上所有索引	排他锁
REINDEX INDEX CONCURRENTLY idx	在线重建（PG 12+）	不阻塞

▶ 示例：重建索引

SQL

REINDEX INDEX idx_orders_customer_id;

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

TEXT 📖 仅展示

-- SQL 语句执行成功

(2) 索引失效常见场景

场景	示例	解决
函数包裹列	`WHERE LOWER(col) = 'x'`	表达式索引
隐式类型转换	`WHERE varchar_col = 123`	统一类型
前缀通配符	`WHERE col LIKE '%abc'`	GIN/pg_trgm
OR 条件	`WHERE a=1 OR b=2`	分别建索引或 UNION
统计信息过期	数据大量变化后	ANALYZE
不满足最左前缀	复合索引(a,b)查 b	调整索引或查询
部分索引条件不匹配	`WHERE status='active'` 查全部	去掉 WHERE 或建全量索引

▶ 示例：类型不匹配导致索引失效

SQL

SELECT * FROM users WHERE phone = 13800138000;

SELECT * FROM users WHERE phone = '13800138000';

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

phone 是 VARCHAR，第一行隐式转换导致索引失效，第二行命中索引。

10. Comprehensive Example

Bob 的索引优化方案——JSONB 商品搜索 + 活跃用户部分索引 + 复合索引 + BRIN 时序索引：

SQL

CREATE INDEX CONCURRENTLY idx_products_attrs_gin
  ON products USING GIN (attrs);

CREATE INDEX CONCURRENTLY idx_users_active_email
  ON users (email, last_login_at)
  WHERE status = 'active';

CREATE INDEX CONCURRENTLY idx_orders_customer_date
  ON orders (customer_id, order_date DESC);

CREATE INDEX CONCURRENTLY idx_audit_log_created_brin
  ON audit_log USING BRIN (created_at)
  WITH (pages_per_range = 32);

EXPLAIN ANALYZE
SELECT p.product_id, p.name, p.attrs
FROM products p
WHERE p.attrs @> '{"category": "electronics", "in_stock": true}';

EXPLAIN ANALYZE
SELECT user_id, email
FROM users
WHERE status = 'active'
  AND email LIKE 'alice%'
ORDER BY last_login_at DESC
LIMIT 10;

11. 执行流程

索引类型选择与优化流程：

flowchart TD
    A[发现慢查询] --> B[EXPLAIN ANALYZE]
    B --> C{扫描类型?}
    C -->|Seq Scan| D{是否有合适索引?}
    C -->|Index Scan| E[索引已命中<br/>优化查询/索引]
    D -->|否| F{数据类型?}
    D -->|有但未命中| G[检查索引失效原因]
    F -->|标量/排序| H[创建 B-Tree]
    F -->|JSONB/数组| I[创建 GIN]
    F -->|几何/范围| J[创建 GiST]
    F -->|大表有序列| K[创建 BRIN]
    F -->|只查子集| L[创建 Partial Index]
    F -->|函数/计算列| M[创建 Expression Index]
    H --> N[CONCURRENTLY 上线]
    I --> N
    J --> N
    K --> N
    L --> N
    M --> N

    style B fill:#e1f5fe
    style G fill:#ffccbc
    style N fill:#c8e6c9

❓ 常见问题

Q 索引越多越好吗？

A 不是。每个索引增加写入开销（INSERT/UPDATE/DELETE 需维护索引）和存储空间。只为高频查询建索引，定期用 pg_stat_user_indexes 检查使用率。

Q CONCURRENTLY 建索引失败怎么办？

A CONCURRENTLY 失败会留下 INVALID 索引，用 SELECT indexname FROM pg_indexes WHERE indexdef LIKE '%INVALID%' 或 \d+ tbl 查看，然后 DROP INDEX 删除。

Q 为什么我的 LIKE 查询不用索引？

A LIKE 'abc%' 可以用 B-Tree；LIKE '%abc' 前缀通配不行，需 GIN + pg_trgm 扩展支持。

Q BRIN 索引适合什么场景？

A 物理有序的大表（如按时间追加写入的日志表）。如果数据与物理顺序无关，BRIN 近似过滤效果差，不推荐。

Q 部分索引对写入性能有影响吗？

A 影响比全量索引小。不满足 WHERE 条件的行写入时不需要更新部分索引，节省了 I/O。

Q 复合索引(a, b)能否支持 WHERE a=1 ORDER BY b？

A 可以。复合索引按 a 排序后按 b 排序，能同时满足 WHERE 过滤和 ORDER BY，避免额外排序。

📖 小节

PostgreSQL 支持 6 种索引类型，B-Tree 适用于 90% 场景
GIN 索引适用于 JSONB/数组/全文搜索的包含查询
BRIN 索引体积极小，适合物理有序的大表范围扫描
部分索引只索引满足条件的行，减少体积和维护成本
表达式索引对函数/计算结果建索引，解决函数包裹列导致的索引失效
复合索引遵循最左前缀原则，列顺序影响查询命中
CONCURRENTLY 在线建索引不阻塞写入，但不能再事务内执行
EXPLAIN ANALYZE 是诊断慢查询的核心工具
索引失效常见原因：函数包裹、类型不匹配、统计信息过期

📝 作业

⭐ 为 orders 表的 customer_id 列创建 B-Tree 索引，并用 EXPLAIN 验证查询命中
⭐ 为 products 表的 JSONB 列创建 GIN 索引，测试 @> 查询的执行计划变化
⭐⭐ 创建一个部分索引：只索引 status = 'pending' 的订单的 created_at 列，对比全量索引的体积差异
⭐⭐ 为 LOWER(email) 创建表达式索引，验证不区分大小写查询命中索引；再创建复合索引 (region, created_at DESC) 支持按区域和时间的查询排序
⭐⭐⭐ 对一张千万级行的日志表，设计 BRIN 索引 + B-Tree 复合索引 + 部分索引的组合方案，用 EXPLAIN ANALYZE 对比优化前后的查询耗时

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL索引原理与优化

1. 你将学到

2. 故事

3. Concept：索引类型概览

(1) 六种索引类型

▶ 示例：查看表的已有索引

(2) 索引类型选择决策

4. Concept：B-Tree 索引

(1) B-Tree 特性与适用场景

▶ 示例：创建 B-Tree 索引

▶ 示例：UNIQUE 索引

5. Concept：GIN 索引

(1) GIN 索引原理

▶ 示例：JSONB 字段 GIN 索引

▶ 示例：数组字段 GIN 索引

▶ 示例：全文搜索 GIN 索引

(2) GIN vs B-Tree 对比

6. Concept：GiST / BRIN / SP-GiST / Hash

(1) GiST 索引

▶ 示例：范围类型 GiST 索引

(2) BRIN 索引

▶ 示例：时序表 BRIN 索引

(3) SP-GiST 索引

▶ 示例：电话前缀 SP-GiST 索引

(4) Hash 索引

7. Concept：高级索引特性

(1) 部分索引（Partial Index）

▶ 示例：只索引活跃用户

▶ 示例：只索引未发货订单

(2) 表达式索引

▶ 示例：不区分大小写查询

▶ 示例：日期截断查询

(3) 复合索引与最左前缀

▶ 示例：创建复合索引

(4) CONCURRENTLY 在线建索引

▶ 示例：在线建索引

8. Concept：EXPLAIN 执行计划

(1) EXPLAIN 基础

▶ 示例：查看执行计划

▶ 示例：EXPLAIN ANALYZE

(2) 关键扫描类型

▶ 示例：Index Only Scan

9. 索引维护与失效

(1) REINDEX 重建索引

▶ 示例：重建索引

(2) 索引失效常见场景

▶ 示例：类型不匹配导致索引失效

10. Comprehensive Example

11. 执行流程

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好