PostgreSQL 12特定过滤条件下慢查询优化求助

现有一张document表，包含约250万行数据、100余列，核心列定义如下：

CREATE TABLE document
(
  id serial PRIMARY KEY,
  organizationid INTEGER,
  status_1 TEXT NOT NULL,
  status_2 INTEGER DEFAULT 0 NOT NULL
);

CREATE INDEX ON document (organizationid);
CREATE INDEX ON document (status_1);
CREATE INDEX ON document (status_2);

需要优化的查询语句：

SELECT *
FROM document
WHERE status_1 = '42' AND status_2 = 0 AND organizationid = 42 
ORDER BY id
LIMIT 25;

问题现象 #

该查询对大部分组织运行正常：数据量多的组织查询耗时不足1秒，数据量少的组织通过organizationid索引扫描+排序，耗时不足50ms。但针对组织42时，因该组织无符合条件的文档（或偶尔不足20条），查询耗时长达数分钟。

根因分析 #

数据分布特征：status_1='42'的文档占95%，status_2=0的占10%，organizationid=42的占2%。PostgreSQL预估符合条件的数据约4750条，因此选择主键索引全表扫描计划；但实际符合条件的数据极少，导致全表扫描效率极低。

已尝试的优化（未解决问题） #

• 开启自动清理与分析，测试前手动执行ANALYZE
• 将GEQO_EFFORT调至最大值10，DEFAULT_STATISTICS_TARGET设为1000
• 创建复合统计信息：CREATE STATISTICS custom_1 ON organizationid, status_1, status_2 FROM document;
• 创建表达式索引：CREATE INDEX ON document ((status_1 = '42' AND status_2 = 0 AND organizationid = 42));
• 所有变更后重新执行ANALYZE，且pg_stats与pg_stats_ext显示统计信息正确，复合统计信息表明该过滤组合并非常见组合，表达式索引仅含false值。

1. 创建匹配查询逻辑的复合索引 #

创建包含过滤条件+排序字段的复合索引，让优化器可以直接通过索引完成过滤与排序，无需全表扫描：

CREATE INDEX document_idx_org_status_id ON document (organizationid, status_1, status_2, id);

原理：该索引先按organizationid过滤出目标组织的所有行，再依次过滤status_1和status_2，最后id字段保证索引内的行已经按排序要求有序，查询时直接取前25条即可，完全匹配WHERE+ORDER BY+LIMIT的逻辑。

2. 针对性创建部分索引 #

针对组织42的特殊场景，创建仅包含符合条件行的部分索引：

CREATE INDEX document_partial_org42 ON document (id) 
WHERE organizationid = 42 AND status_1 = '42' AND status_2 = 0;

原理：该索引体积极小（几乎无数据），查询时优化器会直接扫描这个小索引，瞬间确认是否存在符合条件的行，避免全表扫描。

3. 调整列级统计信息粒度 #

针对organizationid列单独提高统计目标，让优化器更精准地掌握该列与其他过滤条件的组合分布：

ALTER TABLE document ALTER COLUMN organizationid SET STATISTICS 2000;
ANALYZE document;

原理：更高的统计目标会让PostgreSQL收集更详细的分布数据，修正对organizationid=42与status_1、status_2组合的行数预估，从而选择更优的执行计划。

4. 临时强制使用索引（验证/应急方案） #

若优化器仍未自动选择最优索引，可在查询中添加索引提示强制指定：

SELECT *
FROM document INDEX USING document_idx_org_status_id
WHERE status_1 = '42' AND status_2 = 0 AND organizationid = 42 
ORDER BY id
LIMIT 25;

注意：这是应急方案，优先通过索引和统计信息调整让优化器自动选择最优计划。

内容的提问来源于stack exchange，提问作者Bunny Boss