PostgreSQL 百万级数据实战：查询计划与索引策略全解析

上一篇聊了家用 PostgreSQL 的配置调优，这篇来点硬的——100 万条数据，PostgreSQL 的查询计划选择会发生什么变化？索引策略又该怎么调？

开场：当 30 条变成 100 万条

上篇博客里，我们的 projects 表只有 30 条数据。COUNT 一下 0.97ms，排序一下 0.54ms，快得飞起。但你心里清楚——30 条数据，任何数据库都不会慢。

真正的考验是：数据量上来之后，你的索引还管用吗？优化器还会乖乖走 Index Scan 吗？

今天我们用 100 万条真实数据，把 PostgreSQL 的查询计划拆个底朝天。

实验环境

100 万行，248MB——不大不小，刚好是很多真实项目的规模。

插入性能：100 万条要多久

1,000,000 条批量插入: 12.7 秒

平均每秒约 7.8 万条。用的是逐批 10000 条的 INSERT ... VALUES 拼接方式，没有用 COPY。如果用 COPY FROM STDIN，速度还能再快 2-3 倍。

四个索引的创建时间：

索引创建几乎无感。这在生产环境中很重要——你可以放心在已有大表上建索引，不用担心长时间锁表。

查询性能：10 个真实场景

这是重头戏。我们测了 10 种典型查询，覆盖了从简单到复杂的各种场景：

1. COUNT 全表 — 20.9ms

SELECT COUNT(*) FROM bench_projects;

扫描方式：Seq Scan

100 万行全表扫描，20.9ms。PostgreSQL 的 Seq Scan 是高度优化的——它直接读取数据页，不做随机 I/O，SSD 上跑得飞快。

启示： 如果你需要频繁 COUNT，考虑用缓存或者近似值（pg_class.reltuples）。精确 COUNT 在大表上永远是 O(n)。

2. 模糊搜索 — 8.3ms

SELECT * FROM bench_projects 
WHERE description LIKE '%Python%' 
ORDER BY stars DESC LIMIT 100;

扫描方式：Index Scan

这个结果有点意外——PostgreSQL 选择了 Index Scan 而非 Seq Scan。因为 LIMIT 100 让优化器觉得"走索引扫描 100 条就够了"，比全表扫描更划算。

但注意： %Python% 这种前后通配符是无法用普通 B-tree 索引的。PostgreSQL 能走索引，靠的是它扫描了 stars 索引然后回表过滤。如果 LIKE 的匹配率很高（比如 50% 的行都匹配），优化器会切换回 Seq Scan。

3. 范围查询 — 123.3ms

SELECT name, stars FROM bench_projects 
WHERE stars > 400000 ORDER BY stars DESC;

扫描方式：Index Scan

20 万行结果集，123.3ms。这是 Index Scan 的典型场景——索引已经排好序，按范围扫描非常高效。

但 123ms 不算快。 问题出在返回了 20 万行——大量数据从索引跳到数据页（回表），随机 I/O 是瓶颈。

优化方案： 如果只需要部分列，考虑 覆盖索引（Covering Index）：

CREATE INDEX idx_stars_covering ON bench_projects(stars) INCLUDE (name, language);

这样查询直接从索引取数据，不需要回表。

4. 等值查询 — 6.0ms

SELECT name, stars FROM bench_projects 
WHERE language = 'Python' ORDER BY stars DESC LIMIT 100;

扫描方式：Index Scan

6ms 拿到 100 行。这是最理想的 Index Scan 场景——等值过滤 + 排序 + LIMIT，索引全搞定。

关键点： 优化器同时利用了 language 索引的过滤和 stars 的排序。如果你建的是 (language, stars) 复合索引，这个查询会更快——直接顺序扫描，不需要排序。

5. Bitmap 混合查询 — 59.4ms

SELECT name, stars, language FROM bench_projects 
WHERE stars > 300000 AND language IN ('Python', 'Rust');

扫描方式：Bitmap Index Scan

