FQS增强
更新时间：2024-07-01 14:39:47
FQS 简介
数据库 FQS（Fast Query Shipping）是一种高效的查询性能优化技术，它通过在执行计划开始前分析输入的 SQL，快速检测涉及的数据节点，精确地将查询请求快速下推至远程节点，显著减少了查询优化和查询执行的时间，从而显著提升了查询性能。FQS 特别适用于分布式数据库系统TP类的业务，通过深入分析查询特性和数据分布，为改进查询性能提供了有力支持。为了充分发挥 FQS 的优势，需要对数据库结构、数据分布以及查询需求有深入的理解，并配合细致的性能分析和调优，以制定最佳的查询执行策略。
FQS 优化
AntDB 数据库分布式版本做了 FQS 优化。开启 enable_fast_query_shipping 参数，让一些简单的 SQL 语句原本不走 FQS 流程的可以走 FQS 流程，快速的生成执行计划（remotequery），来减少执行计划生成的时间，交由 DN 节点去执行，以提高 SQL 执行的效率。
生成 FQS 执行计划的总体原则是：对 DML SQL 语句,语句在执行时,不需要在 DN 节点之间交换数据的简单 SQL 可以生成 FQS 执行计划。支持 Hash 分片、Mod 取模分片这 2 种分片方式。
FQS 相关参数配置
| 参数名称 | 参数说明 | 参数类型 | 默认值 | 修改后是否需要重启 |
|---|---|---|---|---|
| force_cluster_custom_plan | 该参数设置为 on 时，cluster_gather 执行计划生成始终走 custom_plan 的模式;参数 force_cluster_custom_plan 设置为 off， 则无其他影响 | bool | off | 否 |
| enable_fast_query_shipping | 该参数设置为 on 时，让一些简单的SQL语句原本不走 FQS 流程的可以走 FQS 流程，快速的生成执行计划（remotequery），来减少执行计划生成的时间，交由DN节点去执行，以提高SQL执行的效率 on：开启 FQS 功能 off：关闭 FQS 功能 | bool | on | 否 |
| enable_pushdown_art | 开启这个参数，针对复制表的查询语句，语句中不包含可变函数的，也没有 for update 的，走 FQS 逻辑 | bool | off | 否 |
举例说明：
## 参数 force_cluster_custom_plan 示例如下：
# 设置 force_cluster_custom_plan 为 on， 此时 cluster plan 的语句不再缓存， 每次都按照参数重新生成
# 设置 force_cluster_custom_plan 为 off， 多执行几次会产生缓存
-- 创建测试表格：
antdb=# CREATE TABLE t_hash (a int,b int,c char(10),d timestamp without time zone) DISTRIBUTE BY HASH(a);
CREATE TABLE
-- 设置 pgxc_enable_remote_query 和 enable_fast_query_shipping 为false，这里只是为了模拟下面语句不走fqs，且执行计划为cluster
antdb=# set pgxc_enable_remote_query TO false;
SET
antdb=# set enable_fast_query_shipping TO false;
SET
antdb=# prepare s1(int) as SELECT * FROM t_hash WHERE a = $1;
PREPARE
antdb=# set force_cluster_custom_plan TO on;
SET
-- Filter: (t_hash.a = 1) 代表 cluster plan 语句没有缓存
-- explain verbose execute s1(1) 即便执行多次，也不会产生缓存
antdb=# explain verbose execute s1(1);
QUERY PLAN
---------------------------------------------------------------------
Cluster Gather (cost=0.00..22.10 rows=2 width=60)
Remote node: 16389
-> Seq Scan on public.t_hash (cost=0.00..21.50 rows=2 width=60)
Output: a, b, c, d
Filter: (t_hash.a = 1)
Remote node: 16389
(6 rows)
-- explain verbose execute s1(1) 多执行几次后才会产生缓存
-- Filter: (t_hash.a = $1) 代表已经存在缓存，已经是 generic plan
antdb=# set enable_fast_query_shipping TO false;
SET
antdb=# explain verbose execute s1(1);
QUERY PLAN
---------------------------------------------------------------------
Cluster Gather (cost=0.00..23.30 rows=6 width=60)
Remote node: 16387,16388,16389
-> Seq Scan on public.t_hash (cost=0.00..21.50 rows=2 width=60)
Output: a, b, c, d
Filter: (t_hash.a = $1)
Remote node: 16387,16388,16389
(6 rows)
-- 恢复参数默认设置：
antdb=# reset force_cluster_custom_plan;
RESET
antdb=# reset pgxc_enable_remote_query;
RESET
antdb=# reset enable_fast_query_shipping;
RESET
## 参数 enable_fast_query_shipping 示例如下：
## off：关闭 FQS 功能 on：开启 FQS 功能
## 测试用表和数据请参加附录
antdb=# prepare s3(int, int) as SELECT no_o_id FROM fqs_bmsql_new_order WHERE no_w_id = $1 and no_d_id = $2 order by no_o_id asc;
PREPARE
antdb=# set enable_fast_query_shipping = off;
SET
antdb=# EXPLAIN (verbose) execute s3(1, 1);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Cluster Gather (cost=40.61..40.91 rows=1 width=4)
Remote node: 16389
-> Sort (cost=40.61..40.61 rows=1 width=4)
Output: no_o_id
Sort Key: fqs_bmsql_new_order.no_o_id
-> Seq Scan on public.fqs_bmsql_new_order (cost=0.00..40.60 rows=1 width=4)
Output: no_o_id
Filter: ((fqs_bmsql_new_order.no_w_id = 1) AND (fqs_bmsql_new_order.no_d_id = 1))
Remote node: 16389
(9 rows)
antdb=# set enable_fast_query_shipping = on;
SET
antdb=# EXPLAIN (verbose) execute s3(1, 1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_new_order.no_o_id
Node expr: $1
Remote query: SELECT no_o_id FROM public.fqs_bmsql_new_order fqs_bmsql_new_order WHERE ((no_w_id = $1) AND (no_d_id = $2)) ORDER BY no_o_id
(4 rows)
## 参数 enable_pushdown_art 示例如下：
## 针对复制表的复杂查询语句， off：关闭 FQS 功能 on：开启 FQS 功能
antdb=# create table t_push1(id int, unique1 int) distribute by replication;
(SELECT 1 FROM t_push3 as j3
WHERE j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred)
and t1.unique1 < 1;
drop table t_push1;
drop table t_push2;
drop table t_push3;CREATE TABLE
antdb=# create table t_push2(id int, hundred int) distribute by replication;
CREATE TABLE
antdb=# create table t_push3(id int, unique1 int, tenthous int) distribute by replication;
CREATE TABLE
antdb=# insert into t_push1 values(1,1),(2,2);
INSERT 0 6
antdb=# insert into t_push2 values(1,1),(2,2);
INSERT 0 6
antdb=# insert into t_push3 values(1,1,1),(2,2,2);
INSERT 0 6
antdb=#
antdb=# set enable_pushdown_art = off;
SET
antdb=# explain (verbose, costs off, nodes off)
antdb-# SELECT t1.unique1, t2.hundred
antdb-# FROM t_push1 AS t1, t_push2 as t2
antdb-# WHERE exists (SELECT 1 FROM t_push3 as j3
antdb(# WHERE j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred)
antdb-# and t1.unique1 < 1;
QUERY PLAN
-----------------------------------------------------------------------
Cluster Gather
Remote node: 16388
-> Hash Join
Output: t1.unique1, t2.hundred
Hash Cond: (t2.hundred = j3.tenthous)
-> Seq Scan on public.t_push2 t2
Output: t2.id, t2.hundred
Remote node: 16387,16388,16389
-> Hash
Output: t1.unique1, j3.tenthous
-> Hash Join
Output: t1.unique1, j3.tenthous
Hash Cond: (t1.unique1 = j3.unique1)
-> Seq Scan on public.t_push1 t1
Output: t1.id, t1.unique1
Filter: (t1.unique1 < 1)
Remote node: 16387,16388,16389
-> Hash
Output: j3.unique1, j3.tenthous
-> HashAggregate
Output: j3.unique1, j3.tenthous
Group Key: j3.unique1, j3.tenthous
-> Seq Scan on public.t_push3 j3
Output: j3.unique1, j3.tenthous
Remote node: 16387,16388,16389
(25 rows)
antdb=#
antdb=# set enable_pushdown_art = on;
SET
antdb=# explain (verbose, costs off, nodes off)
antdb-# SELECT t1.unique1, t2.hundred
antdb-# FROM t_push1 AS t1, t_push2 as t2
antdb-# WHERE exists (SELECT 1 FROM t_push3 as j3
antdb(# WHERE j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred)
antdb-# and t1.unique1 < 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t1.unique1, t2.hundred
Remote query: SELECT t1.unique1, t2.hundred FROM public.t_push1 t1, public.t_push2 t2 WHERE ((EXISTS (SELECT 1 FROM public.t_push3 j3 WHERE ((j3.unique1 = t1.unique1) AND (j3.tenthous =
t2.hundred)))) AND (t1.unique1 < 1))
(3 rows)
antdb=#
antdb=# drop table t_push1;
DROP TABLE
antdb=# drop table t_push2;
DROP TABLE
antdb=# drop table t_push3;
DROP TABLE
FQS 支持的场景
单表支持 FQS 的场景
简单单表的 SQL 语句，可确定最终只会在一个 DN 上执行的情况：
- SQL 语句中分片键包含一个=变量的支持。比如：变量 a=$1 这种支持，a=$1+1 这种不支持。可确定最终只会在一个 datanode 上执行,根据 SQL 语句的语法树,构造 DN 选择条件,在运行时确定在哪个 DN 上执行。
- SQL 语句中分片键包含一个=常量的，比如：变量 a=1 这种，可确定最终只会在一个 DN 上执行,根据 SQL 语句的语法树,构造 DN 选择条件,在运行时确定在哪个 DN 上执行。
排序
# 测试用表和数据请参加附录
# 单节点（绑定变量的)：排序
antdb=# prepare s3(int, int) as SELECT no_o_id FROM fqs_bmsql_new_order WHERE no_w_id = $1 and no_d_id = $2 order by no_o_id asc;
PREPARE
antdb=# EXPLAIN (verbose) execute s3(1, 1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_new_order.no_o_id
Node expr: $1
Remote query: SELECT no_o_id FROM public.fqs_bmsql_new_order fqs_bmsql_new_order WHERE ((no_w_id = $1) AND (no_d_id = $2)) ORDER BY no_o_id
(4 rows)
# 单节点（静态常量的)：排序
antdb=# EXPLAIN (verbose) SELECT no_o_id FROM fqs_bmsql_new_order WHERE no_w_id = 3 and no_d_id = 2 order by no_o_id asc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_new_order.no_o_id
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT no_o_id FROM public.fqs_bmsql_new_order fqs_bmsql_new_order WHERE ((no_w_id = 3) AND (no_d_id = 2)) ORDER BY no_o_id
(5 rows)
limit offsert
# 测试用表和数据请参加附录
# 单节点（绑定变量)：limit offsert
antdb=# prepare s_limitoffsert(int) as SELECT * FROM fqs_bmsql_order_line WHERE ol_w_id = $1 limit 5 OFFSET 5;
PREPARE
antdb=# EXPLAIN (verbose) execute s_limitoffsert(2);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_order_line.ol_w_id, fqs_bmsql_order_line.ol_d_id, fqs_bmsql_order_line.ol_o_id, fqs_bmsql_order_line.ol_number, fqs_bmsql_order_line.ol_i_id, fqs_bmsql_order_line.ol_d
elivery_d, fqs_bmsql_order_line.ol_amount, fqs_bmsql_order_line.ol_supply_w_id, fqs_bmsql_order_line.ol_quantity, fqs_bmsql_order_line.ol_dist_info
Node expr: $1
Remote query: SELECT ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d, ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info FROM public.fqs_bmsql_order_line fqs_bmsql_order_
line WHERE (ol_w_id = $1) OFFSET 5 LIMIT 5
(4 rows)
# 单节点（静态常量)：limit offsert
antdb=# EXPLAIN (verbose) SELECT * FROM fqs_bmsql_order_line WHERE ol_w_id = 5 limit 5 OFFSET 5;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_order_line.ol_w_id, fqs_bmsql_order_line.ol_d_id, fqs_bmsql_order_line.ol_o_id, fqs_bmsql_order_line.ol_number, fqs_bmsql_order_line.ol_i_id, fqs_bmsql_order_line.ol_d
elivery_d, fqs_bmsql_order_line.ol_amount, fqs_bmsql_order_line.ol_supply_w_id, fqs_bmsql_order_line.ol_quantity, fqs_bmsql_order_line.ol_dist_info
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d, ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info FROM public.fqs_bmsql_order_line fqs_bmsql_order_
line WHERE (ol_w_id = 5) OFFSET 5 LIMIT 5
(5 rows)
limit count
# 测试用表和数据请参加附录
# 单节点（绑定变量的)：limit count
antdb=# prepare s_limit(int) as SELECT * FROM fqs_bmsql_order_line WHERE ol_w_id = $1 limit 2;
PREPARE
antdb=# EXPLAIN (verbose) execute s_limit(2);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_order_line.ol_w_id, fqs_bmsql_order_line.ol_d_id, fqs_bmsql_order_line.ol_o_id, fqs_bmsql_order_line.ol_number, fqs_bmsql_order_line.ol_i_id, fqs_bmsql_order_line.ol_d
elivery_d, fqs_bmsql_order_line.ol_amount, fqs_bmsql_order_line.ol_supply_w_id, fqs_bmsql_order_line.ol_quantity, fqs_bmsql_order_line.ol_dist_info
Node expr: $1
Remote query: SELECT ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d, ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info FROM public.fqs_bmsql_order_line fqs_bmsql_order_
line WHERE (ol_w_id = $1) LIMIT 2
(4 rows)
# 单节点（静态常量的)：limit count
antdb=# EXPLAIN (verbose) SELECT * FROM fqs_bmsql_order_line WHERE ol_w_id = 3 limit 2;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_order_line.ol_w_id, fqs_bmsql_order_line.ol_d_id, fqs_bmsql_order_line.ol_o_id, fqs_bmsql_order_line.ol_number, fqs_bmsql_order_line.ol_i_id, fqs_bmsql_order_line.ol_d
elivery_d, fqs_bmsql_order_line.ol_amount, fqs_bmsql_order_line.ol_supply_w_id, fqs_bmsql_order_line.ol_quantity, fqs_bmsql_order_line.ol_dist_info
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT ol_w_id, ol_d_id, ol_o_id, ol_number, ol_i_id, ol_delivery_d, ol_amount, ol_supply_w_id, ol_quantity, ol_dist_info FROM public.fqs_bmsql_order_line fqs_bmsql_order_
line WHERE (ol_w_id = 3) LIMIT 2
(5 rows)
聚合函数
# 测试用表和数据请参加附录
# 单节点（绑定变量的)：聚合函数
antdb=# prepare s9(int, int, int) as SELECT sum(ol_amount) as sum_ol_amount FROM fqs_bmsql_order_line WHERE ol_w_id = $1 and ol_d_id = $2 and ol_o_id = $3;
PREPARE
antdb=# EXPLAIN (verbose) execute s9(1, 1, 1);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_bmsql_order_line.ol_amount))
Node expr: $1
Remote query: SELECT sum(ol_amount) AS sum_ol_amount FROM public.fqs_bmsql_order_line fqs_bmsql_order_line WHERE ((ol_w_id = $1) AND (ol_d_id = $2) AND (ol_o_id = $3))
(4 rows)
# 单节点（静态常量的)：聚合函数
antdb=# EXPLAIN (verbose) SELECT sum(ol_amount) as sum_ol_amount FROM fqs_bmsql_order_line WHERE ol_w_id = 3 and ol_d_id = 1 and ol_o_id = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_bmsql_order_line.ol_amount))
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT sum(ol_amount) AS sum_ol_amount FROM public.fqs_bmsql_order_line fqs_bmsql_order_line WHERE ((ol_w_id = 3) AND (ol_d_id = 1) AND (ol_o_id = 1))
(5 rows)
窗口函数
# 测试用表和数据请参加附录
# 单节点（绑定变量的)：窗口函数
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)
update 包含分片键
# 测试用表和数据请参加附录
# update 里面包含分片键，且是动态绑定变量的
antdb=# prepare s15(int, int, int, int) as update fqs_bmsql_oorder set o_carrier_id = $1 WHERE o_w_id = $2 and o_d_id = $3 and o_id = $4;
PREPARE
antdb=# EXPLAIN (verbose) execute s15(1, 1, 1, 1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: ($1)
Node expr: $2
Remote query: UPDATE public.fqs_bmsql_oorder fqs_bmsql_oorder SET o_carrier_id = $1 WHERE ((o_w_id = $2) AND (o_d_id = $3) AND (o_id = $4))
(4 rows)
# update里面包含分片键，且是静态常量的
antdb=# EXPLAIN (verbose) update fqs_bmsql_oorder set o_carrier_id = 3 WHERE o_w_id = 3 and o_d_id = 4 and o_id = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: 3
Primary node/s: DN0
Node/s: DN0
Remote query: UPDATE public.fqs_bmsql_oorder fqs_bmsql_oorder SET o_carrier_id = 3 WHERE ((o_w_id = 3) AND (o_d_id = 4) AND (o_id = 1))
(5 rows)
for update 包含分片键
# 测试用表和数据请参加附录
# for update 里面包含分片键，且是动态绑定变量的
antdb=# prepare s1(int, int) as SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = $1 and d_id = $2 for update;
PREPARE
antdb=# EXPLAIN verbose execute s1(1, 1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: bmsql_district.d_tax, bmsql_district.d_next_o_id
Node expr: $1
Remote query: SELECT d_tax, d_next_o_id FROM public.bmsql_district bmsql_district WHERE ((d_w_id = $1) AND (d_id = $2)) FOR UPDATE OF bmsql_district
(4 rows)
# for update里面包含分片键，且是静态常量的
antdb=# EXPLAIN (verbose) SELECT d_tax, d_next_o_id FROM fqs_bmsql_district WHERE d_w_id = 3 and d_id = 2 for update;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_district.d_tax, fqs_bmsql_district.d_next_o_id
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT d_tax, d_next_o_id FROM public.fqs_bmsql_district fqs_bmsql_district WHERE ((d_w_id = 3) AND (d_id = 2)) FOR UPDATE OF fqs_bmsql_district
(5 rows)
having 且执行节点 group 里面没分布列
# 测试用表和数据请参加附录
# 单节点（绑定变量的)：having且执行节点group里面没分布列的
antdb=# prepare s_having_single(int) as SELECT sum(id) FROM fqs_window_test WHERE id = $1 GROUP BY province HAVING count(province) < 2 ;
PREPARE
antdb=# EXPLAIN (verbose) execute s_having_single(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_window_test.id))
Node expr: $1
Remote query: SELECT sum(id) AS sum FROM public.fqs_window_test fqs_window_test WHERE (id = $1) GROUP BY province HAVING (count(province) < 2)
(4 rows)
# 单节点（静态常量的)：having且执行节点group里面没分布列的
antdb=# EXPLAIN (verbose) SELECT sum(id) FROM fqs_window_test WHERE id = 1 GROUP BY province HAVING count(province) < 2 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_window_test.id))
Node/s: DN2
Remote query: SELECT sum(id) AS sum FROM public.fqs_window_test fqs_window_test WHERE (id = 1) GROUP BY province HAVING (count(province) < 2)
(4 rows)
groupClause 非空且执行节点 group 里面没分布列
# 测试用表和数据请参加附录
# 单节点（绑定变量的)： groupClause 非空且执行节点group里面没分布列的
antdb=# prepare s_group_single(int) as SELECT sum(id) FROM fqs_window_test WHERE id = $1 GROUP BY province;
PREPARE
antdb=# EXPLAIN (verbose) execute s_group_single(1);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_window_test.id))
Node expr: $1
Remote query: SELECT sum(id) AS sum FROM public.fqs_window_test fqs_window_test WHERE (id = $1) GROUP BY province
(4 rows)
# 单节点（静态常量的)： groupClause 非空且执行节点group里面没分布列的
antdb=# EXPLAIN (verbose) SELECT sum(id) FROM fqs_window_test WHERE id = 1 GROUP BY province;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_window_test.id))
Node/s: DN2
Remote query: SELECT sum(id) AS sum FROM public.fqs_window_test fqs_window_test WHERE (id = 1) GROUP BY province
(4 rows)
distinctClause 且 distinctClause 里面没有分布列
# 测试用表和数据请参加附录
# 单节点（绑定变量的)：distinctClause且distinctClause里面没有分布列的
antdb=# prepare s_distinct_single(int) as SELECT distinct province FROM fqs_window_test WHERE id = $1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_distinct_single(1);
QUERY PLAN
------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_window_test.province
Node expr: $1
Remote query: SELECT DISTINCT province FROM public.fqs_window_test fqs_window_test WHERE (id = $1)
(4 rows)
# 单节点（静态常量的)：distinctClause且distinctClause里面没有分布列的
antdb=# EXPLAIN (verbose) SELECT distinct province 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.province
Node/s: DN2
Remote query: SELECT DISTINCT province FROM public.fqs_window_test fqs_window_test WHERE (id = 1)
(4 rows)
普通子查询
# 测试用表和数据请参加附录
# 带动态参数的普通子查询
antdb=# prepare s_common_subquery(int) as SELECT * FROM (SELECT * FROM fqs_window_test WHERE id = $1) a;
PREPARE
antdb=# EXPLAIN (verbose) execute s_common_subquery(2);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: a.id, a.province, a.city
Node expr: $1
Remote query: SELECT id, province, city FROM (SELECT fqs_window_test.id, fqs_window_test.province, fqs_window_test.city FROM public.fqs_window_test fqs_window_test WHERE (fqs_window_tes
t.id = $1)) a
(4 rows)
# 带静态常量的普通子查询
antdb=# EXPLAIN (verbose) SELECT * FROM (SELECT * FROM fqs_window_test WHERE id = 2) a;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: a.id, a.province, a.city
Node/s: DN1
Remote query: SELECT id, province, city FROM (SELECT fqs_window_test.id, fqs_window_test.province, fqs_window_test.city FROM public.fqs_window_test fqs_window_test WHERE (fqs_window_tes
t.id = 2)) a
(4 rows)
标量子查询
# 测试用表和数据请参加附录
# 带动态参数的标量子查询
antdb=# prepare s_scalar_subquery(int) as SELECT * FROM (SELECT province FROM fqs_window_test WHERE id = $1) a;
PREPARE
antdb=# EXPLAIN (verbose) execute s_scalar_subquery(2);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: a.province
Node expr: $1
Remote query: SELECT province FROM (SELECT fqs_window_test.province FROM public.fqs_window_test fqs_window_test WHERE (fqs_window_test.id = $1)) a
(4 rows)
# 带静态常量的标量子查询
antdb=# EXPLAIN (verbose) SELECT * FROM (SELECT province FROM fqs_window_test WHERE id = 2) a;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: a.province
Node/s: DN1
Remote query: SELECT province FROM (SELECT fqs_window_test.province FROM public.fqs_window_test fqs_window_test WHERE (fqs_window_test.id = 2)) a
(4 rows)
包含 sqlvalueFunc
# 测试用表和数据请参加附录
# 包含 sqlvalueFunc（例如:CURRENT_TIMESTAMP）的函数支持 FQS，分片键是动态绑定变量的
antdb=# prepare s_sqlvalueFunc(int) as INSERT INTO fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, 1, 1, 1, CURRENT_TIMESTAMP);
PREPARE
antdb=# EXPLAIN (verbose) execute s_sqlvalueFunc(2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: ($1), 1, 1, 1, ((CURRENT_TIMESTAMP)::timestamp without time zone)
Node expr: $1
Remote query: INSERT INTO public.fqs_pgbench_history AS fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, 1, 1, 1, CURRENT_TIMESTAMP)
(4 rows)
# 包含sqlvalueFunc（例如:CURRENT_TIMESTAMP）的函数支持FQS，分片键是静态常量的
antdb=# EXPLAIN (verbose) INSERT INTO fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES (2, 1, 1, 1, CURRENT_TIMESTAMP);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: 2, 1, 1, 1, ((CURRENT_TIMESTAMP)::timestamp without time zone)
Node expr: 2
Remote query: INSERT INTO public.fqs_pgbench_history AS fqs_pgbench_history (tid, bid, aid, delta, mtime) VALUES (2, 1, 1, 1, CURRENT_TIMESTAMP)
(4 rows)
按 mod 分布的表支持单表查询，条件中包含分片键
# 测试用表和数据请参加附录
# 按 mod 分布的表支持单表查询，条件中包含分片键且是动态绑定变量的，支持排序和没有排序的（排序键随意）
antdb=# prepare s_mod(int) as SELECT * FROM fqs_test_mod WHERE id=$1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_mod(1);
QUERY PLAN
---------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_test_mod.id, fqs_test_mod.name
Node expr: $1
Remote query: SELECT id, name FROM public.fqs_test_mod fqs_test_mod WHERE (id = $1)
(4 rows)
# 按 mod 分布的表支持单表查询，条件中包含分片键且是静态常量的，支持排序和没有排序的（排序键随意）
antdb=# EXPLAIN (verbose) SELECT * FROM fqs_test_mod WHERE id=1;
QUERY PLAN
--------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_test_mod.id, fqs_test_mod.name
Node/s: DN1
Remote query: SELECT id, name FROM public.fqs_test_mod fqs_test_mod WHERE (id = 1)
(4 rows)
复制表支持单表查询
# 测试用表和数据请参加附录
# 复制表支持单表查询，条件中包含分片键且是动态绑定变量的，支持排序和没有排序的（排序键随意）
antdb=# prepare s_fqs_test_repl(int) as SELECT * FROM fqs_test_repl WHERE id=$1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_fqs_test_repl(1);
QUERY PLAN
-----------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_test_repl.id, fqs_test_repl.name
Node/s: DN1
Remote query: SELECT id, name FROM public.fqs_test_repl fqs_test_repl WHERE (id = $1)
(4 rows)
# 复制表支持单表查询，条件中包含分片键且是静态常量的，支持排序和没有排序的（排序键随意）
antdb=# EXPLAIN (verbose) SELECT * FROM fqs_test_repl WHERE id=1;
QUERY PLAN
----------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_test_repl.id, fqs_test_repl.name
Node/s: DN1
Remote query: SELECT id, name FROM public.fqs_test_repl fqs_test_repl WHERE (id = 1)
(4 rows)
随机分布的表支持单表查询
# 测试用表和数据请参加附录
# 随机分布的表支持单表查询，条件中包含分片键且是动态绑定变量的，没有排序的
antdb=# prepare s_fqs_test_random(int) as SELECT * FROM fqs_test_random WHERE id=$1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_fqs_test_random(1);
QUERY PLAN
---------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_test_random.id, fqs_test_random.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT id, name FROM public.fqs_test_random fqs_test_random WHERE (id = $1)
(5 rows)
# 随机分布的表支持单表查询，条件中包含分片键且是静态常量的，没有排序的
antdb=# EXPLAIN (verbose) SELECT * FROM fqs_test_random WHERE id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_test_random.id, fqs_test_random.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT id, name FROM public.fqs_test_random fqs_test_random WHERE (id = 1)
(5 rows)
单表其它情况
# 测试用表和数据请参加附录
# 分片键是 int/char 类型，且是 hash 分布的或取模分布的，条件中包含分片键且含有 or 多个条件的，且是静态常量的，
# 可以带有聚会函数 sum、count,或者DISTINCT，或者group by非分片键，group里面有having 的，并且查询的数据都在同一个DN节点的
# char 类型
# 下述数据必须保证在一个节点上，例如查看 fqs_t_char 表，任意选择一个 node 上的两个数据 name10 和 name13 进行测试：
antdb=# SELECT *,adb_node_oid() as node FROM fqs_t_char limit 10;
id | iid | name | node
----+------+--------+-------
6 | 600 | name6 | 16388
7 | 700 | name7 | 16388
10 | 1000 | name10 | 16388
11 | 1100 | name11 | 16388
13 | 1300 | name13 | 16388
19 | 1900 | name19 | 16388
21 | 2100 | name21 | 16388
23 | 2300 | name23 | 16388
25 | 2500 | name25 | 16388
27 | 2700 | name27 | 16388
(10 rows)
antdb=# EXPLAIN (verbose) SELECT sum(id) FROM fqs_t_char WHERE name='name10' or name='name13';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_t_char.id))
Node/s: DN1
Remote query: SELECT sum(id) AS sum FROM public.fqs_t_char fqs_t_char WHERE (((name)::text = 'name10'::text) OR ((name)::text = 'name13'::text))
(4 rows)
antdb=# EXPLAIN (verbose) SELECT DISTINCT(id) FROM fqs_t_char WHERE name='name10' or name='name13';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.id
Node/s: DN1
Remote query: SELECT DISTINCT id FROM public.fqs_t_char fqs_t_char WHERE (((name)::text = 'name10'::text) OR ((name)::text = 'name13'::text))
(4 rows)
antdb=# EXPLAIN (verbose) SELECT iid FROM fqs_t_char WHERE name ='name10' or name='name13' group by iid;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.iid
Node/s: DN1
Remote query: SELECT iid FROM public.fqs_t_char fqs_t_char WHERE (((name)::text = 'name10'::text) OR ((name)::text = 'name13'::text)) GROUP BY iid
(4 rows)
antdb=# EXPLAIN (verbose) SELECT iid,count(iid) FROM fqs_t_char WHERE name ='name11' or name='name13' group by iid;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.iid, (count(fqs_t_char.iid))
Node/s: DN1
Remote query: SELECT iid, count(iid) AS count FROM public.fqs_t_char fqs_t_char WHERE (((name)::text = 'name11'::text) OR ((name)::text = 'name13'::text)) GROUP BY iid
(4 rows)
antdb=# EXPLAIN (verbose) SELECT iid,count(iid) FROM fqs_t_char WHERE name ='name11' or name='name13' group by iid having sum(id)>100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.iid, (count(fqs_t_char.iid))
Node/s: DN1
Remote query: SELECT iid, count(iid) AS count FROM public.fqs_t_char fqs_t_char WHERE (((name)::text = 'name11'::text) OR ((name)::text = 'name13'::text)) GROUP BY iid HAVING (sum(id) >
100)
(4 rows)
# int 类型
antdb=# EXPLAIN (verbose) SELECT count(iid) FROM fqs_t_int WHERE id=808 or id=809;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (count(fqs_t_int.iid))
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT count(iid) AS count FROM public.fqs_t_int fqs_t_int WHERE ((id = 808) OR (id = 809))
(5 rows)
antdb=# EXPLAIN (verbose) SELECT DISTINCT(iid) FROM fqs_t_int WHERE id=808 or id=809;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_int.iid
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT DISTINCT iid FROM public.fqs_t_int fqs_t_int WHERE ((id = 808) OR (id = 809))
(5 rows)
antdb=# EXPLAIN (verbose) SELECT iid FROM fqs_t_char WHERE name ='name11' or name='name13' group by iid;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.iid
Node/s: DN1
Remote query: SELECT iid FROM public.fqs_t_char fqs_t_char WHERE (((name)::text = 'name11'::text) OR ((name)::text = 'name13'::text)) GROUP BY iid
(4 rows)
# 分片键是 int/char 类型，且是hash分布的或取模分布的，条件中包含分片键且含有in多个条件的，且是静态常量的，
# 可以带有聚会函数 sum、count,或者 DISTINCT，或者 group by 非分片键，group 里面有 having 的，并且查询的数据都在同一个 DN 节点的
# char 类型：
antdb=# EXPLAIN (verbose) SELECT sum(id) FROM fqs_t_char WHERE name in ('name10','name13');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (sum(fqs_t_char.id))
Node/s: DN1
Remote query: SELECT sum(id) AS sum FROM public.fqs_t_char fqs_t_char WHERE ((name)::text = ANY ((ARRAY['name10'::character varying, 'name13'::character varying])::text[]))
(4 rows)
antdb=# EXPLAIN (verbose) SELECT DISTINCT(id) FROM fqs_t_char WHERE name in ('name10','name13');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.id
Node/s: DN1
Remote query: SELECT DISTINCT id FROM public.fqs_t_char fqs_t_char WHERE ((name)::text = ANY ((ARRAY['name10'::character varying, 'name13'::character varying])::text[]))
(4 rows)
antdb=# EXPLAIN (verbose) SELECT iid FROM fqs_t_char WHERE name in ('name10','name13') group by iid;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.iid
Node/s: DN1
Remote query: SELECT iid FROM public.fqs_t_char fqs_t_char WHERE ((name)::text = ANY ((ARRAY['name10'::character varying, 'name13'::character varying])::text[])) GROUP BY iid
(4 rows)
antdb=# EXPLAIN (verbose) SELECT iid,count(iid) FROM fqs_t_char WHERE name in ('name10','name13') group by iid;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.iid, (count(fqs_t_char.iid))
Node/s: DN1
Remote query: SELECT iid, count(iid) AS count FROM public.fqs_t_char fqs_t_char WHERE ((name)::text = ANY ((ARRAY['name10'::character varying, 'name13'::character varying])::text[])) GR
OUP BY iid
(4 rows)
antdb=# EXPLAIN (verbose) SELECT iid,count(iid) FROM fqs_t_char WHERE name in ('name10','name13') group by iid having sum(id)>100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_char.iid, (count(fqs_t_char.iid))
Node/s: DN1
Remote query: SELECT iid, count(iid) AS count FROM public.fqs_t_char fqs_t_char WHERE ((name)::text = ANY ((ARRAY['name10'::character varying, 'name13'::character varying])::text[])) GR
OUP BY iid HAVING (sum(id) > 100)
(4 rows)
# int类型：
antdb=# EXPLAIN (verbose) SELECT count(iid) FROM fqs_t_int WHERE id in (808,809);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: (count(fqs_t_int.iid))
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT count(iid) AS count FROM public.fqs_t_int fqs_t_int WHERE (id = ANY (ARRAY[808, 809]))
(5 rows)
antdb=# EXPLAIN (verbose) SELECT DISTINCT(iid) FROM fqs_t_int WHERE id in (808,809);
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_int.iid
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT DISTINCT iid FROM public.fqs_t_int fqs_t_int WHERE (id = ANY (ARRAY[808, 809]))
(5 rows)
# 表只分布在一个DN节点上的带有in的查询语句，in参数是含有绑定变量的，分片键和非分片键都可
antdb=# prepare s_SELECT_one_DN1(int, int, int, int) as SELECT * FROM fqs_t_DN1 WHERE id in ($1,$2,$3,$4);
PREPARE
antdb=# EXPLAIN verbose execute s_SELECT_one_DN1(1,2,3,4);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_DN1.id, fqs_t_DN1.num, fqs_t_DN1.des
Node/s: DN1
Remote query: SELECT id, num, des FROM public.fqs_t_DN1 fqs_t_DN1 WHERE (id = ANY (ARRAY[$1, $2, $3, $4]))
(4 rows)
# 表只分布在一个DN节点上的带有in的查询语句，in参数是静态常量的，分片键和非分片键都可
antdb=# EXPLAIN verbose SELECT * FROM fqs_t_DN1 WHERE id in (1,2,3,4);
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t_DN1.id, fqs_t_DN1.num, fqs_t_DN1.des
Node/s: DN1
Remote query: SELECT id, num, des FROM public.fqs_t_DN1 fqs_t_DN1 WHERE (id = ANY (ARRAY[1, 2, 3, 4]))
(4 rows)
# 表只分布在一个DN节点上的insert的单个语句，参数含有绑定变量的
antdb=# prepare s_insert_one_DN1(int, int, text) as INSERT INTO fqs_t_DN1 VALUES($1,$2,$3);
PREPARE
antdb=# EXPLAIN verbose execute s_insert_one_DN1(5,5,'55');
QUERY PLAN
----------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: ($1), ($2), ($3)
Node expr: $1
Remote query: INSERT INTO public.fqs_t_DN1 AS fqs_t_DN1 (id, num, des) VALUES ($1, $2, $3)
(4 rows)
# 表只分布在一个DN节点上的insert的单个语句，参数是静态常量的
antdb=# EXPLAIN verbose INSERT INTO fqs_t_DN1 VALUES(5,1,'111');
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: 5, 1, '111'::text
Node expr: 5
Remote query: INSERT INTO public.fqs_t_DN1 AS fqs_t_DN1 (id, num, des) VALUES (5, 1, '111'::text)
(4 rows)
# 表只分布在一个DN节点上的delete的带有in的语句，参数含有绑定变量的，分片键和非分片键都可
antdb=# prepare s_delete_one_DN1(int, int, int, int) as DELETE FROM fqs_t_DN1 WHERE id in ($1,$2,$3,$4);
PREPARE
antdb=# EXPLAIN verbose execute s_delete_one_DN1(1,2,3,4);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Node/s: DN1
Remote query: DELETE FROM public.fqs_t_DN1 fqs_t_DN1 WHERE (id = ANY (ARRAY[$1, $2, $3, $4]))
(3 rows)
# 表只分布在一个DN节点上的delete的带有in的语句，参数是静态常量的，分片键和非分片键都可
antdb=# EXPLAIN verbose DELETE FROM fqs_t_DN1 WHERE id in (1,2,3,4);
QUERY PLAN
---------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Node/s: DN1
Remote query: DELETE FROM public.fqs_t_DN1 fqs_t_DN1 WHERE (id = ANY (ARRAY[1, 2, 3, 4]))
(3 rows)
# 表只分布在一个DN节点上的update的语句，参数是含有绑定变量的，分片键和非分片键都可
antdb=# prepare s_update_one_DN1(int) as UPDATE fqs_t_DN1 SET num = 5 WHERE id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_update_one_DN1(1);
QUERY PLAN
-------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: 5
Node expr: $1
Remote query: UPDATE public.fqs_t_DN1 fqs_t_DN1 SET num = 5 WHERE (id = $1)
(4 rows)
# 表只分布在一个DN节点上的update的语句，参数是静态常量的，分片键和非分片键都可
antdb=# EXPLAIN verbose UPDATE fqs_t_DN1 SET num = 5 WHERE id = 1;
QUERY PLAN
------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: 5
Node/s: DN1
Remote query: UPDATE public.fqs_t_DN1 fqs_t_DN1 SET num = 5 WHERE (id = 1)
(4 rows)
多表 join 支持 FQS 的场景
多表 join 的 SQL 语句支持 FQS 的情况：
-
SQL 语句中分片键=常量的,可直接根据 SQL 语句的条件关系,推断出在哪个 DN 上执行 ①。
-
SQL 语句中分片键包含一个=变量的,可确定最终只会在一个 DN 上执行,根据 SQL 语句的语法树,构造 DN 选择条件,在运行时确定在哪个 DN 上执行 ②。
-
SQL 语句中不包含分片键条件的，CN 只需要 gather DN 节点结果的 SQL ③。
-
如果 SQL 语句中包含了多个分片表，需要满足以下条件 ④。
1）多个分片表之间有通过分片键关联
2）分布表的分布规则相同
3）SQL 的顶层没有聚合函数
场景分3种组合情况：②④、①④、③
-
子场景：整个 SQL 下发单个 DN，适用于 ②④、①④。
# 例如：表bmsql_customer按c_w_id的hash分布，bmsql_warehouse按w_id的hash分布,这2个表是benchmarksql里面的表。 prepare s31(int, int, int) as SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer join bmsql_warehouse on (w_id = c_w_id) WHERE c_w_id = $1 and c_d_id = $2 and c_id = $3; EXPLAIN verbose execute s31(1, 1, 1); # 适用场景：单DN能完全执行出结果,常见于TP点查场景，对应上面的需求②④。 EXPLAIN verbose SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer join bmsql_warehouse on (w_id = c_w_id) WHERE c_w_id =1 and c_d_id =1 and c_id =1; # 适用场景：单DN能完全执行出结果,常见于TP点查场景，对应上面的需求①④。 -
子场景：整个 SQL 下发所有 DN (典型场景)，适用于 ③。
# 例如: 表bmsql_customer按c_w_id的hash分布，bmsql_warehouse按w_id的hash分布,这2个表是benchmarksql里面的表。 EXPLAIN verbose SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer inner join bmsql_warehouse on (w_id = c_w_id); # 适用场景：各DN执行时无数据交互。对应上面的需求③：SQL语句中不包含分片键条件的，CN只需要gather DN节点结果的SQL
内连接单数据节点的 SELECT 改造支持
# 测试用表和数据请参加附录
# 内连接单数据节点的SELECT改造支持（绑定变量的）
antdb=# prepare s31(int, int, int) as SELECT c_discount, c_last, c_credit, w_tax FROM fqs_bmsql_customer join fqs_bmsql_warehouse on (w_id = c_w_id) WHERE c_w_id = $1 and c_d_id = $2 and c_id = $3;
PREPARE
antdb=# EXPLAIN (verbose) execute s31(1, 1, 1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_customer.c_discount, fqs_bmsql_customer.c_last, fqs_bmsql_customer.c_credit, fqs_bmsql_warehouse.w_tax
Node expr: $1
Remote query: SELECT fqs_bmsql_customer.c_discount, fqs_bmsql_customer.c_last, fqs_bmsql_customer.c_credit, fqs_bmsql_warehouse.w_tax FROM (public.fqs_bmsql_customer fqs_bmsql_customer
JOIN public.fqs_bmsql_warehouse fqs_bmsql_warehouse ON ((fqs_bmsql_warehouse.w_id = fqs_bmsql_customer.c_w_id))) WHERE ((fqs_bmsql_customer.c_w_id = $1) AND (fqs_bmsql_customer.c_d_id = $2
) AND (fqs_bmsql_customer.c_id = $3))
(4 rows)
# 内连接单数据节点的SELECT改造支持（静态常量的）
antdb=# EXPLAIN (verbose) SELECT c_discount, c_last, c_credit, w_tax FROM fqs_bmsql_customer join fqs_bmsql_warehouse on (w_id = c_w_id) WHERE c_w_id = 3 and c_d_id = 2 and c_id = 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_bmsql_customer.c_discount, fqs_bmsql_customer.c_last, fqs_bmsql_customer.c_credit, fqs_bmsql_warehouse.w_tax
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT fqs_bmsql_customer.c_discount, fqs_bmsql_customer.c_last, fqs_bmsql_customer.c_credit, fqs_bmsql_warehouse.w_tax FROM (public.fqs_bmsql_customer fqs_bmsql_customer
JOIN public.fqs_bmsql_warehouse fqs_bmsql_warehouse ON ((fqs_bmsql_warehouse.w_id = fqs_bmsql_customer.c_w_id))) WHERE ((fqs_bmsql_customer.c_w_id = 3) AND (fqs_bmsql_customer.c_d_id = 2)
AND (fqs_bmsql_customer.c_id = 1))
(5 rows)
左连接/全连接，on 的条件都包含分片键且没有排序
# 测试用表和数据请参加附录
# 左连接2表on的条件都包含分片键的且没有排序的FQS支持
antdb=# EXPLAIN (verbose) SELECT EMP_ID, NAME, DEPT FROM FQS_COMPANY LEFT OUTER JOIN FQS_DEPARTMENT ON FQS_COMPANY.ID = FQS_DEPARTMENT.ID;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_department.emp_id, fqs_company.name, fqs_department.dept
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT fqs_department.emp_id, fqs_company.name, fqs_department.dept FROM (public.fqs_company fqs_company LEFT JOIN public.fqs_department fqs_department ON ((fqs_company.id
= fqs_department.id)))
(5 rows)
# 全连接2表on的条件都包含分片键的且没有排序的FQS支持
antdb=# EXPLAIN (verbose) SELECT EMP_ID, NAME, DEPT FROM FQS_COMPANY FULL OUTER JOIN FQS_DEPARTMENT ON FQS_COMPANY.ID = FQS_DEPARTMENT.ID;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_department.emp_id, fqs_company.name, fqs_department.dept
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT fqs_department.emp_id, fqs_company.name, fqs_department.dept FROM (public.fqs_company fqs_company FULL JOIN public.fqs_department fqs_department ON ((fqs_company.id
= fqs_department.id)))
(5 rows)
对 Query 条件表达式中多个分片条件的筛选，包含分片键且是绑定变量的
# 测试用表和数据请参加附录
# 对Query条件表达式中多个分片条件的筛选，包含分片键且是绑定变量的
antdb=# prepare s2(int) as SELECT * FROM fqs_t1,fqs_t2 WHERE fqs_t1.c1=fqs_t2.c1 and fqs_t1.c1 = $1;
PREPARE
antdb=# EXPLAIN (verbose) execute s2(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id
Node expr: $1
Remote query: SELECT fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id FROM public.fqs_t1 fqs_t1, public.fqs_t2 fqs_t2 WHERE ((fqs_t1.c1 = fqs_t2.c1) AND (fqs_t1.c1 = $1))
(4 rows)
# 对Query条件表达式中多个分片条件的筛选，包含分片键且静态常量的
antdb=# EXPLAIN (verbose) SELECT * FROM fqs_t1,fqs_t2 WHERE fqs_t1.c1=fqs_t2.c1 and fqs_t1.c1 = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id
Node/s: DN1
Remote query: SELECT fqs_t1.c1, fqs_t1.rel_id, fqs_t2.c1, fqs_t2.rel_id FROM public.fqs_t1 fqs_t1, public.fqs_t2 fqs_t2 WHERE ((fqs_t1.c1 = fqs_t2.c1) AND (fqs_t1.c1 = 2))
(4 rows)
2 张分布表，子查询里面有分片键
# 测试用表和数据请参加附录
# 2 张分布表，子查询里面有分片键条件的且是动态绑定变量的
antdb=# prepare s_2table_subquery(int) as SELECT * FROM (SELECT * FROM fqs_t1 WHERE fqs_t1.c1 = $1) a,fqs_t2 WHERE a.c1=fqs_t2.c1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_2table_subquery(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: a.c1, a.rel_id, fqs_t2.c1, fqs_t2.rel_id
Node expr: $1
Remote query: SELECT a.c1, a.rel_id, fqs_t2.c1, fqs_t2.rel_id FROM (SELECT fqs_t1.c1, fqs_t1.rel_id FROM public.fqs_t1 fqs_t1 WHERE (fqs_t1.c1 = $1)) a, public.fqs_t2 fqs_t2 WHERE (a.c1
= fqs_t2.c1)
(4 rows)
# 2张分布表，子查询里面有分片键条件的且是静态常量的
antdb=# EXPLAIN (verbose) SELECT * FROM (SELECT * FROM fqs_t1 WHERE fqs_t1.c1 = 1) a,fqs_t2 WHERE a.c1=fqs_t2.c1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: a.c1, a.rel_id, fqs_t2.c1, fqs_t2.rel_id
Node/s: DN2
Remote query: SELECT a.c1, a.rel_id, fqs_t2.c1, fqs_t2.rel_id FROM (SELECT fqs_t1.c1, fqs_t1.rel_id FROM public.fqs_t1 fqs_t1 WHERE (fqs_t1.c1 = 1)) a, public.fqs_t2 fqs_t2 WHERE (a.c1
= fqs_t2.c1)
(4 rows)
3 表 join
# 测试用表和数据请参加附录
# 三表是 hash 分布的，分布列相同，3 表 join，WHERE 条件有分布列条件的两两互等的，且是动态绑定变量的，没有排序的
antdb=# prepare s_3table_join(int) as SELECT * FROM fqs_aa,fqs_bb,fqs_cc WHERE fqs_aa.a=fqs_bb.a and fqs_aa.a=fqs_cc.a and fqs_aa.a=$1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_3table_join(4);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d, fqs_bb.a, fqs_bb.b, fqs_bb.c, fqs_bb.d, fqs_cc.a, fqs_cc.b, fqs_cc.c, fqs_cc.d
Node expr: $1
Remote query: SELECT fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d, fqs_bb.a, fqs_bb.b, fqs_bb.c, fqs_bb.d, fqs_cc.a, fqs_cc.b, fqs_cc.c, fqs_cc.d FROM public.fqs_aa fqs_aa, public.fqs_bb fqs_
bb, public.fqs_cc fqs_cc WHERE ((fqs_aa.a = fqs_bb.a) AND (fqs_aa.a = fqs_cc.a) AND (fqs_aa.a = $1))
(4 rows)
# 三表是hash分布的，分布列相同，3表join，WHERE条件有分布列条件的两两互等的，且是静态常量的，没有排序的
antdb=# EXPLAIN verbose SELECT * FROM fqs_aa,fqs_bb,fqs_cc WHERE fqs_aa.a=fqs_bb.a and fqs_aa.a=fqs_cc.a and fqs_aa.a =4;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d, fqs_bb.a, fqs_bb.b, fqs_bb.c, fqs_bb.d, fqs_cc.a, fqs_cc.b, fqs_cc.c, fqs_cc.d
Node/s: DN2
Remote query: SELECT fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d, fqs_bb.a, fqs_bb.b, fqs_bb.c, fqs_bb.d, fqs_cc.a, fqs_cc.b, fqs_cc.c, fqs_cc.d FROM public.fqs_aa fqs_aa, public.fqs_bb fqs_
bb, public.fqs_cc fqs_cc WHERE ((fqs_aa.a = fqs_bb.a) AND (fqs_aa.a = fqs_cc.a) AND (fqs_aa.a = 4))
(4 rows)
# 三表按hash分布的，分布列相同，3表join，join的顺序是“内连接、内连接“、“内连接、左连接”、“内连接、全连接”、“左连接、左连接”、
# “左连接、内连接”、“左连接、全连接”、“全连接、内连接”、“全连接、左连接”、“全连接、全连接”
# join的条件是分片键，且带有动态绑定变量的，没有排序
# 简单举例如下
# INNER JOIN：
antdb=# prepare s_3table_explicit_join1(int) as SELECT fqs_aa.a,fqs_aa.b,fqs_aa.c,fqs_aa.d FROM fqs_aa inner join fqs_bb on fqs_aa.a = fqs_bb.a inner join fqs_cc on fqs_bb.a= fqs_cc.a and fqs_aa.a=$1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_3table_explicit_join1(4);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d FROM ((public.fqs_aa fqs_aa JOIN public.fqs_bb fqs_bb ON ((fqs_aa.a = fqs_bb.a))) JOIN public.fqs_cc fqs_cc ON (((fqs_bb.a =
fqs_cc.a) AND (fqs_aa.a = $1))))
(5 rows)
# LEFT OUTER JOIN：
antdb=# prepare s_3table_explicit_join4(int) as SELECT fqs_aa.a,fqs_aa.b,fqs_aa.c,fqs_aa.d FROM fqs_aa left OUTER join fqs_bb on fqs_aa.a = fqs_bb.a left OUTER join fqs_cc on fqs_bb.a= fqs_cc.a and fqs_aa.a=$1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_3table_explicit_join4(4);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d FROM ((public.fqs_aa fqs_aa LEFT JOIN public.fqs_bb fqs_bb ON ((fqs_aa.a = fqs_bb.a))) LEFT JOIN public.fqs_cc fqs_cc ON (((f
qs_bb.a = fqs_cc.a) AND (fqs_aa.a = $1))))
(5 rows)
# FULL OUTER JOIN：
antdb=# prepare s_3table_explicit_join7(int) as SELECT fqs_aa.a,fqs_aa.b,fqs_aa.c,fqs_aa.d FROM fqs_aa FULL OUTER JOIN fqs_bb on fqs_aa.a = fqs_bb.a inner join fqs_cc on fqs_bb.a= fqs_cc.a and fqs_aa.a=$1;
PREPARE
antdb=# EXPLAIN (verbose) execute s_3table_explicit_join7(4);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT fqs_aa.a, fqs_aa.b, fqs_aa.c, fqs_aa.d FROM ((public.fqs_aa fqs_aa FULL JOIN public.fqs_bb fqs_bb ON ((fqs_aa.a = fqs_bb.a))) JOIN public.fqs_cc fqs_cc ON (((fqs_bb
.a = fqs_cc.a) AND (fqs_aa.a = $1))))
(5 rows)
多表 join 的其他情况
# 测试用表和数据请参加附录
# 2张按hash分布的表，join的条件是分片键，没有排序，join的类型是内连接、左连接、全连接，不带WHERE条件
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_hash2 t2 on (t1.id = t2.id);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id)))
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_hash2 t2 on (t1.id = t2.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 LEFT JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id)))
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_hash2 t2 on (t1.id = t2.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 FULL JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id)))
(5 rows)
# 1张按hash分布的表，1张复制表，join的条件是分片键，没有排序，join的类型是内连接、左连接，不带WHERE条件
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id)))
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id)))
(5 rows)
# 2张取mod分布的表，join的条件是分片键，没有排序，join的类型是内连接、左连接、全连接，不带WHERE条件
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_mod2 t2 on (t1.id = t2.id);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id)))
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_mod2 t2 on (t1.id = t2.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 LEFT JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id)))
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_mod2 t2 on (t1.id = t2.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 FULL JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id)))
(5 rows)
# 1张取mod分布的表，1张复制表，join的条件是分片键，没有排序，join的类型是内连接、左连接，不带WHERE条件
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id)))
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id)))
(5 rows)
# 1张复制表，一张按hash分布的表，join的条件是分片键，没有排序，join的类型是内连接，不带WHERE条件
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_hash1 t2 ON ((t1.id = t2.id)))
(5 rows)
# 1张复制表，一张取mod分布的表，join的条件是分片键，没有排序，join的类型是内连接，不带WHERE条件
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_mod1 t2 ON ((t1.id = t2.id)))
(5 rows)
# 2张复制表，join的条件是随意，join的类型是内连接、左连接、全连接，可以带排序
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_repl2 t2 on (t1.id = t2.id);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id)))
(4 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_repl2 t2 on (t1.id = t2.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 LEFT JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id)))
(4 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_repl2 t2 on (t1.id = t2.id);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 FULL JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id)))
(4 rows)
# 1张复制表，1张按random分布的表，join的条件是随意，join的类型是内连接，不带排序
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_random1 t2 ON ((t1.id = t2.id)))
(5 rows)
# 1张按random分布的表，1张复制表，join的条件是随意，join的类型是内连接、左连接，不带排序
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_random1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id)))
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_random1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id)))
(5 rows)
# 1张hash表，1张hash表，join条件是分片键，join的类型是内连接、左连接、全连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_hash2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_hash2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 LEFT JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_hash2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 FULL JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join1(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_hash2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join1(3);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join2(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_hash2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join2(3);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 LEFT JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join3(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_hash2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join3(3);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 FULL JOIN public.fqs_test_hash2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
# 1张hash表，1张复制表，join条件是分片键，join的类型是内连接、左连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join7(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join7(3);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join8(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join8(3);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_hash1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
# 1张取mod分布的表，1张取mod分布的表，join条件是分片键，join的类型是内连接、左连接、全连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_mod2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_mod2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 LEFT JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_mod2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 FULL JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join16(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_mod2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join16(3);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join17(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_mod2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join17(3);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 LEFT JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join18(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_mod2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join18(3);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 FULL JOIN public.fqs_test_mod2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
# 1张取mod分布的表，1张复制表，join条件是分片键，join的类型是内连接、左连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join19(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join19(3);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join20(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join20(3);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node expr: $1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_mod1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
# 1张复制表，1张按hash分布的表，join条件是分片键，join的类型是内连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_hash1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join25(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join25(3);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_hash1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(5 rows)
# 1张复制表，1张取mod分布的表，join条件是分片键，join的类型是内连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_mod1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join28(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join28(3);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_mod1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(5 rows)
# 1张复制表，1张复制表，join条件是分片键，join的类型是内连接、左连接、全连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，带/不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_repl2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(4 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_repl2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 LEFT JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(4 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_repl2 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 FULL JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(4 rows)
antdb=# prepare s_table_join31(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_repl2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join31(3);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join32(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_repl2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join32(3);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 LEFT JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
antdb=# prepare s_table_join33(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_repl2 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join33(3);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Node/s: DN1
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 FULL JOIN public.fqs_test_repl2 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(4 rows)
# 1张复制表，1张按random分布的表，join条件是分片键，join的类型是内连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_random1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join34(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join34(3);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_repl1 t1 JOIN public.fqs_test_random1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(5 rows)
# 1张按random分布的表，1张复制表，join条件是分片键，join的类型是内连接、左连接，
# WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_random1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_random1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = 3)
(5 rows)
antdb=# prepare s_table_join43(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join43(3);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_random1 t1 JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(5 rows)
antdb=# prepare s_table_join44(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;
PREPARE
antdb=# EXPLAIN verbose execute s_table_join44(3);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1.id, t1.name
Primary node/s: DN0
Node/s: DN0, DN1, DN2
Remote query: SELECT t1.id, t1.name FROM (public.fqs_test_random1 t1 LEFT JOIN public.fqs_test_repl1 t2 ON ((t1.id = t2.id))) WHERE (t1.id = $1)
(5 rows)
FQS 不支持的场景
不支持 FQS 的条件
下述表格展示了一些典型不支持 FQS 的条件：
| 序号 | 约束条件 | 具体条件 | 具体条件 | 是否支持 |
|---|---|---|---|---|
| 1 | 当use_aux_type != USE_AUX_OFF循环表判断是否是RTE_RELATION且调用HasAuxRelation为true | 不支持 | ||
| 2 | 当是insert命令： | 1)判断表只有1张（插入记录只有1条）且insert目标里面包含了可变函数(contain_volatile_functions)，不支持FQS 2)插入多条记录（RTE）时，判断每条记录里面value里面有可变函数的(contain_volatile_functions)，不支持FQS | 不支持 | |
| 2)插入多条记录（RTE）时，判断每条记录里面value里面有可变函数的(contain_volatile_functions)，不支持FQS | 不支持 | |||
| 3 | insert 的SQL语句，分布表的带nextval序列等动态函数的，WHERE条件里面带= 分布键 | insert 的SQL语句，分布表的带nextval序列等动态函数的，WHERE条件里面带= 分布键 | 不支持 | |
| 4 | update 的SQL语句，分布表的带nextval序列等动态函数的，WHERE条件里面带= 分布键 | update 的SQL语句，分布表的带nextval序列等动态函数的，WHERE条件里面带= 分布键 | 不支持 | |
| 5 | 右连接 不支持 FQS | 右连接 不支持 FQS | 不支持 | |
| 6 | 表有多个分片键的不支持FQS | 不支持 | ||
| 7 | ora连接的（query->connect_by），不支持FQS | 不支持 | ||
| 8 | 触发器、规则，不支持FQS | 基本不支持，rule会在rewrite的时候会导致语句添加某些不满足ship的条件 | ||
| 9 | with子句的，不支持FQS | 不支持 | ||
| 10 | 有子链接的(query->hasSubLinks)，且执行节点不是复制表且其子查询也不是复制表的，不支持FQS | 不支持 | ||
| 11 | 单节点表达式：执行节点数量不等于1，且非单点表达式的且不是复制表 | 不支持ok | ||
| 12 | insert 且表达式是返回set的（expression_returns_set） 或表达式有序列不支持FQS | 不支持 | ||
| 13 | 是插入或更新的语句，且是复制表的，且目标列有序列表达式的,不支持FQS | 不支持 | ||
| 14 | insert且是分布表的： | 1）分布键不是1个的，返回NULL ,不支持FQS | 不支持 | |
| 2）分布键是1个的，且target里面不包含分布列的，返回NULL ,不支持FQS | 不支持 | |||
| 15 | RTE不支持FQS | 1)RTE_JOIN 不支持FQS | 不支持 | |
| 2)RTE_CTE 不支持FQS | 不支持 | |||
| 3)RTE_FUNCTION 不支持FQS | 不支持 | |||
| 4)RTE_VALUES 不支持FQS | 不支持 | |||
| 16 | 有不支持的表达式的，只能在cn节点上执行的表达式等，具体的表达式见下面：不支持FQS | 1) 有不支持的表达式的 SS_UNSUPPORTED_EXPR： | a.包含query->returningList的（包含查询的独立表达式不可发送） | 不支持 |
| b.CMD_UTILITY且是建表语句 | 不支持 | |||
| c.有hasRecursive with（query->hasRecursive）语句的 | 不支持 | |||
| d.不是查询并且表数量大于1(list_length(query->rtable) > 1)的 | 不支持 | |||
| e.有转换类型不对的表达式（比如表字段是char，但是你prepare 定义了int，输入了int的） | 不支持 | |||
| f.节点里面有T_SetToDefault 不可透传 | 不支持 | |||
| g.节点类型是参数（T_Param）且param->paramkind != PARAM_EXTERN | 不支持 | |||
| h.节点是T_SortGroupClause 且是sc_context->sc_for_expr = true | 不支持 | |||
| i.节点是T_FROMExpr，且是孤独立表达式（c_context->sc_for_expr） | 不支持 | |||
| j.节点是T_FROMExpr，且有条件的insert 语句： sc_context->sc_query->commandType == CMD_INSERT && ((FROMExpr *)node)->quals | 不支持,无法直接构造例子，但是通过在表上创建rule，可以达成类似的效果 | |||
| k.节点是T_JoinExpr：且是独立表达式（sc_context->sc_for_expr） | 不支持 | |||
| l.占位符等等表达式（T_SubPlan、T_AlternativeSubPlan、T_CommonTableExpr、T_SetOperationStmt、T_PlaceHolderVar、T_AppendRelInfo、T_PlaceHolderInfo） | 不支持 | |||
| 2) 有不能透传的表达式（SS_UNSHIPPABLE_EXPR）： | a.节点是T_Query 且是独立表达式的（sc_context->sc_for_expr） | 不支持 | ||
| b.节点是T_TargetEntry且表达式是空或者是伪类型 (typtype == TYPTYPE_PSEUDO) | 不支持 | |||
| c. 节点类型是T_CoerceViaIO且不能强制转换的（!can_coerce_type(1, &input_type, &output_type, cc)）（有转换类型不对的表达式） | 不支持 | |||
| d.节点是T_FuncExpr且有非安全的函数的。（s，有nextsql等这些函数的）。 | 不支持 | |||
| e.节点是T_FuncExpr 且funcexpr->funcretset && sc_context->sc_for_expr | 不支持 | |||
| f.节点是T_NullIfExpr、T_OpExpr、T_OpExpr且是无效的函数或者是不安全的。 | 不支持 | |||
| g.T_ScalarArrayOpExpr且是无效的函数或者是不安全的。 | 不支持 | |||
| h.节点是T_WindowFunc：有独立表达式或不安全函数的sc_context->sc_for_expr || !pgxc_is_func_shippable(winf->winfnoid) | 不支持 | |||
| i.节点是T_WindowClause且有独立表达式的：sc_context->sc_for_expr | 不支持 | |||
| 3) 有只能cn节点执行的表达式(SS_NEEDS_COORD)： | a.query->is_local an EXEC DIRECT o | 不支持 | ||
| b.pgxc_query_contains_only_pg_catalog：如果查询只涉及目录表，而不是EXEC直接语句，则可 以完全在协调器上进行计算。不需要涉及数据节点。 | 不支持 | |||
| c.ora且T_RownumExpr的 | 不支持 | |||
| d.节点是T_NextValueExpr 且是临时表的 临时序列 （get_rel_persistence(((NextValueExpr*)node)->seqid) == RELPERSISTENCE_TEMP） | 不支持 | |||
| e.节点是函数（T_FuncExpr）且是序列的，且是临时表，临时序列的，非const的（!IsA(c, Const) || | 不支持 | |||
| get_rel_persistence(DatumGetObjectId(c->constvalue)) == RELPERSISTENCE_TEMP） | ||||
| f.节点是T_WithCheckOption && wco->kind == WCO_VIEW_CHECK | 不支持 | |||
| 4）SS_NEED_SINGLENODE： | a.节点是T_Query且是窗口函数、排序、limitoffsert、limitcount，执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | ||
| b. 节点是T_Query且是aggs或having且执行节点group里面没分布列的，执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | |||
| c. 节点是T_Query且是groupClause 非空且执行节点group里面没分布列的，执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | |||
| d. 节点是T_Query且是distinctClause且distinctClause里面没有分布列的，执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | |||
| e. 节点是T_Aggref,且有排序或DISTINCT或具有多态转换类型的聚合等的（aggref->aggorder || aggref->aggdistinct ||aggref->agglevelsup ||!aggref->agghas_collectfn ||IsPolymorphicType(aggref->aggtrantype)），执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | |||
| f.节点是T_GroupingFunc的，执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | |||
| g.节点是T_WindowFunc的，执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | |||
| h.节点是T_WindowClause，执行节点数量不等于1，且非单点表达式的且不是复制表。 | 不支持 | |||
| 5）SS_VARLEVEL | a.是子查询引用外层语句的列(sc_context->sc_max_varlevelsup != 0) | 不支持 | ||
| 6）SS_UNSHIPPABLE_TRIGGER | a.节点是T_Query且sql命令是insert、update、delete 且是不可透传的触发器（!pgxc_check_triggers_shippability(rte->relid,query->commandType)） | 不支持 | ||
| 7）SS_UNSHIPPABLE_RELATION | a. 节点是T_Query且是update、insert、delete命令，且HasAuxRelation（辅助关系） | 不支持 | ||
| b. 节点是T_Query且是update 且没有target目标列的 | 不支持。 | |||
| c. 节点是T_Query且是SELECT的，并且是继承表且能找到继承的子表的 （find_inheritance_children） | 不支持 | |||
| 8）SS_NO_NODES： | a.节点是T_SubLink，没有找到累积的可透传节点的或可合并的节点（!sc_context->sc_subquery_en） | 不支持 | ||
| 9）SS_UNSHIPPABLE_TYPE | 节点表达式里面含有序列、视图、外表（RELKIND_SEQUENCE、RELKIND_VIEW、RELKIND_FOREIGN_TABLE）的 | 不支持 |
不支持 FQS 的部分具体 SQL 的 示例
| 序号 | 具体功能点 | 具体 SQL |
|---|---|---|
| 1 | 右连接 不支持 FQS | EXPLAIN (verbose) SELECT EMP_ID, NAME, DEPT FROM FQS_COMPANY RIGHT OUTER JOIN FQS_DEPARTMENT ON FQS_COMPANY.ID = FQS_DEPARTMENT.ID; |
| 2 | 左连接2表on的条件都包含分片键的且有排序的（按分片键排序或其他键排序），不支持 FQS | EXPLAIN (verbose) SELECT EMP_ID, NAME, DEPT FROM FQS_COMPANY LEFT OUTER JOIN FQS_DEPARTMENT ON FQS_COMPANY.ID = FQS_DEPARTMENT.ID ORDER BY FQS_COMPANY.ID; |
| 3 | 全连接2表on的条件都包含分片键的且有排序的（按分片键排序或其他键排序），不支持 FQS | EXPLAIN (verbose) SELECT EMP_ID, NAME, DEPT FROM FQS_COMPANY FULL OUTER JOIN FQS_DEPARTMENT ON FQS_COMPANY.ID = FQS_DEPARTMENT.ID ORDER BY FQS_COMPANY.ID; |
| 4 | insert 的SQL语句，分布表的带nextval序列等动态函数的，insert条件里面带分布键，分布键是静态参数或动态绑定变量参数的，不支持 FQS | PREPARE s_insert_seqval(int) AS INSERT INTO fqs_test_insert_dist (no_w_id, no_d_id) VALUES ($1, nextval('fqs_test_id_seq'));EXPLAIN (verbose) EXECUTE s_insert_seqval(1); EXPLAIN (verbose) INSERT INTO fqs_test_insert_dist (no_w_id, no_d_id) VALUES (3, nextval('fqs_test_id_seq')); |
| 5 | update 的SQL语句，分布表的带nextval序列等动态函数的，insert条件里面带分布键，分布键是静态参数或动态绑定变量参数的，不支持 FQS | PREPARE s_update_seqval(int) AS UPDATE fqs_test_insert_dist set no_d_id = nextval('fqs_test_id_seq') WHERE no_w_id = $1;EXPLAIN (verbose) execute s_update_seqval(1); EXPLAIN (verbose) UPDATE fqs_test_insert_dist set no_d_id = nextval('fqs_test_id_seq') WHERE no_w_id = 5; |
| 6 | Insert to one DN node，RTE不支持（批量插入）FQS | EXPLAIN verbose INSERT INTO fqs_t_DN1 VALUES(5,1,'111'),(6,2,'22'),(7,3,'33'),(8,4,'444'); |
| 7 | 关联子查询不支持FQS | CREATE TABLE fqs_employee (id int,name varchar(10),age int,role varchar(10)) distribute by hash(id);EXPLAIN VERBOSE SELECT id,name,age FROM fqs_employee as t1 WHERE age > (SELECT avg(age) FROM fqs_employee as t2 WHERE t1.role=t2.role group by role); |
| 8 | 随机分布的表支持单表查询，条件中包含分片键且是静态常量的，有排序的,排序键是分片键或非分片键的，不支持FQS | EXPLAIN (verbose) SELECT * FROM fqs_test_random WHERE id=1 order by id; EXPLAIN (verbose) SELECT * FROM fqs_test_random PREPARE id=1 order by name; |
| 9 | 子链接不支持 | EXPLAIN VERBOSE SELECT * FROM fqs_COMPANY c WHERE c.ID IN(SELECT D.EMP_ID FROM fqs_DEPARTMENT D WHERE D.DEPT = 'IT Billing'); |
| 10 | 分布键是int/char，且是hash分布的，条件中包含分布键且含有or/in多个条件的，且是动态绑定变量的，查询的数据都在同一个DN节点的，不支持FQS | PREPARE fqs_t_char_bind(varchar,varchar) as SELECT sum(id) FROM fqs_t_char WHERE name=$1 or name=$2;EXPLAIN (verbose) execute fqs_t_char_bind('name101','name102');PREPARE fqs_t_int_bind(int,int) as SELECT count(iid) FROM fqs_t_int WHERE id=$1 or id=$2;EXPLAIN (verbose) execute fqs_t_int_bind(808,809); |
| 11 | 1张按hash分布的表，1张取mod分布的表，join的条件是分布键，有或者没有排序，join的类型是内连接、左连接、全连接，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id); |
| 12 | 1张按hash分布的表，1张复制表，join的条件是分布键，有或者没有排序，join的类型是全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id); |
| 13 | 1张按hash分布的表，1张按random分布的表，join的条件是分布键，有或者没有排序，join的类型是内连接、左连接、全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id); |
| 14 | 1张取mod分布的表，1张按hash分布的表，join的条件是分布键，有或者没有排序，join的类型是内连接、左连接、全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id); |
| 15 | 1张取mod分布的表，1张复制表，join的条件是分片键，有或没有排序，join的类型是全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id); |
| 16 | 1张取mod分布的表，1张按random分布的表，join的条件是分片键，有或没有排序，join的类型是内连接、左连接、全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id); |
| 17 | 1张复制表，一张按hash分布的表，join的条件是分片键，有或没有排序，join的类型是左连接、全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id); |
| 18 | 1张复制表，一张取mod分布的表，join的条件是分片键，有或没有排序，join的类型是左连接、全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id); |
| 19 | 1张复制表，1张按random分布的表，join的条件是分片键，join的类型是内连接，带排序，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id) order by t1.id; |
| 20 | 1张复制表，1张按random分布的表，join的条件是分片键，join的类型是左连接、全连接，带或不带排序，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id); |
| 21 | 1张按random分布的表，1张按hash分布的表，join的条件是分片键，join的类型是内连接、左连接、全连接，不带排序/带排序，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id); |
| 22 | 1张按random分布的表，1张取mod分布的表， join的条件是分片键，有或没有排序，join的类型是内连接、左连接、全连接，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id); |
| 23 | 1张按random分布的表，1张复制表，join的条件是分片键，join的类型是全连接，不带排序/带排序，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id); |
| 24 | 1张按random分布的表，1张按random分布的表，join的条件是随意，join的类型是内连接、左连接、全连接，不带排序/带排序，不带WHERE条件，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_random2 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_random2 t2 on (t1.id = t2.id);EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_random2 t2 on (t1.id = t2.id); |
| 25 | 1张hash表，1张取mod分布的表，join条件是分片键，join的类型是内连接、左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，都不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join4(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join4(3);prepare s_table_join5(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join5(3);prepare s_table_join6(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join6(3); |
| 26 | 1张hash表，1张复制表表，join条件是分片键，join的类型是全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join9(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join9(3); |
| 27 | 1张按hash分布的表，1张按random分布的表，join的条件是分片键， join的类型是内连接、左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join10(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join10(3);prepare s_table_join11(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join11(3);prepare s_table_join12(int) as SELECT t1.id, t1.name FROM fqs_test_hash1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join12(3); |
| 28 | 1张取mod分布的表，1张按hash分布的表， join的条件是分片键， join的类型是内连接、左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join13(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join13(3);prepare s_table_join14(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join14(3);prepare s_table_join15(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join15(3); |
| 29 | 1张取mod分布的表，1张复制表， join的条件是分片键， join的类型是全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join21(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join21(3); |
| 30 | 1张取mod分布的表，1张按random分布的表，join的条件是分片键， join的类型是内连接、左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join22(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 inner join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join22(3);prepare s_table_join23(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join23(3);prepare s_table_join24(int) as SELECT t1.id, t1.name FROM fqs_test_mod1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join24(3); |
| 31 | 1张复制表，1张按hash分布的表，join条件是分片键，join的类型是左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join26(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join26(3);prepare s_table_join27(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join27(3); |
| 32 | 1张复制表，1张取mod分布的表，join 条件是分片键，join 的类型是左连接、全连接，WHERE 的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join29(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join29(3);prepare s_table_join30(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join30(3); |
| 33 | 1 张复制表，1 张按random 分布的表，join 条件是分片键，join 的类型是左连接、全连接，WHERE 的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，带/不带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join35(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 left outer join fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join35(3);prepare s_table_join36(int) as SELECT t1.id, t1.name FROM fqs_test_repl1 t1 FULL OUTER JOIN fqs_test_random1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join36(3); |
| 34 | 1张按random分布的表，1张按hash分布的表，join的条件是分片键， join的类型是内连接、左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join37(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join37(3);prepare s_table_join38(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join38(3);prepare s_table_join39(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_hash1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join39(3); |
| 35 | 1张按random分布的表，1张取mod分布的表，join的条件是分片键， join的类型是内连接、左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join40(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join40(3);prepare s_table_join41(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join41(3);prepare s_table_join42(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_mod1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join42(3); |
| 36 | 1张按random分布的表，1张复制表，join的条件是分片键， join的类型是全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join45(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_repl1 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join45(3); |
| 37 | 1张按random分布的表，1张按random分布的表，join的条件是分片键， join的类型是内连接、左连接、全连接，WHERE的条件是分片键或非分片键“=”条件且参数是静态或动态绑定变量的，不带排序的/带排序的，不支持FQS | EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_random2 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_random2 t2 on (t1.id = t2.id) WHERE t1.id =3;EXPLAIN verbose SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_random2 t2 on (t1.id = t2.id) WHERE t1.id =3;prepare s_table_join46(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 inner join fqs_test_random2 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join46(3);prepare s_table_join47(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 left outer join fqs_test_random2 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join47(3);prepare s_table_join48(int) as SELECT t1.id, t1.name FROM fqs_test_random1 t1 FULL OUTER JOIN fqs_test_random2 t2 on (t1.id = t2.id) WHERE t1.id = $1;EXPLAIN verbose execute s_table_join48(3); |
| 38 | SQL语句中分片键包含一个=变量的 比如：a=$1+1、mod($1,5)这种不支持，可确定最终只会在一个datanode上执行, 不支持FQS | CREATE TABLE bmsql_new_order ( no_w_id integer not null, no_d_id integer not null, no_o_id integer not null) distribute by hash(no_w_id);prepare s10(int) as SELECT no_w_id FROM bmsql_new_order WHERE no_w_id = $1+1 and no_d_id = 1 ;EXPLAIN (verbose) execute s10(1); |
附录
-- 表格 fqs_bmsql_new_order：
DROP TABLE IF EXISTS fqs_bmsql_new_order;
CREATE TABLE fqs_bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
) distribute by hash(no_w_id);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (3001, 1, 1);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (1, 1, 1);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (2, 1, 1);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (3001, 1, 2);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (1, 1, 2);
INSERT INTO fqs_bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES (2, 1, 2);
-- 表格 fqs_bmsql_order_line：
DROP TABLE IF EXISTS fqs_bmsql_order_line;
CREATE TABLE fqs_bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
) distribute by hash(ol_w_id);
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3001, 1, 1, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (1, 1, 1, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (2, 1, 1, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (3001, 1, 2, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (1, 1, 2, 1, 1, 2, 1, 22.46, 's_dist_02');
INSERT INTO fqs_bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES (2, 1, 2, 1, 1, 2, 1, 22.46, 's_dist_02');
-- 表格 fqs_bmsql_oorder：
DROP TABLE IF EXISTS fqs_bmsql_oorder;
create table fqs_bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp
) distribute by hash(o_w_id);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (3001, 1, 1, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (1, 1, 1, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (2, 1, 1, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (3001, 1, 2, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (1, 1, 2, 1, '2022-04-24 14:16:10.828', 5, 1);
INSERT INTO fqs_bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (2, 1, 2, 1, '2022-04-24 14:16:10.828', 5, 1);
-- 表格 fqs_bmsql_customer
DROP TABLE IF EXISTS fqs_bmsql_customer;
CREATE TABLE fqs_bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
) distribute by hash(c_w_id);
INSERT INTO fqs_bmsql_customer VALUES (1, 1, 1, 0.4395, 'GC', 'BARBARBAR', 'HNVBhIjtjsku8', 50000.00, -10.00, 10.00, 1, 1, 'HDJJSDc1RLmwqHcr', 'kIWYlHTvnf7kUxa', 'ra6KA3FJjY', 'BU', '557011111', '9366706537194349', '2022-09-20 17:44:43.022', 'OE', 'RXEzmlIlVMBZV0vuNjuvmzBZR3t1wLxeduFW9xlzsrpwqewHEVqb9TkGx6kI0iWMy
5nuuK8F48JToOYbCq2VQxwNbwoPnCoJKmMNJiJEwXBB6eEls9jZfngID6MMJhQ7XgYMRnDWG0qh5ln4i92A1uE2PKjjIysF2YTKNo5XLWmWq2NNNhsdOAuvpW6UwEGwtYDsegshgpqKI6koRjCvW3GPQBTxaCBL7vh1mE4
1AG0elXsPBUSXGbQEHrw6tFsw3plpHfYkH36jagdIi5Dk2TcfS19z8veG9VSBjUwRhg2i7jwsKC86gQ9GB67DNgYkzZdsCNv36cByw3vOA4B0QvOcKnByOkFwUijzta9ZBBwnHrao3n4m979UzeK6bRCvMXrM2N2ewngvTkZmtoysKA');
INSERT INTO fqs_bmsql_customer VALUES (1, 1, 2, 0.1748, 'GC', 'BARBAROUGHT', 'kEPmpoUTA0', 50000.00, -10.00, 10.00, 1, 1, 'Hi7ZDuNEFVL3BFhq3V', 'mCM8h1kALRvU5I4k'
, 'c3VpfkpPADAe1Th2', 'FA', '976111111', '3045334757503957', '2022-09-20 17:44:43.023', 'OE', 'fXcA9aGK36u4ILnvtdDdd95XkPEeeSNwho03B8fhHy41MB15FOwvqEQrSRkiWDPRgYaXXou
gfj0tFl72xfVRY0ELDKEKoII0AA1f3ncdK8SRLgZK18uAVnS0daMnV1xkrOZyHmQqSI7Thnp8qT1T02AfTxIz6jr1lzOY7T3bFlX8S1DkvoNjCOMh97LEYBXWdoxGYGjb6rWSRKDJuIIARyv1fhuTW6TYb4mCi8pa5JcA3
xnir5FuzutQbUcis76g2sQQCrgEg3shKwCtoSRStFy1XqjfQr9Un0hydSoJeLwXKqo6SBgBGuI3XTpgvLEgTw93OsE0M2HinmOpHFPEb3Qr3LhwHZePB7mWnt4f8JocvcYYhh9eWcaB0yWn3PRrMWygGIXX2NY2bVL6hr3
hgABe4oEYDih4wMpAxNx4mao');
-- 表格 fqs_bmsql_warehouse：
DROP TABLE IF EXISTS fqs_bmsql_warehouse;
CREATE TABLE fqs_bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
) distribute by hash(w_id);
INSERT INTO fqs_bmsql_warehouse VALUES (1, 300000.00, 0.0468, 'gehIih', 'CIJ5N3Y3F5s', 'ul7E6xoKz37', 'W2Kn3kOm7F7IH', 'FL', '007411111');
INSERT INTO fqs_bmsql_warehouse VALUES (2, 300000.00, 0.0720, 'TwfKXI', 'qT5gTWKSYLqeteLYZ6A', 'zG4c4w9F2sWCU', 'Kr0SnDySPb9j5Fe1', 'HA', '499111111');
INSERT INTO fqs_bmsql_warehouse VALUES (3, 300000.00, 0.1337, 'ec9fPQ9', 'c4z8apy6f44aOnKT', 'kgCuQ6HCmITVLsseI2L', 'Y3KHRBZygx', 'LY', '529111111');
INSERT INTO fqs_bmsql_warehouse VALUES (4, 300000.00, 0.0422, 'f3qgAZWl', 'H6ygGef5QIS', 'ToYe3XH8fL01f', 'k3FgsJ5dB8ZjVwhMpyim', 'KH', '998111111');
INSERT INTO fqs_bmsql_warehouse VALUES (5, 300000.00, 0.1464, 'Er8Mt3', 'cOdXHSNZgUjcByP9', 'zCujFInAWxrmDl4X', 'Wpc7AX0IC0y2P6Na', 'BB', '159811111');
-- 表格 fqs_bmsql_district：
DROP TABLE IF EXISTS fqs_bmsql_district;
CREATE TABLE fqs_bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
) distribute by hash(d_w_id);
INSERT INTO fqs_bmsql_district VALUES (1, 1, 30000.00, 0.0140, 3001, 'Ou2TyklYKp', 'QU7IWKeF7CmEPw', 'wvvLhFQNfms4ubgNtutj', 'LUsGPw2sLQhidDIQeJ8', 'PH', '596911111');
INSERT INTO fqs_bmsql_district VALUES (1, 2, 30000.00, 0.0885, 3001, 'z7ooGzlm', 'p3k33NWJYeo6IK5m', 'Mrl6ehDCQcY2RPEa', 'KJiO0vplriI', 'XY', '568211111');
INSERT INTO fqs_bmsql_district VALUES (1, 3, 30000.00, 0.0753, 3001, 'uQhmcio', 'ZZd4QYPh2R4jP', 'BUzVx3gLsRIu', 'MInqHFI7K8', 'DD', '964711111');
INSERT INTO fqs_bmsql_district VALUES (2, 1, 30000.00, 0.0706, 3001, 'wI93M62hoW', 'oMYOpBCYlyQFJ7iKBs', 'cycBxwfjw02LjP0hS3', 'YnKNWyaIpqyqJvnGR', 'AI', '118411111');
INSERT INTO fqs_bmsql_district VALUES (2, 2, 30000.00, 0.1235, 3001, 'zsyRwYjEI', 'GxfjQUMmGvb', 'rvdyaHeqCvd2vm1OqX1S', 'hJbAR2M0pfehPCCADVfD', 'UR', '157011111');
INSERT INTO fqs_bmsql_district VALUES (2, 3, 30000.00, 0.0722, 3001, 'Yl3frBMW', 'AIxDjNtK9zHFVd79EJK', 'Lxe2AGES0S5ckpOceXm', 'CqCCYhGddw71Y', 'XM', '812911111');
-- 表格 fqs_window_test：
DROP TABLE IF EXISTS fqs_window_test;
CREATE TABLE fqs_window_test (
id int4 NULL,
province varchar(10) NULL,
city varchar(10) NULL
);
INSERT INTO fqs_window_test (id,province,city) VALUES
(1,'guangdong','guangzhou')
,(2,'guangdong','shenzhen')
,(3,'guangdong','foushan')
,(4,'hunan','changsha')
,(5,'sichuan','chengdu')
,(6,'hunan','hengyang');
-- 表格 fqs_bmsql_warehouse：
DROP TABLE IF EXISTS fqs_bmsql_warehouse;
CREATE TABLE fqs_bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
) distribute by hash(w_id);
INSERT INTO fqs_bmsql_warehouse VALUES (1, 300000.00, 0.0468, 'gehIih', 'CIJ5N3Y3F5s', 'ul7E6xoKz37', 'W2Kn3kOm7F7IH', 'FL', '007411111');
INSERT INTO fqs_bmsql_warehouse VALUES (2, 300000.00, 0.0720, 'TwfKXI', 'qT5gTWKSYLqeteLYZ6A', 'zG4c4w9F2sWCU', 'Kr0SnDySPb9j5Fe1', 'HA', '499111111');
INSERT INTO fqs_bmsql_warehouse VALUES (3, 300000.00, 0.1337, 'ec9fPQ9', 'c4z8apy6f44aOnKT', 'kgCuQ6HCmITVLsseI2L', 'Y3KHRBZygx', 'LY', '529111111');
INSERT INTO fqs_bmsql_warehouse VALUES (4, 300000.00, 0.0422, 'f3qgAZWl', 'H6ygGef5QIS', 'ToYe3XH8fL01f', 'k3FgsJ5dB8ZjVwhMpyim', 'KH', '998111111');
INSERT INTO fqs_bmsql_warehouse VALUES (5, 300000.00, 0.1464, 'Er8Mt3', 'cOdXHSNZgUjcByP9', 'zCujFInAWxrmDl4X', 'Wpc7AX0IC0y2P6Na', 'BB', '159811111');
-- 表格 FQS_COMPANY：
DROP TABLE IF EXISTS FQS_COMPANY;
CREATE TABLE FQS_COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO FQS_COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO FQS_COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
-- 表格 FQS_DEPARTMENT：
DROP TABLE IF EXISTS FQS_DEPARTMENT;
CREATE TABLE FQS_DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
INSERT INTO FQS_DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT INTO FQS_DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT INTO FQS_DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
-- 表格 fqs_t1：
DROP TABLE IF EXISTS fqs_t1;
CREATE TABLE fqs_t1 (
c1 integer primary key,
rel_id INT NOT NULL
) distribute by hash(c1);
-- 表格 fqs_t2:
DROP TABLE IF EXISTS fqs_t2;
CREATE TABLE fqs_t2(
c1 INT PRIMARY KEY NOT NULL,
rel_id INT NOT NULL
) distribute by hash(c1);
-- 表格 fqs_pgbench_history：
DROP TABLE IF EXISTS fqs_pgbench_history;
CREATE TABLE fqs_pgbench_history (tid integer,bid integer,aid integer,delta integer,mtime timestamp without time zone,filler character(22))
DISTRIBUTE BY HASH(tid);
-- 表格 fqs_test_mod：
DROP TABLE IF EXISTS fqs_test_mod;
CREATE TABLE fqs_test_mod(id int, name text) distribute by modulo(id);
INSERT INTO fqs_test_mod values (1,'1'),(2,'2'),(3,'3');
-- 表格 fqs_test_repl：
DROP TABLE IF EXISTS fqs_test_repl;
CREATE TABLE fqs_test_repl(id int, name text) distribute by REPLICATION;
INSERT INTO fqs_test_repl values (1,'1'),(2,'2'),(3,'3');
-- 表格 fqs_test_random：
DROP TABLE IF EXISTS fqs_test_random;
CREATE TABLE fqs_test_random(id int, name text) distribute by random;
INSERT INTO fqs_test_random values (1,'1'),(2,'2'),(3,'3');
-- 表格 fqs_t_char：
DROP TABLE IF EXISTS fqs_t_char;
CREATE TABLE fqs_t_char(id int,iid int,name varchar) distribute by hash(name);
INSERT INTO fqs_t_char SELECT i,floor(100*i),'name'||i FROM generate_series(0, 1000) i;
-- 表格 fqs_t_int：
DROP TABLE IF EXISTS fqs_t_int;
CREATE TABLE fqs_t_int(id int,iid int,name varchar) distribute by hash(id);
INSERT INTO fqs_t_int SELECT i,floor(100*i),'name'||i FROM generate_series(0, 1000) i;
-- 表格 fqs_aa、fqs_bb、fqs_cc：
DROP TABLE IF EXISTS fqs_aa;
DROP TABLE IF EXISTS fqs_bb;
DROP TABLE IF EXISTS fqs_cc;
CREATE TABLE fqs_aa (
a integer primary key,
b integer,
c integer,
d integer
);
CREATE TABLE fqs_bb (
a integer primary key,
b integer,
c integer,
d integer
);
CREATE TABLE fqs_cc (
a integer primary key,
b integer,
c integer,
d integer
);
INSERT INTO fqs_aa VALUES(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
INSERT INTO fqs_bb VALUES(1,1,2,1),(2,2,3,2),(3,3,4,3),(4,4,4,4);
INSERT INTO fqs_aa VALUES(5,1,1,1),(6,2,2,2),(7,3,3,3),(8,4,4,4);
INSERT INTO fqs_cc VALUES(1,1,2,1),(2,2,3,2),(3,3,4,3),(4,4,4,4);
-- 表格 fqs_t_DN1：
DROP TABLE IF EXISTS fqs_t_DN1;
CREATE TABLE fqs_t_DN1(id int, num int, des text) to node(DN1);
INSERT INTO fqs_t_DN1 VALUES(1,1,'111'),(2,2,'22'),(3,3,'33'),(4,4,'444');
-- 表格 fqs_test_hash1、fqs_test_hash2：
DROP TABLE IF EXISTS fqs_test_hash1;
CREATE TABLE fqs_test_hash1(id int, name text) distribute by hash(id);
INSERT INTO fqs_test_hash1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_hash2;
CREATE TABLE fqs_test_hash2(id int, name text) distribute by hash(id);
INSERT INTO fqs_test_hash2 values (1,'1'),(2,'2'),(3,'3');
-- 表格 fqs_test_repl1、fqs_test_repl2：
DROP TABLE IF EXISTS fqs_test_repl1;
CREATE TABLE fqs_test_repl1(id int, name text) distribute by replication;
INSERT INTO fqs_test_repl1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_repl2;
CREATE TABLE fqs_test_repl2(id int, name text) distribute by replication;
INSERT INTO fqs_test_repl2 values (1,'1'),(2,'2'),(3,'3');
-- 表格 fqs_test_mod1、fqs_test_mod2：
DROP TABLE IF EXISTS fqs_test_mod1;
CREATE TABLE fqs_test_mod1(id int, name text) distribute by modulo(id);
INSERT INTO fqs_test_mod1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_mod2;
CREATE TABLE fqs_test_mod2(id int, name text) distribute by modulo(id);
INSERT INTO fqs_test_mod2 values (1,'1'),(2,'2'),(3,'3');
-- 表格 fqs_test_random1、fqs_test_random2：
DROP TABLE IF EXISTS fqs_test_random1;
CREATE TABLE fqs_test_random1(id int, name text) distribute by random;
INSERT INTO fqs_test_random1 values (1,'1'),(2,'2'),(3,'3');
DROP TABLE IF EXISTS fqs_test_random2;
CREATE TABLE fqs_test_random2(id int, name text) distribute by random;
INSERT INTO fqs_test_random2 values (1,'1'),(2,'2'),(3,'3');
问题反馈