PostgreSQL / 系统课时
选择 B-tree 索引
选择 B-tree 索引
本课解决的核心问题是:面对等值、范围、排序以及多列过滤时,什么时候应该优先使用 PostgreSQL 的 B-tree。学完后,你可以从查询条件与排序方式判断默认索引是否真的适用。
学习目标
- 判断 B-tree 对等值、范围和排序操作的适用边界
- 理解多列 B-tree 的列顺序与查询条件之间的关系
- 能用
EXPLAIN验证索引是否被优化器采用
B-tree 为什么适合作为默认索引
B-tree 将索引键按有序结构组织,因此既能定位某个具体值,也能快速找到一个连续范围。常见的 =, <, <=, >, >= 以及 BETWEEN 查询,都属于它擅长的访问方式。
排序也是 B-tree 的重要能力。由于索引本身有序,查询在满足条件时可以直接按索引顺序输出,减少额外的排序工作。对于经常同时过滤和排序的查询,这一点尤其重要。
需要注意的是,B-tree 并不意味着“只要建了索引,所有查询都会变快”。优化器会比较索引扫描、顺序扫描等成本;当返回行数很多,顺序扫描反而可能更便宜。
从单列查询判断是否适用
先用一个最小实验验证等值和范围条件。下面的表同时准备离散值和时间范围,索引只建立在查询真正使用的列上。
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
created_at timestamptz NOT NULL,
amount numeric(12,2) NOT NULL
);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);
EXPLAIN (COSTS OFF)
SELECT id, amount
FROM orders
WHERE customer_id = 10001;
EXPLAIN (COSTS OFF)
SELECT id, amount
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND created_at < TIMESTAMPTZ '2026-02-01 00:00:00+00';预期结果
QUERY PLAN
Index Scan using idx_orders_customer_id on orders
Index Cond: (customer_id = 10001)
QUERY PLAN
Index Scan using idx_orders_created_at on orders
Index Cond: ((created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-02-01 00:00:00+00'::timestamp with time zone))实际计划会受数据量、统计信息和成本参数影响,因此未必总是显示 Index Scan。这里真正需要验证的是:查询条件是否能形成有效的索引条件,以及索引扫描的成本是否低于顺序扫描。
多列 B-tree 先看列顺序
多列索引的关键不是“列越多越好”,而是列顺序是否符合主要查询模式。例如:
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);
EXPLAIN (COSTS OFF)
SELECT id, amount
FROM orders
WHERE customer_id = 10001
AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
AND created_at < TIMESTAMPTZ '2026-02-01 00:00:00+00'
ORDER BY created_at;预期结果
QUERY PLAN
Index Scan using idx_orders_customer_created on orders
Index Cond: ((customer_id = 10001) AND (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-02-01 00:00:00+00'::timestamp with time zone))对于 (customer_id, created_at),把 customer_id 放在前面意味着先缩小到某个客户,再在该范围内按 created_at 定位和读取。这样的组合尤其适合“等值过滤 + 范围过滤/排序”的查询。
如果查询只过滤第二列,例如只按 created_at 查询,不能简单假定这个复合索引等价于单独的 created_at 索引。现代 PostgreSQL 在部分场景下可能采用跳跃扫描等策略,但是否发生取决于版本、数据分布和优化器估算,因此设计索引时仍应以实际查询模式和 EXPLAIN 为准。
什么时候不该优先选择 B-tree
B-tree 的默认地位来自适用范围广,而不是任何场景都最优。遇到只需要哈希等值匹配的场景,可以比较 Hash;需要全文搜索、几何对象或其他特殊操作符时,则应根据操作符类和访问方式选择其他索引类型。
还要警惕低选择性条件。例如状态列只有两三个值时,即使存在 B-tree,也可能因为单次查询返回大量行而不值得走索引。索引设计最终应回到三个问题:查询条件是什么、希望利用什么顺序、优化器实际选择了什么计划。
使用 B-tree 时,最稳妥的判断方法不是背“默认索引万能”,而是先根据操作符与列顺序设计候选索引,再用 EXPLAIN 验证它是否真正改善了目标查询。