站点图标 高效码农

PostgreSQL性能翻盘秘笈：3个颠覆常规的数据库优化技巧，让你的查询快如闪电！

高效码农

解锁PostgreSQL性能：3个非常规优化技巧

在数据库优化领域，大多数开发者的工具箱里永远是那几样“老伙计”——微调查询语句、给字段加索引、做数据反范式化，再配合分析、清理、聚类操作反复尝试。常规优化方法固然有效，但有时候跳出固有思维，用一些创意性的技巧，能让PostgreSQL的性能实现质的飞跃。本文将拆解3个实用又冷门的PostgreSQL优化思路，从消除无效全表扫描，到优化低基数场景索引，再到用哈希索引实现唯一性约束，每一个都能解决实际工作中的性能痛点。

一、利用检查约束消除无意义的全表扫描

日常操作中，我们常会给表字段添加检查约束（CHECK Constraint）来保证数据合法性，但很少有人注意到：PostgreSQL默认不会利用这些约束来优化查询，哪怕查询条件明显违背约束，数据库也会执行全表扫描。通过简单的参数调整，我们就能避免这种无意义的性能消耗。

1.1 场景还原：一个小错误引发的全表扫描

假设我们创建了一张用户表，记录用户ID、用户名和付费套餐类型，且通过检查约束限定套餐只能是free或pro：

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

向表中插入10万条测试数据并执行分析：

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

ANALYZE users;

此时分析师想查询“Pro”套餐（首字母大写）的用户，写出了这样的查询：

SELECT * FROM users WHERE plan = 'Pro';

结果自然是返回0行，但关键问题在于这个查询的执行成本——查看执行计划：

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

输出显示PostgreSQL执行了全表扫描（Seq Scan），遍历了全部10万条数据，耗时约7.4毫秒：

 Seq Scan on users  (cost=0.00..2185.00 rows=1 width=45)
                    (actual time=7.406..7.407 rows=0.00 loops=1)
   Filter: (plan = 'Pro'::text)
   Rows Removed by Filter: 100000
   Buffers: shared hit=935
 Planning:
   Buffers: shared hit=29 read=2
 Planning Time: 4.564 ms
 Execution Time: 7.436 ms

明明检查约束已经限定plan只能是free或pro，不存在Pro这个值，为什么数据库还要扫描全表？核心原因是：PostgreSQL默认不会主动校验查询条件是否违背检查约束。

1.2 开启constraint_exclusion参数解决问题

PostgreSQL提供了constraint_exclusion参数，开启后数据库会在生成执行计划时校验查询条件与约束的匹配性，若条件必然为假，则直接跳过表扫描。

执行以下命令开启该参数：

SET constraint_exclusion to 'on';

再次执行相同的查询并查看执行计划：

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

此时执行计划变为：

 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.001 rows=0.00 loops=1)
   One-Time Filter: false
 Planning:
   Buffers: shared hit=5 read=4
 Planning Time: 5.760 ms
 Execution Time: 0.008 ms

数据库识别出条件必然不成立，直接返回空结果，执行时间从7.4毫秒骤降到0.008毫秒，完全消除了全表扫描。

1.3 什么时候开启constraint_exclusion才合理？

可能有人会问：既然这个参数这么好用，为什么不默认开启？

PostgreSQL官方文档给出了答案：constraint_exclusion默认值为partition（仅用于分区表的分区裁剪），因为对所有表开启该参数会增加查询规划的开销——对于简单查询，规划阶段多花的时间可能超过执行优化带来的收益。

但在以下场景中，开启constraint_exclusion = on性价比极高：

  • BI/报表分析环境：分析师常手写临时查询，容易出现拼写错误（如上文的Pro）或无效条件，此时避免全表扫描能显著节省资源；
  • 数据仓库场景：表数据量极大，哪怕一次无意义的全表扫描也会消耗大量IO和CPU；
  • 分区表场景：除了默认的分区裁剪，还能进一步利用检查约束优化跨分区查询。

二、为低基数场景优化：函数索引+虚拟生成列

在处理时间类字段的索引优化时，开发者常直接给timestamptz类型字段加B-Tree索引，但这种“一刀切”的方式会造成索引体积过大、维护成本高。针对“按日统计”这类低基数需求，我们可以用函数索引+虚拟生成列的组合，在保证性能的同时大幅缩减索引体积。

2.1 场景：按日统计的销售数据查询

创建一张销售表，包含1000万条销售记录，记录销售ID、成交时间和金额：

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

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

ANALYZE sale;

分析师需要查询2025年1月的每日销售额，查询语句如下：

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

2.1.1 常规操作：加B-Tree索引

默认情况下，该查询会执行全表扫描，耗时约627毫秒。为了优化，我们先给sold_at加常规B-Tree索引：

CREATE INDEX sale_sold_at_ix ON sale(sold_at);

优化后查询耗时降到187毫秒，但索引体积高达214MB（接近表体积的一半）：

