作者
digoal
日期
2020-09-16
标签
PostgreSQL , incremental sort , window
背景
window函数, 支持增量排序.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=62e221e1c01e3985d2b8e4b68c364f8486c327ab
```
Allow incremental sorts for windowing functions
author David Rowley drowley@postgresql.org
Tue, 15 Sep 2020 19:44:45 +0800 (23:44 +1200)
committer David Rowley drowley@postgresql.org
Tue, 15 Sep 2020 19:44:45 +0800 (23:44 +1200)
commit 62e221e1c01e3985d2b8e4b68c364f8486c327ab
tree cf4466e3861bd81461c691a128a6d77455efad39 tree | snapshot
parent fe4f36bcde182d57dee5dba898076aba5d826515 commit | diff
Allow incremental sorts for windowing functions
This expands on the work done in d2d8a229b and allows incremental sort
to be considered during create_window_paths().
Author: David Rowley
Reviewed-by: Daniel Gustafsson, Tomas Vondra
Discussion: https://postgr.es/m/CAApHDvoOHobiA2x13NtWnWLcTXYj9ddpCkv9PnAJQBMegYf_xw%40mail.gmail.com
```
例如, 排序KEY为empsalary.depname, empsalary.enroll_date DESC, 增量排序为empsalary.depname, empsalary.enroll_date , 因为depname在上阶段已排序.
+-- Test incremental sorting
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT depname,
+ empno,
+ salary,
+ enroll_date,
+ row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp,
+ row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp
+ FROM empsalary) emp
+WHERE first_emp = 1 OR last_emp = 1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: ((emp.first_emp = 1) OR (emp.last_emp = 1))
+ -> WindowAgg
+ -> Incremental Sort
+ Sort Key: empsalary.depname, empsalary.enroll_date
+ Presorted Key: empsalary.depname
+ -> WindowAgg
+ -> Sort
+ Sort Key: empsalary.depname, empsalary.enroll_date DESC
+ -> Seq Scan on empsalary
+(10 rows)
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
