窗口函数查询优化求助：600万数据查询超时终止

生产表包含超过3200万条记录，测试用模拟表有600万条记录，以下查询执行时间超过10分钟，中途被终止，恳请提供优化建议。

原查询语句 #

select 
    name
    ,sales
    ,sum(sales) over(partition by name order by sales_dated_on rows between unbounded preceding and current row) as running_total
    ,first_value(sales) over(partition by name order by sales_dated_on ) as first_sales
    ,last_value(sales) over(partition by name order by sales_dated_on desc ) as first_sales
    ,sum(sales) over() as total_sales
    ,sum(sales) over(partition by name ) as total_sales_by_name
from demo.window_test wt ;

执行计划 #

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
WindowAgg  (cost=1153310.26..2678815.36 rows=5999981 width=51) (actual time=99673.366..101631.354 rows=6000000 loops=1)                                                       |
  Output: name, sales, (sum(sales) OVER (?)), (first_value(sales) OVER (?)), (last_value(sales) OVER (?)), sum(sales) OVER (?), (sum(sales) OVER (?)), sales_dated_on         |
  Buffers: shared hit=15762 read=22455, temp read=22522984 written=199339                                                                                                     |
  ->  WindowAgg  (cost=1153310.26..2603815.59 rows=5999981 width=43) (actual time=30058.611..96819.860 rows=6000000 loops=1)                                                  |
        Output: name, sales_dated_on, sales, (last_value(sales) OVER (?)), (sum(sales) OVER (?)), (first_value(sales) OVER (?)), sum(sales) OVER (?)                          |
        Buffers: shared hit=15762 read=22455, temp read=22438358 written=156857                                                                                               |
        ->  WindowAgg  (cost=1153310.26..2513815.88 rows=5999981 width=35) (actual time=21230.484..91691.264 rows=6000000 loops=1)                                            |
              Output: name, sales_dated_on, sales, (last_value(sales) OVER (?)), (sum(sales) OVER (?)), first_value(sales) OVER (?)                                           |
              Buffers: shared hit=15762 read=22455, temp read=22373366 written=123159                                                                                         |
              ->  WindowAgg  (cost=1153310.26..2408816.21 rows=5999981 width=31) (actual time=21230.475..32732.391 rows=6000000 loops=1)                                      |
                    Output: name, sales_dated_on, sales, (last_value(sales) OVER (?)), sum(sales) OVER (?)                                                                    |
                    Buffers: shared hit=15762 read=22455, temp read=89153 written=89461                                                                                       |
                    ->  Incremental Sort  (cost=1153310.26..2303816.54 rows=5999981 width=23) (actual time=21230.460..29881.325 rows=6000000 loops=1)                         |
                          Output: name, sales_dated_on, sales, (last_value(sales) OVER (?))                                                                                   |
                          Sort Key: wt.name, wt.sales_dated_on                                                                                                                |
                          Presorted Key: wt.name                                                                                                                              |
                          Full-sort Groups: 9  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB                                                                |
                          Pre-sorted Groups: 9  Sort Method: external merge  Average Disk: 22207kB  Peak Disk: 22208kB                                                        |
                          Buffers: shared hit=15762 read=22455, temp read=89153 written=89461                                                                                 |
                          ->  WindowAgg  (cost=1019809.47..1139809.09 rows=5999981 width=23) (actual time=20317.487..26224.097 rows=6000000 loops=1)                          |
                                Output: name, sales_dated_on, sales, last_value(sales) OVER (?)                                                                               |
                                Buffers: shared hit=15762 read=22455, temp read=58332 written=58487                                                                           |
                                ->  Sort  (cost=1019809.47..1034809.42 rows=5999981 width=19) (actual time=20317.469..22908.680 rows=6000000 loops=1)                         |
                                      Output: name, sales_dated_on, sales                                                                                                     |
                                      Sort Key: wt.name, wt.sales_dated_on DESC                                                                                               |
                                      Sort Method: external merge  Disk: 176024kB                                                                                             |
                                      Buffers: shared hit=15762 read=22455, temp read=58332 written=58487                                                                     |
                                      ->  Seq Scan on demo.window_test wt  (cost=0.00..98216.81 rows=5999981 width=19) (actual time=0.015..890.826 rows=6000000 loops=1)|
                                            Output: name, sales_dated_on, sales                                                                                               |
                                            Buffers: shared hit=15762 read=22455   

测试表创建脚本 #

create table demo.window_test(name varchar(20),sales int,sales_dated_on timestamp);

insert into demo.window_test
select 
    --concat('Group' ,id::text),
    concat('Group',floor(random()*(10-1)+1)::INT) as name
    ,floor(random()*(500-1)+1)::INT as sales_amount
    ,now() -'1 days'::interval * random() * ((10000+1)-1)
from 
    generate_series(1,10) as id
    ,generate_series(1,600000) ;

优化建议 #

1. 统一窗口排序规则，避免重复排序 #

执行计划显示存在两次独立排序（正序和倒序），这是主要性能消耗点。调整last_value的窗口范围，复用正序排序结果：

• 原last_value(sales) over(partition by name order by sales_dated_on desc )改为last_value(sales) over(partition by name order by sales_dated_on rows between unbounded preceding and unbounded following)
• 原因：last_value默认窗口范围是到当前行，必须显式指定完整范围才能获取分组内最后一条记录的sales值，同时避免倒序排序。

调整后查询：

select 
    name
    ,sales
    ,sum(sales) over(partition by name order by sales_dated_on rows between unbounded preceding and current row) as running_total
    ,first_value(sales) over(partition by name order by sales_dated_on) as first_sales
    ,last_value(sales) over(partition by name order by sales_dated_on rows between unbounded preceding and unbounded following) as last_sales
    ,sum(sales) over() as total_sales
    ,sum(sales) over(partition by name) as total_sales_by_name
from demo.window_test wt ;

2. 创建覆盖索引，消除全表扫描与排序 #

创建包含分组、排序、查询字段的覆盖索引，让数据库直接通过索引完成窗口计算：

create index idx_window_test_name_date_sales on demo.window_test(name, sales_dated_on) include(sales);

该索引包含所有所需字段，数据库可利用索引的有序性跳过全表扫描和磁盘排序步骤。

3. 拆分聚合计算，减少窗口函数重复计算 #

将全局和分组的聚合结果提前计算，再关联回主查询，避免窗口函数重复遍历数据：

with global_total as (
    select sum(sales) as total_sales from demo.window_test
),
name_totals as (
    select name, sum(sales) as total_sales_by_name from demo.window_test group by name
)
select 
    wt.name
    ,wt.sales
    ,sum(wt.sales) over(partition by wt.name order by wt.sales_dated_on rows between unbounded preceding and current row) as running_total
    ,first_value(wt.sales) over(partition by wt.name order by wt.sales_dated_on) as first_sales
    ,last_value(wt.sales) over(partition by wt.name order by wt.sales_dated_on rows between unbounded preceding and unbounded following) as last_sales
    ,gt.total_sales
    ,nt.total_sales_by_name
from demo.window_test wt
join name_totals nt on wt.name = nt.name
cross join global_total gt;

4. 调整数据库参数，优化内存使用 #

如果磁盘排序无法避免，临时调整参数提升排序效率：

• 增大work_mem（如set work_mem = '256MB';，根据服务器内存配置调整），让更多排序在内存中完成，减少磁盘IO。
• 索引创建时可增大maintenance_work_mem，加快索引构建速度。

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