\di+ sale_sold_at_ix
List of indexes
─[ RECORD 1 ]─┬────────────────
Schema        │ public
Name          │ sale_sold_at_ix
Type          │ index
Owner         │ haki
Table         │ sale
Persistence   │ permanent
Access method │ btree
Size          │ 214 MB

这种方式虽然提升了性能，但索引体积过大，不仅占用存储空间，还会增加数据写入时的索引维护成本。

2.2 优化思路：只索引需要的维度（日期）

分析师的需求是“按日统计”，而我们的B-Tree索引却包含了毫秒级的时间精度——这是典型的“过度索引”。我们可以创建函数索引，只索引成交时间的“日期”部分：

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

这个索引仅针对日期维度，体积骤降到66MB（仅为原B-Tree索引的1/3）：

\di+ sale_sold_at_*
                  List of indexes
         Name         │ Table │ Access method │  Size
──────────────────────┼───────┼───────────────┼────────
 sale_sold_at_date_ix │ sale  │ btree         │ 66 MB
 sale_sold_at_ix      │ sale  │ btree         │ 214 MB

调整查询语句使用该索引（匹配函数索引的表达式）：

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;

执行耗时进一步降到145毫秒，比原B-Tree索引更快，且索引体积大幅缩减。

2.3 解决函数索引的“一致性问题”

函数索引的痛点在于：查询语句必须严格匹配索引的表达式，哪怕微小的差异（比如用::date代替date_trunc），数据库也无法使用索引：

-- 这个查询会走全表扫描，因为表达式和索引不匹配
EXPLAIN (ANALYZE OFF, COSTS OFF)
SELECT (sold_at AT TIME ZONE 'UTC')::date, SUM(charged)
FROM sale
WHERE (sold_at AT TIME ZONE 'UTC')::date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1;

靠“规范查询写法”来避免这个问题不现实，PostgreSQL 18引入的虚拟生成列能完美解决这个痛点。

2.3.1 创建虚拟生成列

虚拟生成列看起来是普通列，但实际存储的是表达式计算结果（每次访问时动态计算，不占用物理存储），我们可以用它固化函数索引的表达式：

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

2.3.2 基于虚拟列查询

现在只需查询虚拟列，就能确保表达式和索引匹配，数据库会自动使用函数索引：

EXPLAIN (ANALYZE ON, COSTS OFF, BUFFERS OFF)
SELECT sold_at_date, SUM(charged)
FROM sale
WHERE sold_at_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1;

执行计划显示索引被正常使用，耗时约163毫秒，且无需强制要求开发者记住复杂的表达式。

2.4 这种优化方式的核心优势

优势 具体说明
更小的索引体积 低基数（日期）的函数索引利用PostgreSQL的去重机制，体积远小于全精度B-Tree索引
更快的查询速度 小索引占用更少内存和IO，查询时的索引扫描效率更高
无需人为规范 虚拟生成列固化表达式，避免因查询写法不一致导致索引失效
无时区歧义 统一的表达式确保所有查询使用相同的时区转换规则，避免统计结果错误

注意：截至PostgreSQL 18，暂不支持直接在虚拟生成列上创建索引，需先创建函数索引，再通过虚拟列间接使用。

三、用哈希索引高效强制URL字段的唯一性

在处理大尺寸字符串（如URL）的唯一性约束时，常规的唯一B-Tree索引会因存储完整字符串导致体积过大。利用PostgreSQL的排除约束+哈希索引，既能强制唯一性，又能大幅缩减索引体积。

3.1 场景：避免重复处理URL

创建一张URL表，存储URL和对应的解析数据，要求URL不能重复（避免重复解析消耗资源）：

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

INSERT INTO urls (id, url)
SELECT n, 'https://' || uuidv4() || '.com/ ' || uuidv4() || '?p=' || uuidv4()
FROM generate_series(1, 1_000_000) AS t(n);

3.2 常规方案：唯一B-Tree索引的问题

给url字段加唯一B-Tree索引是最直接的方式：

CREATE UNIQUE INDEX urls_url_unique_ix ON urls(url);

该索引能有效强制唯一性，但体积问题极为突出：

\dt+ urls
List of tables
─[ RECORD 1 ]─┬──────────
Schema        │ public
Name          │ urls
Type          │ table
Owner         │ haki
Persistence   │ permanent
Access method │ heap
Size          │ 160 MB

\di+ urls_url_unique_ix
List of indexes
─[ RECORD 1 ]─┬───────────────────
Schema        │ public
Name          │ urls_url_unique_ix
Type          │ index
Owner         │ haki
Table         │ urls
Persistence   │ permanent
Access method │ btree
Size          │ 154 MB

160MB的表，对应的唯一B-Tree索引却有154MB——因为B-Tree索引会在叶子节点存储完整的URL字符串，而URL本身长度长、重复少，导致索引体积接近表体积。

3.3 优化方案：排除约束+哈希索引

PostgreSQL不支持直接创建“唯一哈希索引”，但可以通过排除约束（Exclusion Constraint） 间接实现：

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

