PostgreSQL双列范围条件下数据聚合查询优化问询

在PostgreSQL 10.19中，针对particles表的粒径、速度范围聚合统计需求，原查询因重复扫描全表导致性能低下（3万条数据耗时超1分钟），可通过以下方式重写查询并优化：

原查询问题分析 #

原查询先生成所有粒径-速度范围的笛卡尔积组合，再对每个组合执行一次子查询扫描全表统计数量。假设区间组合数为5000（粒径50个区间×速度100个区间），则需扫描表5000次，总操作量达到1.5亿次，效率极低。

优化后的查询 #

通过先对粒子数据做分组映射，再与全量区间组合左连接的方式，仅需扫描一次particles表：

WITH speed_series AS (
   SELECT generate_series(-1, 19.8, 0.2) AS speed_from
), speed_range AS (
   SELECT speed_from, speed_from + 0.2 AS speed_to FROM speed_series
), diameter_series AS (
   SELECT generate_series(0, 9.8, 0.2) AS diameter_from
), diameter_range AS (
   SELECT 
      d.diameter_from, d.diameter_from + 0.2 AS diameter_to,
      s.speed_from, s.speed_to
   FROM diameter_series d
   CROSS JOIN speed_range s
), particle_groups AS (
   SELECT
      -- 计算粒子所属粒径区间的起始值
      FLOOR(particle_diameter / 0.2) * 0.2 AS diameter_group,
      -- 计算粒子所属速度区间的起始值
      FLOOR(particle_speed / 0.2) * 0.2 AS speed_group,
      COUNT(*) AS count
   FROM particles
   GROUP BY diameter_group, speed_group
)
SELECT
   dr.diameter_from,
   dr.diameter_to,
   dr.speed_from,
   dr.speed_to,
   COALESCE(pg.count, 0) AS particle_count
FROM diameter_range dr
LEFT JOIN particle_groups pg
   ON dr.diameter_from = pg.diameter_group
   AND dr.speed_from = pg.speed_group
ORDER BY dr.diameter_from, dr.speed_from;

优化原理 #

1. 单次表扫描：通过数学计算（FLOOR(value / step) * step）直接将粒子映射到对应的区间起始值，分组统计仅需扫描一次particles表，时间复杂度为O(n)（n为粒子数量）。
2. 左连接补全空区间：将分组统计结果与全量区间组合左连接，确保没有粒子的区间也能显示数量0，保持结果完整性。

进一步性能提升：添加索引 #

若这类聚合查询是高频操作，可创建复合索引或表达式索引来加速分组统计：

复合索引（适用于多种范围查询场景） #

CREATE INDEX idx_particles_diameter_speed ON particles (particle_diameter, particle_speed);

表达式索引（精准匹配本次分组逻辑） #

CREATE INDEX idx_particles_group ON particles 
   ((FLOOR(particle_diameter / 0.2) * 0.2), (FLOOR(particle_speed / 0.2) * 0.2));

内容的提问来源于stack exchange，提问作者Yannick Guéhenneux