PostgreSQL 14 日期范围分组统计查询优化方案咨询

现有索引无效的核心原因 #

之前创建的entry_date单列索引、entry_date::date表达式索引没有带来性能提升，核心问题有两个：

• 单列entry_date索引仅能定位符合时间范围的行位置，无法直接返回查询需要的user_name字段，扫描索引后需要回表做大量随机IO捞取数据，当10天范围内数据量较大时，回表开销甚至高于全表扫描，优化器会直接选择全表扫。
• entry_date::date的表达式索引和查询过滤条件不匹配：过滤条件是带时分秒的timestamp类型范围比较，无法直接命中按date类型构建的索引，自然没有优化效果。

优化手段 #

• 构建覆盖联合索引（优先操作，收益最明显）
  该查询仅依赖entry_date、user_name两个字段，直接创建包含两个字段的联合索引，完全避免回表：

  CREATE INDEX idx_q1_entrydate_username ON pdq.q_1 (entry_date, user_name);
  

  该索引按entry_date有序存储，首先可以快速定位10天时间范围的索引区间，同时索引叶子节点已经存储了对应的user_name值，扫描索引的过程中就能拿到分组需要的全部字段，IO成本会出现量级下降。

• 简化分组逻辑，减少计算开销
  现有写法用5个extract函数拆分时间字段做分组，本质是将entry_date截断到分钟粒度，可以用date_trunc替代重复计算，减少分组阶段的计算量和排序字段数量：

  SELECT
      user_name,
      extract(year from minute_ts) as year,
      extract(month from minute_ts) as month, 
      extract(day from minute_ts) as day,
      extract(hour from minute_ts) as hour,
      extract(minute from minute_ts) as minute,
      count(*) as cnt
  FROM (
      SELECT 
          user_name,
          date_trunc('minute', entry_date) as minute_ts
      FROM pdq.q_1 
      WHERE entry_date > current_timestamp - interval '10 day'
  ) t
  GROUP BY user_name, minute_ts;
  

• 调整内存参数避免分组排序落盘
  查看执行计划如果Sort或HashAggregate节点出现Disk:标识，说明work_mem分配不足，分组排序过程溢出到磁盘，会严重拖慢性能。可以在会话级临时调大参数：

  -- 数值根据服务器剩余内存调整，不要设置过大避免OOM
  SET work_mem = '64MB';
  

• 高频查询场景用物化视图预聚合
  如果该查询是高频访问的统计类需求，可以创建按user_name+分钟粒度预聚合的物化视图，定时刷新最近数据，查询直接访问预聚合结果，性能可提升1-2个数量级：

  CREATE MATERIALIZED VIEW mv_q1_minute_stat
  AS
  SELECT
      user_name,
      date_trunc('minute', entry_date) as minute_ts,
      count(*) as cnt
  FROM pdq.q_1
  GROUP BY user_name, date_trunc('minute', entry_date);
  

  可以配合定时任务，仅刷新最近10天的数据，降低刷新成本。

原查询执行计划 #

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