多表 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)「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
