AntDB 数据库分布式功能-FQS增强4

窗口函数

# 测试用表和数据请参加附录
# 单节点（绑定变量的)：窗口函数
antdb=# prepare s_window_single(int) as SELECT *,row_number() over (order by province ) as idx FROM fqs_window_test WHERE id = $1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_window_single(1);
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: fqs_window_test.id, fqs_window_test.province, fqs_window_test.city, (row_number() OVER (?))
   Node expr: $1
   Remote query: SELECT id, province, city, row_number() OVER (ORDER BY province) AS idx FROM public.fqs_window_test fqs_window_test WHERE (id = $1)
(4 rows)


# 单节点（静态常量的)：窗口函数
antdb=# EXPLAIN (verbose) SELECT *,row_number() over (order by province ) as idx FROM fqs_window_test WHERE id = 1;
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: fqs_window_test.id, fqs_window_test.province, fqs_window_test.city, (row_number() OVER (?))
   Node/s: DN2
   Remote query: SELECT id, province, city, row_number() OVER (ORDER BY province) AS idx FROM public.fqs_window_test fqs_window_test WHERE (id = 1)
(4 rows)
复制

delete 包含分片键

# 测试用表和数据请参加附录
# delete 里面包含分片键，且是动态绑定变量的
antdb=# prepare s12(int, int, int) as delete FROM fqs_bmsql_new_order WHERE no_w_id = $1 and no_d_id = $2 and no_o_id = $3;
PREPARE
antdb=# EXPLAIN (verbose) execute s12(1, 1, 1);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Node expr: $1
   Remote query: DELETE FROM public.fqs_bmsql_new_order fqs_bmsql_new_order WHERE ((no_w_id = $1) AND (no_d_id = $2) AND (no_o_id = $3))
(3 rows)


# delete 里面包含分片键，且是静态常量的
antdb=# EXPLAIN (verbose) delete FROM fqs_bmsql_new_order WHERE no_w_id = 5 and no_d_id = 1 and no_o_id = 2;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Primary node/s: DN0
   Node/s: DN0
   Remote query: DELETE FROM public.fqs_bmsql_new_order fqs_bmsql_new_order WHERE ((no_w_id = 5) AND (no_d_id = 1) AND (no_o_id = 2))
(4 rows)
复制