PostgreSQL如何单查询获取各分组最高排名数据并优化性能

需求说明 #

需要获取每种job_type分组中，按priority（数值越小优先级越高）、created_at（时间越早越优先）排序后的最高排名任务的id。

现有实现及问题 #

表结构、索引与测试数据 #

CREATE TABLE job_queue (
  id SERIAL PRIMARY KEY,
  job_type VARCHAR,
  priority INT,
  created_at TIMESTAMP WITHOUT TIME ZONE
  -- 其他与问题无关的列省略
);

CREATE INDEX job_idx ON job_queue (job_type, priority, created_at);

INSERT INTO job_queue (id, job_type, priority, created_at) 
VALUES 
  (1, 'j1', 1, '2000-01-01 00:00:00'),
  (2, 'j1', 1, '2000-01-02 00:00:00'),
  (3, 'j1', 2, '2000-01-01 00:00:00'),
  (4, 'j2', 1, '2000-01-01 00:00:00'),
  (5, 'j2', 1, '2000-01-02 00:00:00'),
  (6, 'j2', 2, '2000-01-01 00:00:00');

原有查询 #

-- 获取每种任务类型中最早的最高优先级任务
SELECT id FROM (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY job_type 
      ORDER BY priority, created_at
    ) AS rank
  FROM job_queue
) AS ranked_jobs
WHERE rank = 1;

问题分析 #

该查询能得到正确结果，但性能较差：它需要先对全表数据进行排名计算，再过滤出排名为1的记录，无法有效利用已创建的联合索引。

优化方案：使用DISTINCT ON #

PostgreSQL特有的DISTINCT ON语法完美适配这种「分组取首行」的场景，且能直接利用已有的job_idx索引，实现和逐个查询每个job_type再合并结果等价的高效查询。

优化后的查询 #

SELECT DISTINCT ON (job_type) id
FROM job_queue
ORDER BY job_type, priority, created_at;

优势说明 #

1. 性能高效：DISTINCT ON (job_type)会按照ORDER BY的顺序，从索引中直接定位每个job_type分组的第一条符合排序规则的记录，无需全表扫描和排名计算，完全利用job_idx索引的有序性。
2. 简洁灵活：单条查询即可适配任意数量的job_type，返回所有目标id的结果集。
3. 结果等价：返回的结果和原有查询、以及逐个查询每个job_type再合并的结果完全一致。

执行计划验证 #

执行EXPLAIN ANALYZE可看到，该查询会使用Index Scan using job_idx on job_queue，直接遍历索引获取所需数据，性能远优于原有查询的全表排名方案。

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