这个约束的作用是：禁止url字段值相等的两行数据存在，等同于“唯一约束”；且约束由哈希索引支撑，索引中存储的是URL的哈希值（而非完整URL）。

3.3.1 验证唯一性约束

尝试插入重复URL，会触发约束冲突：

INSERT INTO urls (id, url) VALUES (1_000_002, 'https://hakbenita.com/postgresql-hash-index');
-- 插入成功

INSERT INTO urls (id, url) VALUES (1_000_003, 'https://hakbenita.com/postgresql-hash-index');
-- 报错：conflicting key value violates exclusion constraint "urls_url_unique_hash"

3.3.2 验证索引查询性能

哈希索引不仅能强制唯一性，还能用于普通的URL查询，且性能优于B-Tree索引：

EXPLAIN (ANALYZE ON, BUFFERS OFF, COSTS OFF)
SELECT * FROM urls WHERE url = 'https://hakibenita.com';

执行耗时仅0.022毫秒（B-Tree索引需0.046毫秒），执行计划显示使用了哈希索引：

 Index Scan using urls_url_unique_hash on urls (actual time=0.010..0.011 rows=1.00 loops=1)
   Index Cond: (url = 'https://hakibenita.com'::text)
   Index Searches: 1
 Planning Time: 0.178 ms
 Execution Time: 0.022 ms

3.3.3 索引体积对比

哈希索引的体积优势极为明显：

\di+ urls_url_*
                List of indexes
        Name         │ Access method │  Size
─────────────────────┼───────────────┼────────
urls_url_unique_hash │ hash          │ 32 MB
urls_url_unique_ix   │ btree         │ 154 MB

哈希索引仅32MB，是B-Tree索引的1/5，大幅节省存储成本。

3.4 哈希索引实现唯一性的注意事项

虽然哈希索引优势显著，但存在两个关键限制，使用时需注意：

3.4.1 无法作为外键引用

PostgreSQL要求外键必须引用“唯一约束”（Unique Constraint），而排除约束不属于此类，因此无法将其他表的字段外键关联到该url字段：

CREATE TABLE foo (url TEXT REFERENCES urls(url));
-- 报错：there is no unique constraint matching given keys for referenced table "urls"

3.4.2 INSERT … ON CONFLICT的使用限制

  • 不能直接用ON CONFLICT (url)，需指定约束名： 
    -- 错误写法
INSERT INTO urls (id, url) VALUES (1_000_004, 'https://hakibenita.com')
ON CONFLICT (url) DO NOTHING;

-- 正确写法
INSERT INTO urls (id, url) VALUES (1_000_004, '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);

四、常见问题解答（FAQ）

Q1：开启constraint_exclusion会影响普通查询的性能吗？

A：对于简单查询，开启constraint_exclusion = on会增加少量规划阶段的开销（因为需要校验约束），但在报表、数据仓库等场景，这种开销远小于避免全表扫描带来的收益。如果是高并发的OLTP场景，建议保持默认值partition。

Q2：函数索引和虚拟生成列只能用于时间字段吗？

A：当然不是。只要是“查询仅需使用字段的部分特征”的场景都适用，比如：

  • 对手机号字段，创建函数索引只索引后8位（用于模糊查询）；
  • 对JSON字段，创建函数索引索引特定JSON路径的值；
  • 对字符串字段，创建函数索引索引小写形式（用于不区分大小写的查询）。

Q3：哈希索引适合所有唯一性约束场景吗？

A：不适合。哈希索引的优势是存储大尺寸、低重复率的字符串（如URL、长文本），但如果字段是小尺寸、有序的（如数字、短字符串），B-Tree索引更合适——因为B-Tree支持范围查询（如url > 'https://a.com'），而哈希索引仅支持等值查询。

Q4：PostgreSQL 18之前的版本没有虚拟生成列，该怎么解决函数索引的一致性问题？

A：可以用视图来替代：

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

要求开发者查询视图而非原表，就能保证表达式一致。缺点是需要限制原表的访问权限，否则开发者仍可能直接查询表导致索引失效。

五、总结

PostgreSQL的优化不止于“加索引、调查询”，理解数据库的底层机制，结合业务场景做针对性优化，才能实现性能与资源的平衡：

  1. 利用constraint_exclusion参数，让检查约束成为优化查询的“帮手”，避免无意义的全表扫描；
  2. 针对低基数查询需求，用函数索引+虚拟生成列替代全精度B-Tree索引，大幅缩减索引体积；
  3. 对大尺寸字符串的唯一性约束，用排除约束+哈希索引替代唯一B-Tree索引，兼顾性能与存储。

这些非常规技巧的核心逻辑是：不要为不需要的功能买单——不需要毫秒级时间精度，就不索引毫秒；不需要存储完整URL，就只存储哈希值；不需要扫描违背约束的数据，就直接跳过扫描。只有让索引和查询精准匹配业务需求，才能发挥PostgreSQL的最大性能。

退出移动版