PostgreSQL复合唯一约束为何未在单首列查询中生效？

使用PostgreSQL 14.7版本，此前我认为如果表存在复合唯一约束，无需为约束中的首列单独创建索引，依据官方文档：

PostgreSQL在为表定义唯一约束或主键时，会自动创建唯一索引。该索引覆盖构成主键或唯一约束的列（如果是多列约束则为多列索引），这是约束生效的底层机制。

但通过包含外键的复合唯一约束测试发现：仅依据首列查询时，复合唯一约束对应的索引完全未被使用，只有在包含第二列的条件（或排序）时才会使用该唯一索引。即便提前创建复合索引而非事后创建，单独为首列创建索引的成本更低、查询速度更快。为什么这个场景和文档描述不符？

测试步骤 #

1. 创建外键表

CREATE TABLE uniques_test_alt (id SERIAL CONSTRAINT uniques_alt_pk PRIMARY KEY);

2. 填充外键表

INSERT INTO uniques_test_alt (id) VALUES (1);
INSERT INTO uniques_test_alt (id) VALUES (2);

3. 创建主测试表

CREATE TABLE uniques_test (
id SERIAL CONSTRAINT uniques_pk PRIMARY KEY,
alt_id INT NOT NULL CONSTRAINT uniques_alt_id_fk REFERENCES uniques_test_alt ON DELETE CASCADE,
some_txt varchar(255),
created TIMESTAMP NOT NULL,
CONSTRAINT uniques_test_unique UNIQUE (alt_id, some_txt));

4. 向测试表生成200万条关联外键表的记录

INSERT INTO uniques_test(alt_id, some_txt, created) SELECT 1, concat('https://', x.id), NOW() FROM generate_series(1,1000000) AS x(id);
INSERT INTO uniques_test(alt_id, some_txt, created) SELECT 2, concat('https://', x.id), NOW() FROM generate_series(1,1000000) AS x(id);

5. 观察仅用首列查询时未使用复合唯一索引

EXPLAIN ANALYZE SELECT * FROM uniques_test WHERE alt_id = 2;

执行计划：

Seq Scan on uniques_test  (cost=0.00..39706.00 rows=1002200 width=30) (actual time=54.192..104.769 rows=1000000 loops=1)
Filter: (alt_id = 2)
Rows Removed by Filter: 1000000
Planning Time: 0.221 ms
Execution Time: 120.997 ms

6. 为外键列单独创建索引

CREATE INDEX "uniques_test_alt_idx" ON uniques_test (alt_id);

7. 观察查询性能提升

EXPLAIN ANALYZE SELECT * FROM uniques_test WHERE alt_id = 2;

执行计划：

Index Scan using uniques_test_alt_idx on uniques_test  (cost=0.43..28311.93 rows=1002200 width=30) (actual time=0.027..73.361 rows=1000000 loops=1)
Index Cond: (alt_id = 2)
Planning Time: 0.244 ms
Execution Time: 91.634 ms

原因分析 #

1. 复合索引的前缀匹配特性与优化器成本选择 #

复合唯一约束创建的(alt_id, some_txt)多列索引，本身是支持仅用alt_id前缀查询的，但PostgreSQL的查询优化器会根据成本估算选择执行计划。在测试场景中，alt_id只有2个取值，每个取值对应100万行数据（占表总数据的50%），此时优化器认为：顺序扫描直接遍历全表的IO成本，比“扫描复合索引+回表取完整数据”的总成本更低，因此选择了Seq Scan而非索引扫描。

2. 单列索引与复合索引的成本差异 #

单独创建的alt_id单列索引，体积远小于包含some_txt的复合索引——单列索引仅存储alt_id和行指针，而复合索引需要存储alt_id、some_txt和行指针。更小的索引意味着更少的磁盘IO和内存占用，扫描速度更快，优化器会优先选择成本更低的单列索引。

3. 文档描述的侧重点 #

官方文档的表述是准确的：复合唯一约束会自动生成对应的多列索引，且该索引可用于覆盖约束列的查询。但文档并未承诺优化器一定会选择该索引用于前缀列的查询——优化器的选择始终基于实际数据分布、返回行数等因素的成本估算。

内容的提问来源于stack exchange，提问作者Alkanshel