8 万行结果集，59.4ms。这里 PostgreSQL 用了 Bitmap Index Scan——先从两个索引各取一组行号，取交集（BitmapAnd），再批量回表。

为什么不用普通 Index Scan？ 因为条件命中了太多行（8 万），逐个回表的随机 I/O 太贵。Bitmap 的优势是把行号排序后批量读取数据页，减少随机 I/O。

类比： Index Scan 像是你在图书馆一本一本找书；Bitmap Scan 像是你先列个清单，然后按清单上的位置一次性抱一摞书回来。

6. 复合索引 — 2.4ms ⚡

SELECT name, stars FROM bench_projects 
WHERE stars > 200000 AND language = 'Go' ORDER BY stars DESC LIMIT 50;

扫描方式：Index Scan

全场最快！ 2.4ms 拿到 50 行。这就是复合索引的威力——(stars, language) 索引让 PostgreSQL 可以同时完成过滤、排序、取数据，一气呵成。

建索引的学问： 复合索引的列顺序很重要。把等值条件的列（language）放前面，范围条件的列（stars）放后面，效果会更好：

-- 更优的复合索引
CREATE INDEX idx_lang_stars ON bench_projects(language, stars);

7. GIN JSONB 查询 — 12.0ms

SELECT name, topics FROM bench_projects 
WHERE topics @> '["ai"]'::jsonb LIMIT 100;

扫描方式：Bitmap Index Scan

12ms。JSONB 的 @> 包含查询用的是 GIN 索引，走 Bitmap Scan。

GIN 索引的代价： 创建时比 B-tree 慢，占用空间更大（JSONB 内容越多越大），但查询时非常高效。如果你的 JSONB 字段经常被查询，GIN 索引是必须的。

8. GROUP BY 聚合 — 54.1ms

SELECT language, COUNT(*), AVG(stars) 
FROM bench_projects 
GROUP BY language ORDER BY COUNT(*) DESC;

扫描方式：Seq Scan

54.1ms，只有 10 行结果（10 种语言）。为什么是 Seq Scan？因为聚合需要扫描所有行，没有 WHERE 条件可以利用索引。

优化思路： 如果你经常按 language 做聚合，可以创建一个 物化视图（Materialized View）：

CREATE MATERIALIZED VIEW lang_stats AS
SELECT language, COUNT(*) as cnt, AVG(stars) as avg_stars
FROM bench_projects GROUP BY language;

-- 定期刷新
REFRESH MATERIALIZED VIEW lang_stats;

9. 窗口函数 — 34.1ms

SELECT name, stars, language, 
       RANK() OVER (PARTITION BY language ORDER BY stars DESC) 
FROM bench_projects 
WHERE stars > 100000 LIMIT 200;

扫描方式：Index Scan

34.1ms。窗口函数需要对每个分区（每种语言）分别排序，计算量不小。但因为有 stars > 100000 的过滤条件走索引，实际处理的行数被大幅缩减。

10. 子查询 — 29.1ms

SELECT * FROM bench_projects 
WHERE stars > (SELECT AVG(stars) FROM bench_projects) 
ORDER BY stars DESC LIMIT 100;

扫描方式：Index Scan

29.1ms。先算 AVG（全表扫描），再用结果做范围查询（Index Scan）。两个阶段的代价叠加。

优化方案： 如果你需要频繁做这种"高于平均值"的查询，可以定期把 AVG 缓存起来，避免每次都全表扫描。

总览表

索引策略总结

什么时候该建索引

1. WHERE 条件的列 — 最基本的
2. ORDER BY 的列 — 避免排序
3. JOIN 的关联列 — 加速连接
4. 高选择性列 — 比如 language（10 种值）比 stars（50 万种值）选择性低，但配合其他条件仍有价值

什么时候不该建索引

1. 小表（<10000 行） — Seq Scan 比 Index Scan 快
2. 频繁写入的列 — 索引会拖慢 INSERT/UPDATE
3. 低选择性列单独用 — 比如只有 true/false 的布尔列

复合索引的黄金法则

