怎么解决PostgreSQL窗口函数调用的限制

46次阅读

没有评论

共计 6643 个字符，预计需要花费 17 分钟才能阅读完成。

这篇文章主要讲解了“怎么解决 PostgreSQL 窗口函数调用的限制”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“怎么解决 PostgreSQL 窗口函数调用的限制”吧！

背景

窗口函数是分析场景常用的，目前 (citus 7.5) 仅支持两种场景使用 window 函数，

1、partition by 必须是分布键。

2、where 条件里面带分布键的等值过滤条件。

本质上：目前（citus 7.5）window 函数不支持跨 shard 操作，或者说过程中不进行重分布。

而 Greenplum 这方面做得很好，是一个完整的 MPP 数据库。

citus window 函数的支持

postgres=# \set VERBOSITY verbose 
 
 
postgres=# select row_number() over(partition by bid order by aid) rn,* from pgbench_accounts; 
ERROR: 0A000: could not run distributed query because the window function that is used cannot be pushed down 
HINT: Window functions are supported in two ways. 
Either add an equality filter on the distributed tables  partition column 
or 
use the window functions with a PARTITION BY clause containing the distribution column 
LOCATION: DeferErrorIfQueryNotSupported, multi_logical_planner.c:938

满足以下条件即可支持

1、partition by 必须是分布键。

2、where 条件里面带分布键的等值过滤条件。

postgres=# select row_number() over(partition by bid order by aid) rn,* from pgbench_accounts where aid=1; 
 rn | aid | bid | abalance | filler 
----+-----+-----+----------+-------------------------------------------------------------------------------------- 
 1 | 1 | 1 | 0 | 
(1 row) 
 
postgres=# select row_number() over(partition by aid order by bid) rn,* from pgbench_accounts limit 1; 
 rn | aid | bid | abalance | filler 
----+-----+-----+----------+-------------------------------------------------------------------------------------- 
 1 | 298 | 1 | 0 | 
(1 row)

执行计划

postgres=# explain verbose select row_number() over(partition by aid order by bid) rn,* from pgbench_accounts limit 1; 
 QUERY PLAN 
--------------------------------------------------------------------------------------------------------------------------------------------------------- 
 Limit (cost=0.00..0.00 rows=0 width=0) 
 Output: remote_scan.rn, remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler 
 -  Custom Scan (Citus Real-Time) (cost=0.00..0.00 rows=0 width=0) 
 Output: remote_scan.rn, remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler 
 Task Count: 128 
 Tasks Shown: One of 128 
 -  Task 
 Node: host=172.24.211.224 port=1921 dbname=postgres 
 -  Limit (cost=705.99..706.01 rows=1 width=105) 
 Output: (row_number() OVER (?)), pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler 
 -  WindowAgg (cost=705.99..860.95 rows=7748 width=105) 
 Output: row_number() OVER (?), pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler 
 -  Sort (cost=705.99..725.36 rows=7748 width=97) 
 Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler 
 Sort Key: pgbench_accounts.aid, pgbench_accounts.bid 
 -  Seq Scan on public.pgbench_accounts_106812 pgbench_accounts (cost=0.00..205.48 rows=7748 width=97) 
 Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler 
(17 rows) 
 
postgres=# explain verbose select row_number() over(partition by bid order by aid) rn,* from pgbench_accounts where aid=1; 
 QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0) 
 Output: remote_scan.rn, remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler 
 Task Count: 1 
 Tasks Shown: All 
 -  Task 
 Node: host=172.24.211.232 port=1921 dbname=postgres 
 -  WindowAgg (cost=2.51..2.53 rows=1 width=105) 
 Output: row_number() OVER (?), aid, bid, abalance, filler 
 -  Sort (cost=2.51..2.51 rows=1 width=97) 
 Output: aid, bid, abalance, filler 
 Sort Key: pgbench_accounts.bid 
 -  Index Scan using pgbench_accounts_pkey_106819 on public.pgbench_accounts_106819 pgbench_accounts (cost=0.28..2.50 rows=1 width=97) 
 Output: aid, bid, abalance, filler 
 Index Cond: (pgbench_accounts.aid = 1) 
(14 rows)

Citus 未在 window 调用中支持重分布的过程。

greenplum window 函数的支持

支持任意姿势的 window 调用

postgres=# create table t(id int, c1 int, c2 int); 
NOTICE: Table doesn t have  DISTRIBUTED BY  clause -- Using column named  id  as the Greenplum Database data distribution key for this table. 
HINT: The  DISTRIBUTED BY  clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. 
CREATE TABLE 
 
postgres=# insert into t select random()*100000, random()*10, random()*100 from generate_series(1,10000000); 
INSERT 0 10000000 
 
postgres=# explain select row_number() over (partition by c1 order by id) rn,* from t ; 
 QUERY PLAN 
------------------------------------------------------------------------------------------------------------------ 
 Gather Motion 33:1 (slice2; segments: 33) (cost=1477974.88..1553064.94 rows=10012008 width=12) 
 -  Window (cost=1477974.88..1553064.94 rows=303395 width=12) 
 Partition By: c1 
 Order By: id 
 -  Sort (cost=1477974.88..1503004.90 rows=303395 width=12) 
 Sort Key: c1, id 
 //  以下在 citus 中用临时表代替  
  -  Redistribute Motion 33:33 (slice1; segments: 33) (cost=0.00..313817.24 rows=303395 width=12) 
 Hash Key: c1 
 -  Seq Scan on t (cost=0.00..113577.08 rows=303395 width=12) 
 Optimizer status: legacy query optimizer 
(10 rows)

甚至一个 SQL 中支持多个不同维度的 partition

postgres=# explain select row_number() over (partition by c1 order by id) rn1, row_number() over (partition by c2 order by c1) rn2, * from t ; 
 QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------------------------ 
 Gather Motion 33:1 (slice3; segments: 33) (cost=3017582.83..3192792.97 rows=10012008 width=12) 
 -  Subquery Scan coplan (cost=3017582.83..3192792.97 rows=303395 width=12) 
 -  Window (cost=3017582.83..3092672.89 rows=303395 width=12) 
 Partition By: coplan.c1 
 Order By: coplan.id 
 -  Sort (cost=3017582.83..3042612.85 rows=303395 width=12) 
 Sort Key: coplan.c1, coplan.id 
 //  以下在 citus 中用临时表代替  
  -  Redistribute Motion 33:33 (slice2; segments: 33) (cost=1477974.88..1853425.18 rows=303395 width=12) 
 Hash Key: coplan.c1 
 -  Subquery Scan coplan (cost=1477974.88..1653185.02 rows=303395 width=12) 
 -  Window (cost=1477974.88..1553064.94 rows=303395 width=12) 
 Partition By: t.c2 
 Order By: t.c1 
 -  Sort (cost=1477974.88..1503004.90 rows=303395 width=12) 
 Sort Key: t.c2, t.c1 
 //  以下在 citus 中用临时表代替  
  -  Redistribute Motion 33:33 (slice1; segments: 33) (cost=0.00..313817.24 rows=303395 width=12) 
 Hash Key: t.c2 
 -  Seq Scan on t (cost=0.00..113577.08 rows=303395 width=12) 
 Optimizer status: legacy query optimizer 
(19 rows)

感谢各位的阅读，以上就是“怎么解决 PostgreSQL 窗口函数调用的限制”的内容了，经过本文的学习后，相信大家对怎么解决 PostgreSQL 窗口函数调用的限制这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！

正文完