查询优化经常被归结为“找到正确的索引”。但在生产环境里，真正困难的地方往往是：你知道应该建什么索引，却没有条件立刻建出来。
一张 750 GB、包含 160 亿行的 PostgreSQL 单体表，新增一个 GiST 索引可能需要超过 24 小时。业务又不能停，查询却已经从几秒恶化到几分钟。这时，优化问题就不再是“哪个索引最理想”，而是“在不能改变大表结构的前提下，如何尽可能减少它需要读取的数据”。
下面这个案例展示了一种很实用的思路：把业务事实抽取到一个小型映射表中，让它承担类似“临时索引”的作用。
查询为什么突然变慢
待优化的查询可以抽象成这样：
SELECT *
FROM t
WHERE a = $1
AND b = $2
AND c = $3
AND start_date <= $4
AND end_date >= $4;
它要查找的是：给定一个日期，返回所有时间区间 [start_date, end_date] 覆盖该日期的记录，同时满足 a、b、c 三个等值条件。
如果可以自由修改表结构，比较自然的方案是把两个日期转换成 PostgreSQL 的 daterange，再创建 GiST 索引：
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX CONCURRENTLY t_active_period_gist_idx
ON t
USING gist (
daterange(start_date, end_date, '[]'),
a,
b,
c
);
但这里有一个现实问题：索引建立在 160 亿行的大表上，而且还是 GiST 索引。即使使用 CREATE INDEX CONCURRENTLY 降低对生产写入的影响，构建时间和额外资源消耗仍然可能无法接受。
原有索引也不理想。最接近的索引可能是：
(a, start_date, end_date)
但 a 的选择性最低。当查询日期较晚时，几乎所有记录都满足 start_date <= 查询日期，数据库必须读取大量索引项和表数据，再过滤掉不符合 end_date >= 查询日期 的记录。
这就是典型的选择性问题：查询条件看起来很多，但真正能快速缩小扫描范围的条件并没有出现在合适的索引前缀中。
第一种尝试：人为增加日期下界
一个直接的办法，是给查询增加一个更严格的 start_date 下界：
SELECT *
FROM t
WHERE a = $1
AND b = $2
AND c = $3
AND start_date <= $4
AND end_date >= $4
AND start_date >= DATE '2026-01-01';
如果业务能够保证“有效记录不可能早于某个固定日期”，这个方法非常简单，也可能立刻改善性能。
但本案例要求覆盖全部历史数据，不能凭经验截断时间范围。于是问题变成了：对于当前查询的 (a, b, c) 组合，最早可能出现的 start_date 到底是哪一天？
让优化器先知道每个组合的最早日期
最初可以尝试从大表中计算某个 a 的最小日期：
WITH min_start_day AS MATERIALIZED (
SELECT min(start_date) AS first_date
FROM t
WHERE a = $1
)
SELECT *
FROM t
WHERE a = $1
AND b = $2
AND c = $3
AND start_date <= $4
AND end_date >= $4
AND start_date >= (
SELECT first_date
FROM min_start_day
);
MATERIALIZED 的作用是明确告诉 PostgreSQL：先执行这个 CTE，并将结果作为一次独立的中间结果使用。它并不会自动让 min(start_date) 变快；如果最小日期仍然需要扫描大量数据，整体查询依旧可能很慢。
更关键的是，这个查询只按 a 计算最早日期，而实际业务条件是 (a, b, c) 的组合。即使按 a 找到的最小日期是正确的，它也可能比当前组合真正的最早日期早很多，无法有效减少扫描范围。
从业务数据中找到真正的边界
后续检查业务提供的条件列表时，发现每个 (a, b, c) 组合不仅包含查询条件，还包含该组合第一次出现的日期。
这条信息非常重要。它不是从查询语句本身推导出来的，而是业务数据中的一个事实：对于每个组合，start_date 不会早于某个已知日期。
可以把这些边界保存到一个很小的映射表中：
CREATE TABLE mapping_table (
a text NOT NULL,
b text NOT NULL,
c text NOT NULL,
first_date date NOT NULL,
PRIMARY KEY (a, b, c)
);
INSERT INTO mapping_table (a, b, c, first_date)
VALUES
('customer-001', 'plan-a', 'region-east', DATE '2024-03-12'),
('customer-002', 'plan-b', 'region-west', DATE '2025-01-08');
实际类型应与生产表中的列保持一致。主键会为 (a, b, c) 创建唯一 B-tree 索引，使边界查询只需要读取一行：
WITH min_start_day AS MATERIALIZED (
SELECT first_date
FROM mapping_table
WHERE a = $1
AND b = $2
AND c = $3
)
SELECT *
FROM t
WHERE a = $1
AND b = $2
AND c = $3
AND start_date <= $4
AND end_date >= $4
AND start_date >= (
SELECT first_date
FROM min_start_day
);
这个额外条件并没有改变查询结果，前提是 mapping_table.first_date 的定义正确：它必须是该 (a, b, c) 组合在大表中可能出现的最早 start_date。
但它改变了执行计划可以处理的数据范围。原有索引至少可以利用新的 start_date >= first_date 条件，避免从一个过于宽泛的历史区间开始扫描。
一个可运行的最小验证脚本如下：
CREATE TEMP TABLE t (
a text NOT NULL,
b text NOT NULL,
c text NOT NULL,
start_date date NOT NULL,
end_date date NOT NULL,
payload text
);
CREATE INDEX t_lookup_idx
ON t (a, start_date, end_date);
INSERT INTO t
SELECT
'customer-001',
DATE '2020-01-01' + (g % 2500),
DATE '2020-01-01' + (g % 2500) + 30,
'row-' || g
FROM generate_series(1, 100000) AS g;
CREATE TEMP TABLE mapping_table (
a text NOT NULL,
b text NOT NULL,
c text NOT NULL,
first_date date NOT NULL,
PRIMARY KEY (a, b, c)
);
INSERT INTO mapping_table
VALUES ('customer-001', 'plan-a', 'region-east', DATE '2020-01-01');
EXPLAIN (ANALYZE, BUFFERS)
WITH min_start_day AS MATERIALIZED (
SELECT first_date
FROM mapping_table
WHERE a = 'customer-001'
AND b = 'plan-a'
AND c = 'region-east'
)
SELECT *
FROM t
WHERE a = 'customer-001'
AND b = 'plan-a'
AND c = 'region-east'
AND start_date <= DATE '2026-08-16'
AND end_date >= DATE '2026-08-16'
AND start_date >= (SELECT first_date FROM min_start_day);
这个示例中的数据和索引只是为了验证查询形状。真实环境中应使用 EXPLAIN (ANALYZE, BUFFERS) 对比修改前后的实际读取块数、过滤行数和执行时间。
部分索引可以先解决一部分问题
如果业务只关心少量高频组合，另一个选择是为这些组合创建部分索引。索引可以使用 CONCURRENTLY，降低对生产写入的阻塞：
CREATE INDEX CONCURRENTLY t_subset_start_date_idx
ON t (start_date, end_date, a)
WHERE a = 'customer-001'
AND b = 'plan-a'
AND c = 'region-east';
对于部分索引，查询条件必须能够让 PostgreSQL 推断出查询满足索引谓词。参数化查询是否能稳定匹配部分索引，取决于准备语句、参数类型和执行计划选择，因此应该使用实际应用查询进行验证，而不是只看一条手工 SQL。
在这个案例中，针对客户列出的少量组合创建部分索引后，相关查询可以降到 100 毫秒以内。这是一种有效的应急方案，但它有明显边界：
- 只覆盖预先知道的组合；
- 组合数量增加后，索引维护成本会上升；
- 查询模式变化时，需要重新评估索引谓词；
- 它仍然需要在大表上构建索引，构建时间只是比全局理想索引更可控。
映射表方案的特点不同。它不负责直接返回业务记录，而是先提供一个极小的范围边界，再借助大表已有索引完成主查询。可以把它理解为一个“缩小搜索起点的索引表”。
不想复制数据时，使用外部表
业务方可能已经在另一个数据库中维护了 (a, b, c) 与 first_date 的对应关系，不希望在两个数据库里长期保存同一份数据。
如果环境允许，可以通过 postgres_fdw 访问远程表。下面是一个简化配置示例，具体权限和连接参数应按生产安全要求设置：
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER metadata_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host 'metadata-db.internal',
port '5432',
dbname 'metadata'
);
CREATE USER MAPPING FOR app_user
SERVER metadata_db
OPTIONS (
user 'readonly_metadata',
password 'change-this-secret'
);
CREATE FOREIGN TABLE foreign_table (
a text NOT NULL,
b text NOT NULL,
c text NOT NULL,
start_date date NOT NULL
)
SERVER metadata_db
OPTIONS (
schema_name 'public',
table_name 'mapping_table'
);
之后查询可以保持相同结构：
WITH min_start_day AS MATERIALIZED (
SELECT start_date AS first_date
FROM foreign_table
WHERE a = $1
AND b = $2
AND c = $3
)
SELECT *
FROM t
WHERE a = $1
AND b = $2
AND c = $3
AND start_date <= $4
AND end_date >= $4
AND start_date >= (
SELECT first_date
FROM min_start_day
);
映射表只有约两千条记录时，跨数据库读取一行元数据的成本通常很低。不过不能只凭数据量做判断，仍要检查网络延迟、远程连接复用、事务语义和故障行为。尤其要给远程用户配置只读权限，并限制它只能访问必要的表和列。
如何判断这种优化是否可靠
这个方案的关键不是 SQL 写法，而是边界数据是否可信。上线前至少需要验证以下几件事。
1. 边界值不能过晚
如果 first_date 比真实最早日期还晚，查询会漏掉合法记录。这是正确性问题，不是性能问题。
可以定期用离线任务抽样核对：
SELECT a, b, c, min(start_date) AS actual_first_date
FROM t
GROUP BY a, b, c;
对于超大表，不建议频繁在线执行全量聚合。可以使用增量维护、变更事件、定期抽样或专门的离线计算流程。
2. 边界值过早只会影响性能
如果 first_date 早于真实最早日期，结果通常仍然正确，但扫描范围会变宽。也就是说，映射表的维护策略应优先保证“不晚于真实边界”，再逐步追求更精确的值。
3. 检查实际计划和读取量
不要只比较总执行时间。重点观察：
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
WITH min_start_day AS MATERIALIZED (
SELECT first_date
FROM mapping_table
WHERE a = 'customer-001'
AND b = 'plan-a'
AND c = 'region-east'
)
SELECT *
FROM t
WHERE a = 'customer-001'
AND b = 'plan-a'
AND c = 'region-east'
AND start_date <= DATE '2026-08-16'
AND end_date >= DATE '2026-08-16'
AND start_date >= (SELECT first_date FROM min_start_day);
重点看顺序扫描是否消失、索引扫描读取了多少块、过滤前后行数差距多大，以及远程表扫描是否发生了不必要的数据传输。
4. 确认查询参数类型
日期参数应使用 date 类型，避免隐式转换导致索引条件无法按预期使用：
PREPARE find_records(text, text, text, date) AS
SELECT *
FROM t
WHERE a = $1
AND b = $2
AND c = $3
AND start_date <= $4
AND end_date >= $4;
不能建理想索引时的决策清单
遇到大型生产表上的慢查询，可以按这个顺序排查：
- 先确认查询是否因为数据分布变化跨过了性能拐点，而不是简单地认为“SQL 一直如此”。
- 用
EXPLAIN (ANALYZE, BUFFERS)找出真正读取最多的数据范围。 - 判断理想索引是否能在可接受的窗口内完成构建和维护。
- 如果不能全局建索引，确认是否只需要优化一小组高频条件，并考虑部分索引。
- 检查业务数据中是否存在可证明的边界、分类、生命周期或首次出现时间。
- 将这些小规模事实放入映射表，先缩小大表扫描范围。
- 如果事实位于另一个数据库，评估
postgres_fdw或由应用执行一次轻量查询的方案。 - 用实际参数、实际数据分布和缓冲区统计验证性能与正确性。
查询优化最终依赖的并不只是 PostgreSQL 版本、索引类型和执行计划。很多时候，最有价值的信息藏在业务数据的关系里：某个组合从什么时候开始出现，哪些组合永远不会发生，哪些历史记录实际上不可能属于当前查询。
当理想索引需要一天以上才能完成时，不一定只能等待。一个几千行的映射表，加上一个准确的业务边界，就可能让数据库避开数十亿行中绝大部分不必要的读取。代价是额外的数据维护、跨库依赖和边界正确性责任；但在生产事故中，这往往是值得认真评估的工程选项。