用途
本技能面向 PostgreSQL 性能优化，聚焦于常规 DBA 手段（查询重写、加索引、VACUUM、ANALYZE）无法奏效时的非常规技巧。适用于临时查询环境（用户容易写错条件）、索引体积接近表大小、大文本值（URL、文档）的唯一性约束、以及按较粗粒度查询时间戳列等场景。
主要能力
技巧一：约束排除（Constraint Exclusion）
CHECK 约束能阻止非法数据，但 PostgreSQL 默认不会用它来优化查询。例如表 users 上有 CONSTRAINT plan_check CHECK (plan IN ('free', 'pro'))，当分析人员写出 SELECT * FROM users WHERE plan = 'Pro';（大写 P）时，尽管该条件与约束矛盾、不可能命中任何行，PostgreSQL 仍会全表扫描。
开启方式：
SET constraint_exclusion TO 'on';
开启后执行 EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';，结果为 One-Time Filter: false，执行时间 0.008 ms，PostgreSQL 识别出条件与约束矛盾并完全跳过扫描。
启用建议：OLTP 生产环境保持默认 'partition'；BI/数据仓库、临时查询工具、报表数据库设为 'on'。权衡：收益是消除不可能命中的查询扫描，代价是评估约束与条件匹配带来的额外规划开销。默认值 'partition' 仅用于分区裁剪。
技巧二：函数索引降低基数
当 sale 表按天查询（date_trunc('day', sold_at AT TIME ZONE 'UTC')）时，在 sold_at 上建 B-Tree 索引，160 MB 的表会产生 214 MB 索引，几乎占表大小的一半。改为只索引查询所需内容：
CREATE INDEX sale_sold_at_date_ix
ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date);
索引从 214 MB 降至 66 MB，缩小 3 倍，原因是日期为 4 字节而 timestamptz 为 8 字节，且不同值更少、便于去重。
注意“纪律问题”：函数索引要求表达式完全匹配。WHERE date_trunc('day', sold_at AT TIME ZONE 'UTC')::date BETWEEN ... 会使用索引；而 WHERE (sold_at AT TIME ZONE 'UTC')::date BETWEEN ... 不会。
PostgreSQL 18+ 可用虚拟生成列解决：
ALTER TABLE sale ADD sold_at_date DATE
GENERATED ALWAYS AS (date_trunc('day', sold_at AT TIME ZONE 'UTC'));
之后查询直接使用 sold_at_date 列，好处是索引更小、查询更快、无需人工保持表达式一致、时区无歧义。限制：PostgreSQL 18 尚不支持直接在虚拟列上建索引。
技巧三：哈希索引实现唯一性
当 urls 表含大 URL 文本时，唯一 B-Tree 索引会存储实际值，160 MB 的表产生 154 MB 索引。改用哈希排除约束：
ALTER TABLE urls
ADD CONSTRAINT urls_url_unique_hash
EXCLUDE USING HASH (url WITH =);
索引从 154 MB 降至 32 MB，缩小 5 倍，因为存储的是哈希值而非实际 URL。唯一性仍然强制生效（重复插入报错 conflicting key value violates exclusion constraint），等值查询依然快速（0.022 ms，快于 B-Tree 的 0.046 ms）。
限制对比：B-Tree 唯一索引支持外键引用、ON CONFLICT (column)、ON CONFLICT DO UPDATE；哈希排除约束均不支持，仅支持 ON CONFLICT ON CONSTRAINT（且只能 DO NOTHING）和 MERGE。替代 INSERT ... ON CONFLICT DO UPDATE 的写法：
MERGE INTO urls t
USING (VALUES (1000004, 'https://example.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);
使用方式
诊断查询：用 \di+ table_* 查看索引大小；用以下语句比较索引与表大小：
SELECT
relname AS name,
pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname LIKE 'your_table%'
ORDER BY pg_relation_size(oid) DESC;
用 SHOW constraint_exclusion; 检查设置。
决策树：查询是否在扫描不可能命中的行？是则启用 constraint_exclusion；否则判断索引是否接近表大小——是且为时间戳列则用日期函数索引，是且为大文本列则用哈希排除约束，否则标准 B-Tree 即可。
可用命令：ANALYZE [table]（分析查询性能）、CHECK-CONSTRAINTS（评估约束排除机会）、LOWER-CARDINALITY（寻找函数索引机会）、HASH-UNIQUE（评估大值哈希索引）、COMPARE-INDEXES（比较索引大小与性能）。
适用场景与限制
适用于临时查询环境、索引接近表大小、大文本值唯一性约束、按较粗粒度查询时间戳列等场景。限制包括：constraint_exclusion 会带来额外规划开销；函数索引要求表达式精确匹配；虚拟生成列在 PostgreSQL 18 尚不支持直接建索引；哈希排除约束不支持外键引用、ON CONFLICT (column) 与 ON CONFLICT DO UPDATE。