-- 等值条件在前，范围条件在后
CREATE INDEX idx_good ON table(language, stars);

-- ❌ 反过来效果差
CREATE INDEX idx_bad ON table(stars, language);

原因： B-tree 索引是按列顺序排序的。(language, stars) 先按语言分组，每组内再按 stars 排序。当 WHERE language = 'Python' AND stars > 100000 时，索引可以直接定位到 Python 组，然后顺序扫描 stars。

反过来 (stars, language) 是先按 stars 排序，每组内 language 是乱序的，language = 'Python' 的条件只能过滤不能利用排序。

JSONB 字段必建 GIN 索引

CREATE INDEX idx_topics ON bench_projects USING GIN(topics);

如果你的 JSONB 字段经常被 @>、?、?| 查询，GIN 索引是必须的。没有 GIN 索引，这些查询会退化为全表扫描。

EXPLAIN ANALYZE：你的终极武器

每个查询的执行计划都可以用 EXPLAIN ANALYZE 查看：

EXPLAIN ANALYZE 
SELECT name, stars FROM bench_projects 
WHERE stars > 200000 AND language = 'Go' 
ORDER BY stars DESC LIMIT 50;

输出会告诉你：

• 扫描方式 — Seq Scan / Index Scan / Bitmap Scan
• 实际行数 — 估算值 vs 实际值的偏差
• 耗时 — 每个节点的执行时间
• 缓冲区 — 读了多少数据页

如果估算值和实际值差 10 倍以上，说明统计信息过时了，跑一下：

ANALYZE bench_projects;

踩坑记录

坑 1：索引不是越多越好

每个索引都会：

• 拖慢 INSERT/UPDATE（每次写入都要更新索引）
• 占用磁盘空间
• 增加 VACUUM 负担

经验值： 一个表的索引不要超过 5-6 个。

坑 2：EXPLAIN 和 EXPLAIN ANALYZE 不一样

• EXPLAIN — 只显示预估计划，不实际执行
• EXPLAIN ANALYZE — 实际执行并显示真实耗时

调试用 EXPLAIN ANALYZE，但要注意它真的会执行查询。对 DELETE/UPDATE 用 EXPLAIN 就够了。

坑 3：pg_stat_statements 是你的好朋友

-- 启用（需要在 postgresql.conf 中配置 shared_preload_libraries）
CREATE EXTENSION pg_stat_statements;

-- 查看最慢的 10 条查询
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 10;

这是生产环境调优的第一步——先找到慢查询，再针对性优化。

配置模板：百万级数据优化

在上篇家用配置的基础上，针对百万级数据补充：

# === 统计信息 ===
# 让优化器更准确地估算行数
default_statistics_target = 200  # 默认 100，大表可以调高

# === 并行查询 ===
max_parallel_workers_per_gather = 4  # 并行查询 worker 数
max_parallel_workers = 8             # 总并行 worker 数
parallel_tuple_cost = 0.01           # 降低以鼓励并行

# === VACUUM ===
autovacuum_vacuum_scale_factor = 0.05  # 5% 变动就触发 vacuum（默认 20%）
autovacuum_analyze_scale_factor = 0.02  # 2% 变动就触发 analyze

总结

100 万行数据，PostgreSQL 的表现：

1. 简单查询依然快 — 等值 + LIMIT 控制在个位数毫秒
2. 复合索引是王牌 — 2.4ms vs 59.4ms，差距 25 倍
3. Bitmap Scan 是大结果集的救星 — 比逐个回表快得多
4. Seq Scan 不是坏东西 — COUNT 和 GROUP BY 走 Seq Scan 是正常的
5. JSONB + GIN 索引 — 12ms 查 100 万行的 JSON 内容，够用

最重要的一句话：先用 EXPLAIN ANALYZE 看执行计划，再决定建什么索引。 不要凭感觉建索引，也不要盲目加索引。

系列完结。从配置调优到索引策略，家用 PostgreSQL 的核心知识都在这两篇了。有疑问欢迎留言。