一、索引合并背后的故事
大家可能都经历过这样的情况：明明给表加了多个索引，但查询性能总是达不到预期。这时候我们需要了解PostgreSQL的索引合并策略——当查询优化器发现使用多个索引比使用单个索引更高效时，就会启动这个神奇的功能。
想象你有一个包含千万用户地址的表，查询条件是"省=江苏"且"城市=苏州"，如果这两个字段都有独立索引，优化器可能会选择同时使用两个索引再进行合并，这种策略能够减少需要扫描的数据块数量。
二、索引合并的三种典型模式
2.1 BitmapAnd的奇妙组合
当查询条件包含AND逻辑时，优化器可能会选择BitmapAnd操作。这个过程就像用两张地图叠加，只保留两者共同的区域。
-- 创建示例表
CREATE TABLE user_address (
id SERIAL PRIMARY KEY,
province VARCHAR(20),
city VARCHAR(20),
district VARCHAR(20)
);
-- 建立两个B树索引
CREATE INDEX idx_province ON user_address(province);
CREATE INDEX idx_city ON user_address(city);
-- 插入测试数据（示例数据量应≥10万条）
INSERT INTO user_address (province, city)
SELECT
CASE WHEN random() < 0.1 THEN '江苏' ELSE '浙江' END,
CASE WHEN random() < 0.2 THEN '苏州' ELSE '杭州' END
FROM generate_series(1,1000000);
-- 典型AND查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM user_address
WHERE province = '江苏'
AND city = '苏州';
执行计划中可能会看到这样的片段：
Bitmap Heap Scan on user_address
Recheck Cond: ((province = '江苏'::text) AND (city = '苏州'::text))
-> BitmapAnd
-> Bitmap Index Scan on idx_province
Index Cond: (province = '江苏'::text)
-> Bitmap Index Scan on idx_city
Index Cond: (city = '苏州'::text)
2.2 BitmapOr的合纵连横
当遇到OR条件时，优化器可能会使用BitmapOr策略。这就像把两个搜索结果合并起来，形成更大的搜索范围。
-- 调整查询条件
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM user_address
WHERE province = '江苏'
OR city = '杭州';
-- 优化器可能选择如下路径：
Bitmap Heap Scan on user_address
Recheck Cond: ((province = '江苏'::text) OR (city = '杭州'::text))
-> BitmapOr
-> Bitmap Index Scan on idx_province
Index Cond: (province = '江苏'::text)
-> Bitmap Index Scan on idx_city
Index Cond: (city = '杭州'::text)
2.3 复合条件的量子纠缠
更复杂的条件组合需要优化器做出更聪明的选择，比如混合使用BitmapAnd和BitmapOr。
-- 复杂条件查询
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM user_address
WHERE (province = '江苏' AND city = '苏州')
OR (province = '浙江' AND city = '杭州');
这时优化器可能会生成多层级的位图操作树，先做两个BitmapAnd，再将结果通过BitmapOr合并。
三、优化器的决策密码
3.1 选择性估算的魔法
优化器通过统计信息计算每个条件的基数（Cardinality）。当单个索引的选择性不足以覆盖大部分数据时，更可能触发索引合并。这可以通过调整default_statistics_target参数来影响。
3.2 成本计算的复杂博弈
位图堆扫描的成本计算公式： 总成本 = 索引扫描成本 + 位图创建成本 + 堆访问成本
可以通过SET enable_bitmapscan = off来临时禁用该策略，对比不同执行计划。
3.3 参数设置的蝴蝶效应
-- 重要参数调优示例
ALTER SYSTEM SET random_page_cost = 1.5; -- SSD环境调低该值
ALTER SYSTEM SET effective_cache_size = '8GB';
SELECT pg_reload_conf();
四、实战中的优化艺术
4.1 强制走索引合并
当优化器判断失误时，可以使用CTE强制拆分查询：
WITH province_filter AS (
SELECT ctid FROM user_address
WHERE province = '江苏'
), city_filter AS (
SELECT ctid FROM user_address
WHERE city = '苏州'
)
SELECT a.* FROM user_address a
JOIN (
SELECT ctid FROM province_filter
INTERSECT
SELECT ctid FROM city_filter
) merged USING (ctid);
4.2 组合索引的取舍策略
复合索引的顺序影响重大：
-- 创建不同顺序的组合索引
CREATE INDEX idx_prov_city ON user_address(province, city);
CREATE INDEX idx_city_prov ON user_address(city, province);
当查询条件中的过滤字段出现频率差异较大时，应该把高选择性的字段放在前面。
五、性能红黑榜
5.1 何时应该鼓掌
- 当组合条件的选择性远高于单个条件时
- 表非常大但内存充足的情况
- 需要同时使用不同字段的索引时
5.2 何时应该警惕
- 组合查询字段的值分布不均匀
- 数据更新频繁导致统计信息滞后
- 内存不足时可能引发大量随机IO
六、最佳实践指南
- 定期使用
ANALYZE更新统计信息 - 监控
pg_stat_user_indexes查看索引使用情况 - 使用扩展pg_qualstats分析查询条件分布
- 对于OLAP场景适当增大
work_mem
七、写给调优者的建议
索引合并就像是数据库优化师的瑞士军刀，关键在于理解数据特征和业务场景。实际调优时应：
- 优先考虑高选择性的单列索引
- 通过EXPLAIN验证优化器的选择
- 注意组合索引的维护成本
- 在不同负载下进行压力测试
通过持续观察pg_stat_statements中的查询模式，我们能够逐渐建立对优化器行为的准确预期。记住，最昂贵的优化往往来自于对业务逻辑的深入理解。
评论