PostgreSQL中GROUP BY + HAVING max查询优化咨询

问题背景 #

需要优化的查询语句：

SELECT
  MAX(id),
  claim_id
FROM
  tasks
WHERE
  type = 'UNRECONCILED'
GROUP BY
  claim_id
HAVING
  max(status) IN ('IN_PROGRESS', 'BACKLOG')

已尝试创建索引 tasks(type, claim_id, status desc, id desc)，原本预期该索引能让数据库跳过全表扫描直接定位结果，但实际测试发现：移除HAVING子句后，查询计划的成本反而更低。该现象在PostgreSQL 14.12和17版本中均存在，复现脚本见文末。

针对这个情况，有三个疑问需要解答：

1. 我对HAVING子句的上述理解是否有误？
2. 不采用缓存、删除列等激进手段的话，该查询是否已接近最优状态？
3. 若未达最优，还能通过哪些方式进一步优化？

1. 对HAVING子句的理解偏差 #

你的理解确实存在误差。虽然你创建的索引能快速筛选出type='UNRECONCILED'的行，并按claim_id分组，但HAVING max(status) IN ('IN_PROGRESS', 'BACKLOG')这个条件无法直接通过索引“跳过”不符合的分组：

• 索引里的status是降序排列，但max(status)是分组后的聚合结果，PostgreSQL必须先为每个claim_id计算出最大的status值，再判断是否符合条件，这多了一步聚合判断的开销。
• 移除HAVING后，查询只需要分组取每个claim_id的MAX(id)，索引可以直接按claim_id分组后取最后一条（因为id desc）的id值，无需额外计算过滤，自然成本更低。

2. 当前查询是否接近最优？ #

在不使用缓存、删除列这类激进手段的前提下，当前方案已经比较接近最优，但仍有优化空间。
你创建的索引已经是覆盖索引，避免了回表操作，但HAVING子句带来的聚合过滤开销是业务逻辑决定的，无法完全消除，不过可以通过调整索引结构或查询写法进一步降低这部分开销。

3. 进一步优化的方式 #

（1）调整索引结构，优化max(status)的计算

把索引改成优先支持max(status)的快速获取，比如使用包含列的覆盖索引（PostgreSQL 11+支持）：

CREATE INDEX idx_tasks_type_claimid_status_id ON tasks (type, claim_id, status DESC) INCLUDE (id);

这样每个claim_id分组下的status是降序排列的，PostgreSQL可以直接取分组内第一条的status作为max(status)，不用遍历整个分组计算，能减少聚合的开销。

（2）改写查询，提前过滤无效行

如果业务逻辑允许，可以先筛选出可能符合条件的行，再分组计算：

SELECT
  MAX(id),
  claim_id
FROM (
  SELECT id, claim_id, status
  FROM tasks
  WHERE type = 'UNRECONCILED'
    -- 只保留可能让分组max(status)符合条件的行
    AND status IN ('IN_PROGRESS', 'BACKLOG', 'DEFAULT')
) t
GROUP BY claim_id
HAVING MAX(status) IN ('IN_PROGRESS', 'BACKLOG');

这个改写的效果取决于数据分布，如果大部分UNRECONCILED类型的行status都不在目标集合，能有效减少分组的数据量；反之收益有限。

（3）优化统计信息

确保表的统计信息准确且最新，运行ANALYZE tasks;后，PostgreSQL能更精准地估算行数并选择最优计划。如果status列的数据分布极端，可以手动提高该列的统计精度：

ALTER TABLE tasks ALTER COLUMN status SET STATISTICS 1000;
ANALYZE tasks;

让优化器对status列的分布有更精细的认知，从而做出更合理的计划选择。

（4）物化视图预计算（非激进方案）

如果这个查询的频率很高、但数据更新不频繁，可以创建物化视图预计算分组结果：

CREATE MATERIALIZED VIEW mv_tasks_unreconciled AS
SELECT
  claim_id,
  MAX(id) AS max_id,
  MAX(status) AS max_status
FROM tasks
WHERE type = 'UNRECONCILED'
GROUP BY claim_id;

CREATE INDEX idx_mv_claimid_status ON mv_tasks_unreconciled (claim_id, max_status);

查询时直接从物化视图取数据：

SELECT max_id, claim_id
FROM mv_tasks_unreconciled
WHERE max_status IN ('IN_PROGRESS', 'BACKLOG');

需要定期刷新物化视图（REFRESH MATERIALIZED VIEW mv_tasks_unreconciled;），适合读多写少的场景。

复现脚本 #

CREATE TABLE tasks (id INT, status TEXT, type TEXT, claim_id INT);  
INSERT INTO tasks SELECT n, 'DEFAULT', 'DEFAULT', n%10000 FROM generate_series(1, 5000000) n;
INSERT INTO tasks SELECT n, 'DEFAULT', 'UNRECONCILED', n%10000 FROM generate_series(1, 492000) n;
INSERT INTO tasks SELECT n, 'IN_PROGRESS', 'DEFAULT', n%10000 FROM generate_series(1, 2080000) n;
INSERT INTO tasks SELECT n, 'IN_PROGRESS', 'UNRECONCILED', n%10000 FROM generate_series(1, 60000) n;
INSERT INTO tasks SELECT n, 'BACKLOG', 'DEFAULT', n%10000 FROM generate_series(1, 373000) n;
INSERT INTO tasks SELECT n, 'BACKLOG', 'UNRECONCILED', n%10000 FROM generate_series(1, 145000) n;

CREATE index ON tasks (type, claim_id, status DESC, id DESC);
analyze tasks;

-- 带HAVING的查询计划
EXPLAIN(ANALYZE,BUFFERS,VERBOSE) SELECT
  MAX(id),
  claim_id
FROM
  tasks
WHERE
  type = 'UNRECONCILED'
GROUP BY
  claim_id
HAVING
  max(status) IN ('IN_PROGRESS', 'BACKLOG');

-- 不带HAVING的查询计划
ANALYZE tasks;
EXPLAIN(ANALYZE,BUFFERS,VERBOSE) SELECT
  MAX(id),
  claim_id
FROM
  tasks
WHERE
  type = 'UNRECONCILED'
GROUP BY
  claim_id;

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