ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > > PostgreSQL sharding : citus 系列3 - 窗口函数调用限制 与 破解之法

PostgreSQL sharding : citus 系列3 - 窗口函数调用限制 与 破解之法

作者：gaokeke123 时间：2022-05-09 11:22:19 0 删除 编辑

标签

PostgreSQL , citus , 窗口函数

背景

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

1、partition by 必须是分布键。

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

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

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

citus window函数的支持

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

满足以下条件即可支持

1、partition by 必须是分布键。

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

  1. postgres=# select row_number() over(partition by bid order by aid) rn,* from pgbench_accounts where aid=1;  
  2.  rn | aid | bid | abalance |                                        filler                                          
  3. ----+-----+-----+----------+--------------------------------------------------------------------------------------  
  4.   1 |   1 |   1 |         |                                                                                       
  5. (1 row)  
  6.   
  7. postgres=# select row_number() over(partition by aid order by bid) rn,* from pgbench_accounts  limit 1;  
  8.  rn | aid | bid | abalance |                                        filler                                          
  9. ----+-----+-----+----------+--------------------------------------------------------------------------------------  
  10.   1 | 298 |   1 |         |                                                                                       
  11. (1 row)

执行计划

  1. postgres=# explain verbose select row_number() over(partition by aid order by bid) rn,* from pgbench_accounts  limit 1;  
  2.                                                                        QUERY PLAN                                                                          
  3. ---------------------------------------------------------------------------------------------------------------------------------------------------------  
  4.  Limit  (cost=0.00..0.00 rows= width=)  
  5.    Output: remote_scan.rn, remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler  
  6.    ->  Custom Scan (Citus Real-Time)  (cost=0.00..0.00 rows= width=)  
  7.          Output: remote_scan.rn, remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler  
  8.          Task Count: 128  
  9.          Tasks Shown: One of 128  
  10.          ->  Task  
  11.                Node: host=172.24.211.224 port=1921 dbname=postgres  
  12.                ->  Limit  (cost=705.99..706.01 rows=1 width=105)  
  13.                      Output: (row_number() OVER (?)), pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler  
  14.                      ->  WindowAgg  (cost=705.99..860.95 rows=7748 width=105)  
  15.                            Output: row_number() OVER (?), pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler  
  16.                            ->  Sort  (cost=705.99..725.36 rows=7748 width=97)  
  17.                                  Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler  
  18.                                  Sort Key: pgbench_accounts.aid, pgbench_accounts.bid  
  19.                                  ->  Seq Scan on public.pgbench_accounts_106812 pgbench_accounts  (cost=0.00..205.48 rows=7748 width=97)  
  20.                                        Output: pgbench_accounts.aid, pgbench_accounts.bid, pgbench_accounts.abalance, pgbench_accounts.filler  
  21. (17 rows)  
  22.   
  23. postgres=# explain verbose select row_number() over(partition by bid order by aid) rn,* from pgbench_accounts where aid=1;  
  24.                                                                          QUERY PLAN                                                                            
  25. -------------------------------------------------------------------------------------------------------------------------------------------------------------  
  26.  Custom Scan (Citus Router)  (cost=0.00..0.00 rows= width=)  
  27.    Output: remote_scan.rn, remote_scan.aid, remote_scan.bid, remote_scan.abalance, remote_scan.filler  
  28.    Task Count: 1  
  29.    Tasks Shown: All  
  30.    ->  Task  
  31.          Node: host=172.24.211.232 port=1921 dbname=postgres  
  32.          ->  WindowAgg  (cost=2.51..2.53 rows=1 width=105)  
  33.                Output: row_number() OVER (?), aid, bid, abalance, filler  
  34.                ->  Sort  (cost=2.51..2.51 rows=1 width=97)  
  35.                      Output: aid, bid, abalance, filler  
  36.                      Sort Key: pgbench_accounts.bid  
  37.                      ->  Index Scan using pgbench_accounts_pkey_106819 on public.pgbench_accounts_106819 pgbench_accounts  (cost=0.28..2.50 rows=1 width=97)  
  38.                            Output: aid, bid, abalance, filler  
  39.                            Index Cond: (pgbench_accounts.aid = 1)  
  40. (14 rows)

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

greenplum window函数的支持

支持任意姿势的window调用

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

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

  1. 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 ;  
  2.                                                                    QUERY PLAN                                                                     
  3. ------------------------------------------------------------------------------------------------------------------------------------------------  
  4.  Gather Motion 33:1  (slice3; segments: 33)  (cost=3017582.83..3192792.97 rows=10012008 width=12)  
  5.    ->  Subquery Scan coplan  (cost=3017582.83..3192792.97 rows=303395 width=12)  
  6.          ->  Window  (cost=3017582.83..3092672.89 rows=303395 width=12)  
  7.                Partition By: coplan.c1  
  8.                Order By: coplan.id  
  9.                ->  Sort  (cost=3017582.83..3042612.85 rows=303395 width=12)  
  10.                      Sort Key: coplan.c1, coplan.id  
  11.                      // 以下在citus中用临时表代替  
  12. 		     ->  Redistribute Motion 33:33  (slice2; segments: 33)  (cost=1477974.88..1853425.18 rows=303395 width=12)  
  13.                            Hash Key: coplan.c1  
  14.                            ->  Subquery Scan coplan  (cost=1477974.88..1653185.02 rows=303395 width=12)  
  15.                                  ->  Window  (cost=1477974.88..1553064.94 rows=303395 width=12)  
  16.                                        Partition By: t.c2  
  17.                                        Order By: t.c1  
  18.                                        ->  Sort  (cost=1477974.88..1503004.90 rows=303395 width=12)  
  19.                                              Sort Key: t.c2, t.c1  
  20.                                              // 以下在citus中用临时表代替  
  21. 					     ->  Redistribute Motion 33:33  (slice1; segments: 33)  (cost=0.00..313817.24 rows=303395 width=12)  
  22.                                                    Hash Key: t.c2  
  23.                                                    ->  Seq Scan on t  (cost=0.00..113577.08 rows=303395 width=12)  
  24.  Optimizer status: legacy query optimizer  
  25. (19 rows)

小结

citus 7.5的版本，对窗口函数的支持仅如下条件（二选一，满足即可调用）：

本质上：目前（citus 7.5）window函数不支持跨shard操作。

1、partition by 必须是分布键。

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

还是回到那句话，write in SQL, thinking in mapreduce。懂了这句话的精髓，你才可以使用citus用作分析场景，否则先乖乖的用来做TP为主的业务。

（比如上面不支持的场景，一条SQL拆成多条，最笨的方法，先创建一个临时表（按PARTITION BY分布），然后再跑window函数就支持了，多走几步即可。）

让CITUS支持本身不支持的SQL语法的最愉快的方法：

把结构导入Greenplum，看Greenplum的执行计划，将Redistribute Motion 的部分，在citus里面用临时表实现。 你照这个做，绝对可以让citus跑OLAP很欢快。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26692020/viewspace-3165770/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： NoSQL中的“BASE”特性
下一篇： 运维自动化之报表模块rrdtool（下）实践
请登录后发表评论 登录
全部评论
gaokeke123
大家好

注册时间：2013-08-14

  • 博文量
    1432
  • 访问量
    31645

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593