非常规 PostgreSQL 优化技巧在 PostgreSQL 中加速查询的创造性思路

在进行数据库优化时，开发者往往会拿出那套老工具箱：改写查询、在列上加索引、做反规范化、执行 analyze、vacuum、cluster，然后重复这一过程。这些传统手段确实有效，但有时候，如果能跳出常规思路，创造性地思考，往往能获得意想不到的优化效果。

本文将介绍 PostgreSQL 中一些非常规的优化技巧。

图像来自 abstrakt design

基于 Check 约束消除全表扫描无心之失使用 constraint_exclusion何时使用 constraint_exclusion用函数索引优化低基数场景“直接上 B-Tree 索引”的问题重新思考问题纪律问题使用虚拟生成列（Virtual Generated Columns）为虚拟生成列建立索引用 Hash 索引强制唯一性“直接上 Unique B-Tree 索引”的问题唯一 Hash 索引使用 Hash 索引实现唯一性约束“唯一”排除约束（Exclusion Constraints）的局限基于 Check 约束消除全表扫描

假设我们有一个用户表：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username TEXT NOT NULL,
    plan TEXT NOT NULL,
    CONSTRAINT plan_check CHECK (plan IN ('free', 'pro'))
);

这个表保存用户的姓名以及他们使用的套餐类型。由于套餐只有 “free” 和 “pro” 两种，我们添加了一个检查约束。

生成一些数据并分析表：

INSERT INTO users
SELECT n, uuidv4(), (ARRAY['free', 'pro'])[ceil(random()*2)]
FROM generate_series(1, 100000) AS t(n);
ANALYZE users;

现在系统中有 10 万个用户。

无心之失

现在你要让分析师通过报表工具访问这张表。你为某位分析师开通了访问权限，他写了第一条查询：

SELECT * FROM users WHERE plan = 'Pro';

查询结果为空，这让分析师感到困惑：怎么会没有 “Pro” 套餐的用户呢？

原来套餐名是 “pro”，而不是 “Pro”。这是个很常见的误会。但这个小错误代价不小。

执行计划如下：

EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';

PostgreSQL 扫描了整个表！但是我们有个检查约束规定 plan 只能是 ‘free’ 或 ‘pro’，数据库理应知道这个条件永远为假，为什么还要扫描呢？

使用 constraint_exclusion

PostgreSQL 其实能做到跳过永远为假的查询，但默认没有开启。要让 PostgreSQL 在生成执行计划时考虑约束，需要打开参数 constraint_exclusion：

SET constraint_exclusion TO 'on';

再次执行同样的查询：

EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';

这次 PostgreSQL 直接跳过了表扫描，因为它知道该条件永远为假。

何时使用 constraint_exclusion

默认情况下，constraint_exclusion 仅对基于继承的分区表启用，以支持“分区修剪”。如果全局开启，会带来明显的规划开销。

文档解释说，对于简单查询，评估所有约束条件的代价可能高于它带来的性能收益。但在 BI 报表场景中，用户往往会手写复杂 SQL 并可能犯类似错误。在这种环境下，将 constraint_exclusion 设置为 “on” 能避免很多无意义的全表扫描，节约时间与资源。

用函数索引优化低基数场景

假设我们有一个销售表：

CREATE TABLE sale (
    id INT PRIMARY KEY,
    sold_at TIMESTAMPTZ NOT NULL,
    charged INT NOT NULL
);

我们记录每笔销售的时间和金额。创建 1,000 万条记录并分析：

INSERT INTO sale (id, sold_at, charged)
SELECT n,
       '2025-01-01 UTC'::timestamptz + (interval '5 seconds') * n,
       ceil(random() * 100)
FROM generate_series(1, 10000000) AS t(n);
ANALYZE sale;

“直接上 B-Tree 索引”的问题

分析师经常做日销售报告，他们的查询如下：

SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged)
FROM sale
WHERE sold_at BETWEEN '2025-01-01 UTC' AND '2025-02-01 UTC'
GROUP BY 1;

PostgreSQL 扫描了整个表，用时约 627ms。你加了一个 B-Tree 索引：

CREATE INDEX sale_sold_at_ix ON sale(sold_at);

重新执行查询后，耗时降至 187ms，速度提升 3 倍，但索引大小达 214MB——几乎是表的一半！

重新思考问题

分析师只需要按“天”聚合结果，但索引却细化到了毫秒级，明显过度。那我们为何不只索引“日期”部分呢？

