唯一索引是PostgreSQL物化视图并发刷新和查询重写的前提,需NOT NULL、顺序匹配GROUP BY、类型对齐,并配合ANALYZE更新统计信息。

唯一索引让 PostgreSQL 优化器“敢用”物化视图
没建唯一索引的物化视图,enable_material = on 也大概率被优化器忽略——它无法确认物化视图行与源数据的一一对应关系,怕重写后结果不一致,干脆绕开不用。建了唯一索引(如 CREATE UNIQUE INDEX ON mv_sales (department, year)),优化器才敢把原查询自动重写成查物化视图,跳过原始大表扫描。
唯一索引是并发刷新(CONCURRENTLY)的硬性前提
REFRESH MATERIALIZED VIEW CONCURRENTLY 不锁表,但底层靠逐行比对新旧数据实现,必须依赖唯一键定位变更行。没有唯一索引会直接报错:ERROR: cannot refresh materialized view "mv_sales" concurrently because it has no unique index。而全量刷新(不带 CONCURRENTLY)会锁死整个物化视图,所有 SELECT 都阻塞,线上根本不能用。
- 唯一索引列必须
NOT NULL,且顺序需与GROUP BY字段完全一致(如GROUP BY department, year,索引就得是(department, year),反过来就失效) - 业务上要确保该组合真唯一:比如
(department, EXTRACT(YEAR FROM order_date))在订单系统里不能出现同部门同一年多条聚合记录
唯一索引本身加速 WHERE 和 JOIN
物化视图本质是只读物理表,不继承基表索引,也不自动生成索引。即使你只为并发刷新建了唯一索引,它同时也会成为高效过滤的 B-tree 索引:
-
WHERE department = '研发' AND year = 2023可走索引查找,避免Seq Scan - 若物化视图常被
JOIN到其他表,该唯一索引还能作为连接键提升哈希/嵌套循环效率 - 注意类型对齐:如果
year是double precision(如EXTRACT(YEAR FROM ...)返回值),查询时写year = 2023(整型)会触发隐式转换,索引失效
别把唯一索引和普通索引混为一谈
唯一索引解决的是“能否并发刷新”和“优化器是否信任该物化视图”的问题;普通非唯一索引(如 CREATE INDEX ON mv_sales (region))只解决“查得快不快”。两者目的不同,缺一不可:
- 没唯一索引 →
CONCURRENTLY刷新失败,且优化器大概率无视该物化视图 - 有唯一索引但没普通索引 → 并发刷新可用,但
WHERE region = '华东'还是全表扫 - 唯一索引字段选错(如含可空列、顺序颠倒、类型不匹配)→ 表面建成功,实际刷新或查询时行为异常
真正容易被忽略的点是:唯一索引不是建完就一劳永逸——每次 REFRESH 后,记得 ANALYZE mv_sales 更新统计信息,否则优化器可能因误判数据分布而放弃使用索引。