CREATE INDEX sale_sold_at_date_ix ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date);

新的函数索引仅 66MB，比原索引小三倍。删除原索引后调整查询：

SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC'), SUM(charged)
FROM sale
WHERE date_trunc('day', sold_at AT TIME ZONE 'UTC')::date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1;

执行时间仅 145ms，比使用完整索引还快约 20ms。

纪律问题

函数索引依赖表达式的完全一致性。如果稍微改写，例如改用 (sold_at AT TIME ZONE 'UTC')::date，索引就无法使用。

过去的解决方案是通过视图固定表达式：

CREATE VIEW v_sale AS
SELECT *, date_trunc('day', sold_at AT TIME ZONE 'UTC')::date AS sold_at_date
FROM sale;

在视图中使用该列能让索引生效，但分析师可能仍直接访问原表。

使用虚拟生成列（Virtual Generated Columns）

PostgreSQL 14 引入了生成列，但早期版本中结果会物化，仍占用空间。PostgreSQL 18 支持“虚拟生成列”，它不存储结果，而是在访问时动态计算。

添加虚拟生成列：

ALTER TABLE sale ADD sold_at_date DATE
GENERATED ALWAYS AS (date_trunc('day', sold_at AT TIME ZONE 'UTC'));

现在查询时数据库会使用相同表达式，索引得以利用。这样我们能保证：

遗憾的是，截至 PostgreSQL 18，还不支持直接在虚拟生成列上建索引，预计会在 19 版本中加入。

用 Hash 索引强制唯一性

假设我们有一个存储 URL 的表：

CREATE TABLE urls (
    id INT PRIMARY KEY,
    url TEXT NOT NULL,
    data JSON
);

插入 100 万条数据后，为了防止重复处理相同 URL，我们通常会创建唯一约束：

CREATE UNIQUE INDEX urls_url_unique_ix ON urls(url);

这确实能防止重复，但索引大小达 154MB，几乎与表本身（160MB）一样大。

唯一 Hash 索引

B-Tree 索引会在叶节点存储完整键值，因此对于大文本（如 URL）来说非常庞大。Hash 索引仅存储哈希值，因此体积更小。

遗憾的是，PostgreSQL 不支持唯一 Hash 索引：

CREATE UNIQUE INDEX urls_url_unique_hash ON urls USING HASH(url);
-- ERROR: hash 索引不支持唯一约束

不过，我们可以使用排除约束（Exclusion Constraint）来实现等价效果：

ALTER TABLE urls ADD CONSTRAINT urls_url_unique_hash EXCLUDE USING HASH (url WITH =);

这相当于使用 Hash 索引实现唯一性。测试插入重复 URL 会报错，说明约束生效。

查询也能使用 Hash 索引，甚至比 B-Tree 更快（0.022ms vs 0.046ms）。

更重要的是，Hash 索引仅 32MB，比 B-Tree 小五倍！

“唯一”排除约束的局限

使用 Hash 排除约束确实节省空间、提高性能，但也有几个限制：

️ 无法被外键引用

外键只能引用唯一约束，而不是排除约束。

️ 对 INSERT … ON CONFLICT 的支持有限

普通的 ON CONFLICT (url) 不适用于排除约束，必须写成：

INSERT INTO urls (id, url)
VALUES (1000004, 'https://hakibenita.com')
ON CONFLICT ON CONSTRAINT urls_url_unique_hash DO NOTHING;

而 ON CONFLICT DO UPDATE 则完全不支持，只能使用 MERGE：

MERGE INTO urls t
USING (VALUES (1000004, 'https://hakibenita.com')) AS s(id, url)
ON t.url = s.url
WHEN MATCHED THEN UPDATE SET id = s.id
WHEN NOT MATCHED THEN INSERT (id, url) VALUES (s.id, s.url);

MERGE 语句依然能利用 Hash 索引。

尽管存在这些小限制，Hash 索引仍是存储大型唯一文本值（如 URL）时的理想选择，既节省空间，又能保持高性能。

免责声明：由于无法甄别是否为投稿用户创作以及文章的准确性,本站尊重并保护知识产权，根据《信息网络传播权保护条例》，如我们转载的作品侵犯了您的权利,请您通知我们，请将本侵权页面网址发送邮件到qingge@88.com，深感抱歉，我们会做删除处理。

非常规 PostgreSQL 优化技巧在 PostgreSQL 中加速查询的创造性思路

相关阅读