diff options
| -rw-r--r-- | src/test/regress/expected/xc_FQS.out | 291 | ||||
| -rw-r--r-- | src/test/regress/expected/xc_FQS_join.out | 307 | ||||
| -rw-r--r-- | src/test/regress/sql/xc_FQS.sql | 51 | ||||
| -rw-r--r-- | src/test/regress/sql/xc_FQS_join.sql | 4 |
4 files changed, 405 insertions, 248 deletions
diff --git a/src/test/regress/expected/xc_FQS.out b/src/test/regress/expected/xc_FQS.out index 6e923970b0..c7a9e25ca9 100644 --- a/src/test/regress/expected/xc_FQS.out +++ b/src/test/regress/expected/xc_FQS.out @@ -157,18 +157,20 @@ select distinct val, val2 from tab1_rr where val2 = 8; (1 row) explain (verbose on, nodes off, costs off) select distinct val, val2 from tab1_rr where val2 = 8; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +---------------------------------------------------- HashAggregate Output: val, val2 + Group Key: tab1_rr.val, tab1_rr.val2 -> Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_rr.val, tab1_rr.val2 -> Seq Scan on public.tab1_rr Output: val, val2 Filter: (tab1_rr.val2 = 8) -(9 rows) +(11 rows) -- should not get FQSed because of GROUP clause select val, val2 from tab1_rr where val2 = 8 group by val, val2; @@ -178,18 +180,20 @@ select val, val2 from tab1_rr where val2 = 8 group by val, val2; (1 row) explain (verbose on, nodes off, costs off) select val, val2 from tab1_rr where val2 = 8 group by val, val2; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +---------------------------------------------------- HashAggregate Output: val, val2 + Group Key: tab1_rr.val, tab1_rr.val2 -> Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_rr.val, tab1_rr.val2 -> Seq Scan on public.tab1_rr Output: val, val2 Filter: (tab1_rr.val2 = 8) -(9 rows) +(11 rows) -- should not get FQSed because of HAVING clause select sum(val) from tab1_rr where val2 = 2 group by val2 having sum(val) > 1; @@ -203,15 +207,17 @@ explain (verbose on, nodes off, costs off) select sum(val) from tab1_rr where va ---------------------------------------------------- GroupAggregate Output: pg_catalog.sum((sum(val))), val2 + Group Key: tab1_rr.val2 Filter: (pg_catalog.sum((sum(tab1_rr.val))) > 1) -> Remote Subquery Scan on all Output: sum(val), val2 -> GroupAggregate Output: sum(val), val2 + Group Key: tab1_rr.val2 -> Seq Scan on public.tab1_rr Output: val, val2 Filter: (tab1_rr.val2 = 2) -(10 rows) +(12 rows) -- tests for node reduction by application of quals, for roundrobin node -- reduction is not applicable. Having query not FQSed because of existence of ORDER BY, @@ -354,14 +360,16 @@ explain (verbose on, nodes off, costs off) select distinct val2 from tab1_rr whe ----------------------------------------------- HashAggregate Output: val2 + Group Key: tab1_rr.val2 -> Remote Subquery Scan on all Output: val2 -> HashAggregate Output: val2 + Group Key: tab1_rr.val2 -> Seq Scan on public.tab1_rr Output: val2 Filter: (tab1_rr.val = 7) -(9 rows) +(11 rows) -- DMLs update tab1_rr set val2 = 1000 where val = 7; @@ -371,7 +379,7 @@ explain (verbose on, nodes off, costs off) update tab1_rr set val2 = 1000 where Remote Subquery Scan on all -> Update on public.tab1_rr -> Seq Scan on public.tab1_rr - Output: val, 1000, val, ctid, xc_node_id + Output: val, 1000, val, xc_node_id, ctid Filter: (tab1_rr.val = 7) (5 rows) @@ -383,13 +391,13 @@ select * from tab1_rr where val = 7; (2 rows) delete from tab1_rr where val = 7; -explain verbose delete from tab1_rr where val = 7; - QUERY PLAN ------------------------------------------------------------------------------------------- - Remote Subquery Scan on all (datanode_1,datanode_2) (cost=0.00..36.75 rows=11 width=14) - -> Delete on public.tab1_rr (cost=0.00..36.75 rows=11 width=14) - -> Seq Scan on public.tab1_rr (cost=0.00..36.75 rows=11 width=14) - Output: val, ctid, xc_node_id +explain (verbose on, costs off) delete from tab1_rr where val = 7; + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on all (datanode_1,datanode_2) + -> Delete on public.tab1_rr + -> Seq Scan on public.tab1_rr + Output: val, xc_node_id, ctid Filter: (tab1_rr.val = 7) (5 rows) @@ -410,15 +418,15 @@ insert into tab1_hash values (2, 4); insert into tab1_hash values (5, 3); insert into tab1_hash values (7, 8); insert into tab1_hash values (9, 2); -explain verbose insert into tab1_hash values (9, 2); - QUERY PLAN ----------------------------------------------------------------------------------- - Remote Subquery Scan on all (d01) (cost=0.00..0.01 rows=1 width=0) - -> Insert on public.tab1_hash (cost=0.00..0.01 rows=1 width=0) - -> Remote Subquery Scan on local node (cost=0.00..0.01 rows=1 width=0) +explain (verbose on, costs off) insert into tab1_hash values (9, 2); + QUERY PLAN +------------------------------------------------ + Remote Subquery Scan on all (datanode_1) + -> Insert on public.tab1_hash + -> Remote Subquery Scan on local node Output: 9, 2 Distribute results by H: 9 - -> Result (cost=0.00..0.01 rows=1 width=0) + -> Result Output: 9, 2 (7 rows) @@ -462,9 +470,29 @@ explain (verbose on, nodes off, costs off) select sum(val), avg(val), count(*) f -- should not get FQSed because of window functions select first_value(val) over (partition by val2 order by val) from tab1_hash; -ERROR: Window functions are not supported yet + first_value +------------- + 1 + 1 + 5 + 2 + 7 +(5 rows) + explain (verbose on, nodes off, costs off) select first_value(val) over (partition by val2 order by val) from tab1_hash; -ERROR: Window functions are not supported yet + QUERY PLAN +------------------------------------------------------- + WindowAgg + Output: first_value(val) OVER (?), val, val2 + -> Remote Subquery Scan on all + Output: val, val2 + -> Sort + Output: val, val2 + Sort Key: tab1_hash.val2, tab1_hash.val + -> Seq Scan on public.tab1_hash + Output: val, val2 +(9 rows) + -- should not get FQSed because of LIMIT clause select * from tab1_hash where val2 = 3 limit 1; val | val2 @@ -493,18 +521,16 @@ select * from tab1_hash where val2 = 4 offset 1; (0 rows) explain (verbose on, nodes off, costs off) select * from tab1_hash where val2 = 4 offset 1; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +-------------------------------------------- Limit Output: val, val2 -> Remote Subquery Scan on all Output: val, val2 - -> Limit + -> Seq Scan on public.tab1_hash Output: val, val2 - -> Seq Scan on public.tab1_hash - Output: val, val2 - Filter: (tab1_hash.val2 = 4) -(9 rows) + Filter: (tab1_hash.val2 = 4) +(7 rows) -- should not get FQSed because of SORT clause select * from tab1_hash order by val; @@ -537,16 +563,17 @@ select distinct val, val2 from tab1_hash where val2 = 8; (1 row) explain (verbose on, nodes off, costs off) select distinct val, val2 from tab1_hash where val2 = 8; - QUERY PLAN --------------------------------------------- + QUERY PLAN +-------------------------------------------------- Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_hash.val, tab1_hash.val2 -> Seq Scan on public.tab1_hash Output: val, val2 Filter: (tab1_hash.val2 = 8) -(7 rows) +(8 rows) -- should not get FQSed because of GROUP clause select val, val2 from tab1_hash where val2 = 8 group by val, val2; @@ -556,16 +583,17 @@ select val, val2 from tab1_hash where val2 = 8 group by val, val2; (1 row) explain (verbose on, nodes off, costs off) select val, val2 from tab1_hash where val2 = 8 group by val, val2; - QUERY PLAN --------------------------------------------- + QUERY PLAN +-------------------------------------------------- Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_hash.val, tab1_hash.val2 -> Seq Scan on public.tab1_hash Output: val, val2 Filter: (tab1_hash.val2 = 8) -(7 rows) +(8 rows) -- should not get FQSed because of HAVING clause select sum(val) from tab1_hash where val2 = 2 group by val2 having sum(val) > 1; @@ -579,15 +607,17 @@ explain (verbose on, nodes off, costs off) select sum(val) from tab1_hash where ------------------------------------------------------ GroupAggregate Output: pg_catalog.sum((sum(val))), val2 + Group Key: tab1_hash.val2 Filter: (pg_catalog.sum((sum(tab1_hash.val))) > 1) -> Remote Subquery Scan on all Output: sum(val), val2 -> GroupAggregate Output: sum(val), val2 + Group Key: tab1_hash.val2 -> Seq Scan on public.tab1_hash Output: val, val2 Filter: (tab1_hash.val2 = 2) -(10 rows) +(12 rows) -- tests for node reduction by application of quals. Having query FQSed because of -- existence of ORDER BY, implies that nodes got reduced. @@ -729,14 +759,16 @@ explain (verbose on, nodes off, costs off, num_nodes on) select distinct val2 fr ------------------------------------------------- HashAggregate Output: val2 + Group Key: tab1_hash.val2 -> Remote Subquery Scan on all Output: val2 -> HashAggregate Output: val2 + Group Key: tab1_hash.val2 -> Seq Scan on public.tab1_hash Output: val2 Filter: (tab1_hash.val = 7) -(9 rows) +(11 rows) -- DMLs update tab1_hash set val2 = 1000 where val = 7; @@ -746,7 +778,7 @@ explain (verbose on, nodes off, costs off) update tab1_hash set val2 = 1000 wher Remote Subquery Scan on all -> Update on public.tab1_hash -> Seq Scan on public.tab1_hash - Output: val, 1000, val, ctid, xc_node_id + Output: val, 1000, val, xc_node_id, ctid Filter: (tab1_hash.val = 7) (5 rows) @@ -758,13 +790,13 @@ select * from tab1_hash where val = 7; (2 rows) delete from tab1_hash where val = 7; -explain verbose delete from tab1_hash where val = 7; - QUERY PLAN -------------------------------------------------------------------------------- - Remote Subquery Scan on all (d01) (cost=0.00..36.75 rows=11 width=14) - -> Delete on public.tab1_hash (cost=0.00..36.75 rows=11 width=14) - -> Seq Scan on public.tab1_hash (cost=0.00..36.75 rows=11 width=14) - Output: val, ctid, xc_node_id, val +explain (verbose on, costs off) delete from tab1_hash where val = 7; + QUERY PLAN +-------------------------------------------------- + Remote Subquery Scan on all (datanode_2) + -> Delete on public.tab1_hash + -> Seq Scan on public.tab1_hash + Output: val, xc_node_id, ctid, val Filter: (tab1_hash.val = 7) (5 rows) @@ -785,15 +817,15 @@ insert into tab1_modulo values (2, 4); insert into tab1_modulo values (5, 3); insert into tab1_modulo values (7, 8); insert into tab1_modulo values (9, 2); -explain verbose insert into tab1_modulo values (9, 2); - QUERY PLAN ----------------------------------------------------------------------------------- - Remote Subquery Scan on all (d01) (cost=0.00..0.01 rows=1 width=0) - -> Insert on public.tab1_modulo (cost=0.00..0.01 rows=1 width=0) - -> Remote Subquery Scan on local node (cost=0.00..0.01 rows=1 width=0) +explain (verbose on, costs off) insert into tab1_modulo values (9, 2); + QUERY PLAN +------------------------------------------------ + Remote Subquery Scan on all (datanode_2) + -> Insert on public.tab1_modulo + -> Remote Subquery Scan on local node Output: 9, 2 Distribute results by M: 9 - -> Result (cost=0.00..0.01 rows=1 width=0) + -> Result Output: 9, 2 (7 rows) @@ -837,9 +869,29 @@ explain (verbose on, nodes off, costs off) select sum(val), avg(val), count(*) f -- should not get FQSed because of window functions select first_value(val) over (partition by val2 order by val) from tab1_modulo; -ERROR: Window functions are not supported yet + first_value +------------- + 1 + 1 + 5 + 2 + 7 +(5 rows) + explain (verbose on, nodes off, costs off) select first_value(val) over (partition by val2 order by val) from tab1_modulo; -ERROR: Window functions are not supported yet + QUERY PLAN +----------------------------------------------------------- + WindowAgg + Output: first_value(val) OVER (?), val, val2 + -> Remote Subquery Scan on all + Output: val, val2 + -> Sort + Output: val, val2 + Sort Key: tab1_modulo.val2, tab1_modulo.val + -> Seq Scan on public.tab1_modulo + Output: val, val2 +(9 rows) + -- should not get FQSed because of LIMIT clause select * from tab1_modulo where val2 = 3 limit 1; val | val2 @@ -868,18 +920,16 @@ select * from tab1_modulo where val2 = 4 offset 1; (0 rows) explain (verbose on, nodes off, costs off) select * from tab1_modulo where val2 = 4 offset 1; - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +---------------------------------------------- Limit Output: val, val2 -> Remote Subquery Scan on all Output: val, val2 - -> Limit + -> Seq Scan on public.tab1_modulo Output: val, val2 - -> Seq Scan on public.tab1_modulo - Output: val, val2 - Filter: (tab1_modulo.val2 = 4) -(9 rows) + Filter: (tab1_modulo.val2 = 4) +(7 rows) -- should not get FQSed because of SORT clause select * from tab1_modulo order by val; @@ -912,16 +962,17 @@ select distinct val, val2 from tab1_modulo where val2 = 8; (1 row) explain (verbose on, nodes off, costs off) select distinct val, val2 from tab1_modulo where val2 = 8; - QUERY PLAN ----------------------------------------------- + QUERY PLAN +------------------------------------------------------ Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_modulo.val, tab1_modulo.val2 -> Seq Scan on public.tab1_modulo Output: val, val2 Filter: (tab1_modulo.val2 = 8) -(7 rows) +(8 rows) -- should not get FQSed because of GROUP clause select val, val2 from tab1_modulo where val2 = 8 group by val, val2; @@ -931,16 +982,17 @@ select val, val2 from tab1_modulo where val2 = 8 group by val, val2; (1 row) explain (verbose on, nodes off, costs off) select val, val2 from tab1_modulo where val2 = 8 group by val, val2; - QUERY PLAN ----------------------------------------------- + QUERY PLAN +------------------------------------------------------ Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_modulo.val, tab1_modulo.val2 -> Seq Scan on public.tab1_modulo Output: val, val2 Filter: (tab1_modulo.val2 = 8) -(7 rows) +(8 rows) -- should not get FQSed because of HAVING clause select sum(val) from tab1_modulo where val2 = 2 group by val2 having sum(val) > 1; @@ -954,15 +1006,17 @@ explain (verbose on, nodes off, costs off) select sum(val) from tab1_modulo wher -------------------------------------------------------- GroupAggregate Output: pg_catalog.sum((sum(val))), val2 + Group Key: tab1_modulo.val2 Filter: (pg_catalog.sum((sum(tab1_modulo.val))) > 1) -> Remote Subquery Scan on all Output: sum(val), val2 -> GroupAggregate Output: sum(val), val2 + Group Key: tab1_modulo.val2 -> Seq Scan on public.tab1_modulo Output: val, val2 Filter: (tab1_modulo.val2 = 2) -(10 rows) +(12 rows) -- tests for node reduction by application of quals. Having query FQSed because of -- existence of ORDER BY, implies that nodes got reduced. @@ -1104,14 +1158,16 @@ explain (verbose on, nodes off, costs off, num_nodes on) select distinct val2 fr --------------------------------------------------- HashAggregate Output: val2 + Group Key: tab1_modulo.val2 -> Remote Subquery Scan on all Output: val2 -> HashAggregate Output: val2 + Group Key: tab1_modulo.val2 -> Seq Scan on public.tab1_modulo Output: val2 Filter: (tab1_modulo.val = 7) -(9 rows) +(11 rows) -- DMLs update tab1_modulo set val2 = 1000 where val = 7; @@ -1121,7 +1177,7 @@ explain (verbose on, nodes off, costs off) update tab1_modulo set val2 = 1000 wh Remote Subquery Scan on all -> Update on public.tab1_modulo -> Seq Scan on public.tab1_modulo - Output: val, 1000, val, ctid, xc_node_id + Output: val, 1000, val, xc_node_id, ctid Filter: (tab1_modulo.val = 7) (5 rows) @@ -1133,13 +1189,13 @@ select * from tab1_modulo where val = 7; (2 rows) delete from tab1_modulo where val = 7; -explain verbose delete from tab1_modulo where val = 7; - QUERY PLAN ---------------------------------------------------------------------------------- - Remote Subquery Scan on all (d01) (cost=0.00..36.75 rows=11 width=14) - -> Delete on public.tab1_modulo (cost=0.00..36.75 rows=11 width=14) - -> Seq Scan on public.tab1_modulo (cost=0.00..36.75 rows=11 width=14) - Output: val, ctid, xc_node_id, val +explain (verbose on, costs off) delete from tab1_modulo where val = 7; + QUERY PLAN +-------------------------------------------------- + Remote Subquery Scan on all (datanode_2) + -> Delete on public.tab1_modulo + -> Seq Scan on public.tab1_modulo + Output: val, xc_node_id, ctid, val Filter: (tab1_modulo.val = 7) (5 rows) @@ -1162,11 +1218,11 @@ insert into tab1_replicated values (5, 3); insert into tab1_replicated values (7, 8); insert into tab1_replicated values (9, 2); explain (verbose on, nodes off, costs off) insert into tab1_replicated values (9, 2); - QUERY PLAN ------------------------------------------------- - Remote Subquery Scan on all + QUERY PLAN +----------------------------------------- + Remote Subquery Scan on any -> Insert on public.tab1_replicated - -> Remote Subquery Scan on local node + -> Remote Subquery Scan on all Output: 9, 2 Distribute results by R -> Result @@ -1211,9 +1267,29 @@ explain (num_nodes on, verbose on, nodes off, costs off) select sum(val), avg(va (6 rows) select first_value(val) over (partition by val2 order by val) from tab1_replicated; -ERROR: Window functions are not supported yet + first_value +------------- + 1 + 1 + 5 + 2 + 7 +(5 rows) + explain (num_nodes on, verbose on, nodes off, costs off) select first_value(val) over (partition by val2 order by val) from tab1_replicated; -ERROR: Window functions are not supported yet + QUERY PLAN +------------------------------------------------------------- + WindowAgg + Output: first_value(val) OVER (?), val, val2 + -> Sort + Output: val, val2 + Sort Key: tab1_replicated.val2, tab1_replicated.val + -> Remote Subquery Scan on all + Output: val, val2 + -> Seq Scan on public.tab1_replicated + Output: val, val2 +(9 rows) + select * from tab1_replicated where val2 = 2 limit 2; val | val2 -----+------ @@ -1241,18 +1317,16 @@ select * from tab1_replicated where val2 = 4 offset 1; (0 rows) explain (num_nodes on, verbose on, nodes off, costs off) select * from tab1_replicated where val2 = 4 offset 1; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +-------------------------------------------------- Limit Output: val, val2 -> Remote Subquery Scan on all Output: val, val2 - -> Limit + -> Seq Scan on public.tab1_replicated Output: val, val2 - -> Seq Scan on public.tab1_replicated - Output: val, val2 - Filter: (tab1_replicated.val2 = 4) -(9 rows) + Filter: (tab1_replicated.val2 = 4) +(7 rows) select * from tab1_replicated order by val; val | val2 @@ -1287,15 +1361,16 @@ select distinct val, val2 from tab1_replicated; (5 rows) explain (num_nodes on, verbose on, nodes off, costs off) select distinct val, val2 from tab1_replicated; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_replicated.val, tab1_replicated.val2 -> Seq Scan on public.tab1_replicated Output: val, val2 -(6 rows) +(7 rows) select val, val2 from tab1_replicated group by val, val2; val | val2 @@ -1308,15 +1383,16 @@ select val, val2 from tab1_replicated group by val, val2; (5 rows) explain (num_nodes on, verbose on, nodes off, costs off) select val, val2 from tab1_replicated group by val, val2; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Remote Subquery Scan on all Output: val, val2 -> HashAggregate Output: val, val2 + Group Key: tab1_replicated.val, tab1_replicated.val2 -> Seq Scan on public.tab1_replicated Output: val, val2 -(6 rows) +(7 rows) select sum(val) from tab1_replicated group by val2 having sum(val) > 1; sum @@ -1334,17 +1410,18 @@ explain (num_nodes on, verbose on, nodes off, costs off) select sum(val) from ta Output: sum(val), val2 -> HashAggregate Output: sum(val), val2 + Group Key: tab1_replicated.val2 Filter: (sum(tab1_replicated.val) > 1) -> Seq Scan on public.tab1_replicated Output: val, val2 -(7 rows) +(8 rows) -- DMLs update tab1_replicated set val2 = 1000 where val = 7; explain (verbose on, nodes off, costs off) update tab1_replicated set val2 = 1000 where val = 7; QUERY PLAN ------------------------------------------------- - Remote Subquery Scan on all + Remote Subquery Scan on any -> Update on public.tab1_replicated -> Seq Scan on public.tab1_replicated Output: val, 1000, val, ctid @@ -1358,12 +1435,12 @@ select * from tab1_replicated where val = 7; (1 row) delete from tab1_replicated where val = 7; -explain verbose delete from tab1_replicated where val = 7; - QUERY PLAN -------------------------------------------------------------------------------------- - Remote Subquery Scan on all (d01) (cost=0.00..36.75 rows=11 width=10) - -> Delete on public.tab1_replicated (cost=0.00..36.75 rows=11 width=10) - -> Seq Scan on public.tab1_replicated (cost=0.00..36.75 rows=11 width=10) +explain (verbose on, costs off) delete from tab1_replicated where val = 7; + QUERY PLAN +----------------------------------------------------- + Remote Subquery Scan on any (datanode_1,datanode_2) + -> Delete on public.tab1_replicated + -> Seq Scan on public.tab1_replicated Output: val, ctid Filter: (tab1_replicated.val = 7) (5 rows) diff --git a/src/test/regress/expected/xc_FQS_join.out b/src/test/regress/expected/xc_FQS_join.out index 6f08c66aaf..f195382f0e 100644 --- a/src/test/regress/expected/xc_FQS_join.out +++ b/src/test/regress/expected/xc_FQS_join.out @@ -15,7 +15,7 @@ declare num_nodes int; begin nodenames_query := 'SELECT node_name FROM pgxc_node WHERE node_type = ''D'''; - cr_command := 'CREATE TABLE ' || tab_schema || ' DISTRIBUTE BY ' || distribution || ' TO NODE '; + cr_command := 'CREATE TABLE ' || tab_schema || ' DISTRIBUTE BY ' || distribution || ' TO NODE ('; for nodename in execute nodenames_query loop nodes := array_append(nodes, nodename); end loop; @@ -34,9 +34,11 @@ begin sep := ', '; end loop; cr_command := cr_command || nodenames; + cr_command := cr_command || ')'; if (cmd_suffix is not null) then cr_command := cr_command || ' ' || cmd_suffix; end if; + execute cr_command; end; $$; @@ -307,20 +309,34 @@ select * from tab3_rep natural join tab4_rep explain (num_nodes on, nodes off, costs off, verbose on) select * from tab3_rep natural join tab4_rep where tab3_rep.val > 2 and tab4_rep.val < 5; - QUERY PLAN ----------------------------------------------------------------------------------------------------- - Hash Join + QUERY PLAN +----------------------------------------------------------------------------------------- + Remote Subquery Scan on all Output: tab3_rep.val, tab3_rep.val2 - Hash Cond: ((tab3_rep.val = tab4_rep.val) AND (tab3_rep.val2 = tab4_rep.val2)) - -> Data Node Scan (primary node count=0, node count=1) on tab3_rep "_REMOTE_TABLE_QUERY_" + -> Merge Join Output: tab3_rep.val, tab3_rep.val2 - Remote query: SELECT val, val2 FROM ONLY tab3_rep WHERE (val > 2) - -> Hash - Output: tab4_rep.val, tab4_rep.val2 - -> Data Node Scan (primary node count=0, node count=1) on tab4_rep "_REMOTE_TABLE_QUERY_" + Merge Cond: ((tab3_rep.val = tab4_rep.val) AND (tab3_rep.val2 = tab4_rep.val2)) + -> Sort + Output: tab3_rep.val, tab3_rep.val2 + Sort Key: tab3_rep.val, tab3_rep.val2 + -> Remote Subquery Scan on all + Output: tab3_rep.val, tab3_rep.val2 + Distribute results by H: val + -> Seq Scan on public.tab3_rep + Output: tab3_rep.val, tab3_rep.val2 + Filter: (tab3_rep.val > 2) + -> Materialize Output: tab4_rep.val, tab4_rep.val2 - Remote query: SELECT val, val2 FROM ONLY tab4_rep WHERE (val < 5) -(11 rows) + -> Sort + Output: tab4_rep.val, tab4_rep.val2 + Sort Key: tab4_rep.val, tab4_rep.val2 + -> Remote Subquery Scan on all + Output: tab4_rep.val, tab4_rep.val2 + Distribute results by H: val + -> Seq Scan on public.tab4_rep + Output: tab4_rep.val, tab4_rep.val2 + Filter: (tab4_rep.val < 5) +(25 rows) -- Join involving one distributed and one replicated table, with replicated -- table existing on all nodes where distributed table exists. should be @@ -375,20 +391,29 @@ select * from tab1_mod natural join tab4_rep explain (verbose on, nodes off, costs off) select * from tab1_mod natural join tab4_rep where tab1_mod.val > 2 and tab4_rep.val < 4; - QUERY PLAN ----------------------------------------------------------------------------------- - Hash Join + QUERY PLAN +----------------------------------------------------------------------------------------- + Remote Subquery Scan on all Output: tab1_mod.val, tab1_mod.val2 - Hash Cond: ((tab1_mod.val = tab4_rep.val) AND (tab1_mod.val2 = tab4_rep.val2)) - -> Data Node Scan on tab1_mod "_REMOTE_TABLE_QUERY_" + -> Merge Join Output: tab1_mod.val, tab1_mod.val2 - Remote query: SELECT val, val2 FROM ONLY tab1_mod WHERE (val > 2) - -> Hash - Output: tab4_rep.val, tab4_rep.val2 - -> Data Node Scan on tab4_rep "_REMOTE_TABLE_QUERY_" + Merge Cond: ((tab4_rep.val = tab1_mod.val) AND (tab4_rep.val2 = tab1_mod.val2)) + -> Sort Output: tab4_rep.val, tab4_rep.val2 - Remote query: SELECT val, val2 FROM ONLY tab4_rep WHERE (val < 4) -(11 rows) + Sort Key: tab4_rep.val, tab4_rep.val2 + -> Remote Subquery Scan on all + Output: tab4_rep.val, tab4_rep.val2 + Distribute results by M: val + -> Seq Scan on public.tab4_rep + Output: tab4_rep.val, tab4_rep.val2 + Filter: (tab4_rep.val < 4) + -> Sort + Output: tab1_mod.val, tab1_mod.val2 + Sort Key: tab1_mod.val, tab1_mod.val2 + -> Seq Scan on public.tab1_mod + Output: tab1_mod.val, tab1_mod.val2 + Filter: (tab1_mod.val > 2) +(20 rows) -- Join involving two distributed tables, never shipped select * from tab1_mod natural join tab2_mod @@ -404,20 +429,31 @@ select * from tab1_mod natural join tab2_mod explain (verbose on, nodes off, costs off) select * from tab1_mod natural join tab2_mod where tab1_mod.val > 2 and tab2_mod.val < 4; - QUERY PLAN ----------------------------------------------------------------------------------- - Hash Join + QUERY PLAN +----------------------------------------------------------------------------------------- + Remote Subquery Scan on all Output: tab1_mod.val, tab1_mod.val2 - Hash Cond: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2)) - -> Data Node Scan on tab1_mod "_REMOTE_TABLE_QUERY_" + -> Merge Join Output: tab1_mod.val, tab1_mod.val2 - Remote query: SELECT val, val2 FROM ONLY tab1_mod WHERE (val > 2) - -> Hash - Output: tab2_mod.val, tab2_mod.val2 - -> Data Node Scan on tab2_mod "_REMOTE_TABLE_QUERY_" + Merge Cond: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2)) + -> Sort + Output: tab1_mod.val, tab1_mod.val2 + Sort Key: tab1_mod.val, tab1_mod.val2 + -> Seq Scan on public.tab1_mod + Output: tab1_mod.val, tab1_mod.val2 + Filter: (tab1_mod.val > 2) + -> Materialize Output: tab2_mod.val, tab2_mod.val2 - Remote query: SELECT val, val2 FROM ONLY tab2_mod WHERE (val < 4) -(11 rows) + -> Sort + Output: tab2_mod.val, tab2_mod.val2 + Sort Key: tab2_mod.val, tab2_mod.val2 + -> Remote Subquery Scan on all + Output: tab2_mod.val, tab2_mod.val2 + Distribute results by M: val + -> Seq Scan on public.tab2_mod + Output: tab2_mod.val, tab2_mod.val2 + Filter: (tab2_mod.val < 4) +(22 rows) -- Join involving a distributed table and two replicated tables, such that the -- distributed table exists only on nodes common to replicated tables, try few @@ -558,8 +594,8 @@ select * from tab1_mod natural join tab4_rep where tab1_mod.val = 1 order by tab (5 rows) explain (verbose on, nodes off, costs off, num_nodes on) select * from tab1_mod natural join tab4_rep where tab1_mod.val = 1 order by tab1_mod.val2; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Remote Subquery Scan on all Output: tab1_mod.val, tab1_mod.val2, tab1_mod.val2 -> Sort @@ -568,15 +604,21 @@ explain (verbose on, nodes off, costs off, num_nodes on) select * from tab1_mod -> Hash Join Output: tab1_mod.val, tab1_mod.val2, tab1_mod.val2 Hash Cond: (tab1_mod.val2 = tab4_rep.val2) - -> Seq Scan on public.tab1_mod + -> Remote Subquery Scan on all Output: tab1_mod.val, tab1_mod.val2 - Filter: (tab1_mod.val = 1) + Distribute results by H: val2 + -> Seq Scan on public.tab1_mod + Output: tab1_mod.val, tab1_mod.val2 + Filter: (tab1_mod.val = 1) -> Hash Output: tab4_rep.val, tab4_rep.val2 - -> Seq Scan on public.tab4_rep + -> Remote Subquery Scan on all Output: tab4_rep.val, tab4_rep.val2 - Filter: (tab4_rep.val = 1) -(16 rows) + Distribute results by H: val2 + -> Seq Scan on public.tab4_rep + Output: tab4_rep.val, tab4_rep.val2 + Filter: (tab4_rep.val = 1) +(22 rows) -- following join between distributed tables should get FQSed because both of -- them reduce to a single node @@ -597,21 +639,24 @@ explain (verbose on, nodes off, costs off, num_nodes on) select * from tab1_mod ------------------------------------------------------------------------- Remote Subquery Scan on all Output: tab1_mod.val2, tab1_mod.val, tab2_mod.val, tab1_mod.val - -> Nested Loop + -> Hash Join Output: tab1_mod.val2, tab1_mod.val, tab2_mod.val, tab1_mod.val - Join Filter: (tab1_mod.val2 = tab2_mod.val2) + Hash Cond: (tab1_mod.val2 = tab2_mod.val2) -> Remote Subquery Scan on all Output: tab1_mod.val2, tab1_mod.val - Distribute results by R + Distribute results by H: val2 -> Seq Scan on public.tab1_mod Output: tab1_mod.val2, tab1_mod.val Filter: (tab1_mod.val = 1) - -> Materialize + -> Hash Output: tab2_mod.val, tab2_mod.val2 - -> Seq Scan on public.tab2_mod + -> Remote Subquery Scan on all Output: tab2_mod.val, tab2_mod.val2 - Filter: (tab2_mod.val = 2) -(16 rows) + Distribute results by H: val2 + -> Seq Scan on public.tab2_mod + Output: tab2_mod.val, tab2_mod.val2 + Filter: (tab2_mod.val = 2) +(19 rows) -- JOIN involving the distributed table with equi-JOIN on the distributed column -- with same kind of distribution on same nodes. @@ -647,24 +692,23 @@ select * from tab1_mod, tab3_mod where tab1_mod.val = tab3_mod.val and tab1_mod. explain (verbose on, nodes off, costs off) select * from tab1_mod, tab3_mod where tab1_mod.val = tab3_mod.val and tab1_mod.val = 1; - QUERY PLAN --------------------------------------------------------------------------- - Remote Subquery Scan on all + QUERY PLAN +-------------------------------------------------------------------- + Nested Loop Output: tab1_mod.val, tab1_mod.val2, tab3_mod.val, tab3_mod.val2 - -> Nested Loop - Output: tab1_mod.val, tab1_mod.val2, tab3_mod.val, tab3_mod.val2 - -> Remote Subquery Scan on all + -> Remote Subquery Scan on all + Output: tab1_mod.val, tab1_mod.val2 + -> Seq Scan on public.tab1_mod Output: tab1_mod.val, tab1_mod.val2 - Distribute results by R - -> Seq Scan on public.tab1_mod - Output: tab1_mod.val, tab1_mod.val2 - Filter: (tab1_mod.val = 1) - -> Materialize + Filter: (tab1_mod.val = 1) + -> Materialize + Output: tab3_mod.val, tab3_mod.val2 + -> Remote Subquery Scan on all Output: tab3_mod.val, tab3_mod.val2 -> Seq Scan on public.tab3_mod Output: tab3_mod.val, tab3_mod.val2 Filter: (tab3_mod.val = 1) -(15 rows) +(14 rows) -- DMLs involving JOINs are not FQSed explain (verbose on, nodes off, costs off) update tab1_mod set val2 = 1000 from tab2_mod @@ -674,98 +718,93 @@ explain (verbose on, nodes off, costs off) update tab1_mod set val2 = 1000 from Remote Subquery Scan on all -> Update on public.tab1_mod -> Merge Join - Output: tab1_mod.val, 1000, tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id, tab2_mod.ctid + Output: tab1_mod.val, 1000, tab1_mod.val, tab1_mod.val2, tab1_mod.xc_node_id, tab1_mod.ctid, tab2_mod.ctid Merge Cond: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2)) -> Sort - Output: tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id + Output: tab1_mod.val, tab1_mod.val2, tab1_mod.xc_node_id, tab1_mod.ctid Sort Key: tab1_mod.val, tab1_mod.val2 -> Seq Scan on public.tab1_mod - Output: tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id - -> Sort + Output: tab1_mod.val, tab1_mod.val2, tab1_mod.xc_node_id, tab1_mod.ctid + -> Materialize Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 - Sort Key: tab2_mod.val, tab2_mod.val2 - -> Seq Scan on public.tab2_mod + -> Sort Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 -(15 rows) + Sort Key: tab2_mod.val, tab2_mod.val2 + -> Remote Subquery Scan on all + Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 + Distribute results by M: val + -> Seq Scan on public.tab2_mod + Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 +(20 rows) explain (verbose on, nodes off, costs off) delete from tab1_mod using tab2_mod where tab1_mod.val = tab2_mod.val and tab1_mod.val2 = tab2_mod.val2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- - Update on public.tab1_mod - Node expr: tab1_mod.val - Remote query: UPDATE ONLY public.tab1_mod SET val2 = $2 WHERE ctid = $5 AND xc_node_id = $6 - -> Hash Join - Output: tab1_mod.val, 1000, tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id, tab2_mod.ctid - Hash Cond: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2)) - -> Data Node Scan on tab1_mod "_REMOTE_TABLE_QUERY_" - Output: tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id - Remote query: SELECT val, val2, ctid, xc_node_id FROM ONLY tab1_mod WHERE true - -> Hash - Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 - -> Data Node Scan on tab2_mod "_REMOTE_TABLE_QUERY_" - Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 - Remote query: SELECT ctid, val, val2 FROM ONLY tab2_mod WHERE true -(14 rows) - -explain (costs off, verbose on, nodes off) delete from tab1_mod using tab2_mod - where tab1_mod.val = tab2_mod.val and tab1_mod.val2 = tab2_mod.val2; - QUERY PLAN ------------------------------------------------------------------------------------------------- - Delete on public.tab1_mod - Remote query: DELETE FROM ONLY public.tab1_mod WHERE ctid = $3 AND xc_node_id = $4 - -> Hash Join - Output: tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id, tab2_mod.ctid - Hash Cond: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2)) - -> Data Node Scan on tab1_mod "_REMOTE_TABLE_QUERY_" - Output: tab1_mod.val, tab1_mod.val2, tab1_mod.ctid, tab1_mod.xc_node_id - Remote query: SELECT val, val2, ctid, xc_node_id FROM ONLY tab1_mod WHERE true - -> Hash - Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 - -> Data Node Scan on tab2_mod "_REMOTE_TABLE_QUERY_" + Remote Subquery Scan on all + -> Delete on public.tab1_mod + -> Merge Join + Output: tab1_mod.val, tab1_mod.val2, tab1_mod.xc_node_id, tab1_mod.ctid, tab1_mod.val, tab2_mod.ctid + Merge Cond: ((tab1_mod.val = tab2_mod.val) AND (tab1_mod.val2 = tab2_mod.val2)) + -> Sort + Output: tab1_mod.val, tab1_mod.val2, tab1_mod.xc_node_id, tab1_mod.ctid + Sort Key: tab1_mod.val, tab1_mod.val2 + -> Seq Scan on public.tab1_mod + Output: tab1_mod.val, tab1_mod.val2, tab1_mod.xc_node_id, tab1_mod.ctid + -> Materialize Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 - Remote query: SELECT ctid, val, val2 FROM ONLY tab2_mod WHERE true -(13 rows) - -explain (costs off, verbose on, nodes off) update tab1_rep set val2 = 1000 from tab2_rep + -> Sort + Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 + Sort Key: tab2_mod.val, tab2_mod.val2 + -> Remote Subquery Scan on all + Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 + Distribute results by M: val + -> Seq Scan on public.tab2_mod + Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 +(20 rows) + +explain (verbose on, nodes off, costs off) update tab1_rep set val2 = 1000 from tab2_rep where tab1_rep.val = tab2_rep.val and tab1_rep.val2 = tab2_rep.val2; - QUERY PLAN ------------------------------------------------------------------------------------------------ - Update on public.tab1_rep - Remote query: UPDATE ONLY public.tab1_rep SET val2 = $2 WHERE ctid = $5 - -> Hash Join - Output: tab1_rep.val, 1000, tab1_rep.val, tab1_rep.val2, tab1_rep.ctid, tab2_rep.ctid - Hash Cond: ((tab1_rep.val = tab2_rep.val) AND (tab1_rep.val2 = tab2_rep.val2)) - -> Data Node Scan on tab1_rep "_REMOTE_TABLE_QUERY_" - Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid - Remote query: SELECT val, val2, ctid FROM ONLY tab1_rep WHERE true - -> Hash - Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 - -> Data Node Scan on tab2_rep "_REMOTE_TABLE_QUERY_" + QUERY PLAN +----------------------------------------------------------------------------------------------------- + Remote Subquery Scan on any + -> Update on public.tab1_rep + -> Merge Join + Output: tab1_rep.val, 1000, tab1_rep.val, tab1_rep.val2, tab1_rep.ctid, tab2_rep.ctid + Merge Cond: ((tab1_rep.val = tab2_rep.val) AND (tab1_rep.val2 = tab2_rep.val2)) + -> Sort + Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid + Sort Key: tab1_rep.val, tab1_rep.val2 + -> Seq Scan on public.tab1_rep + Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid + -> Sort Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 - Remote query: SELECT ctid, val, val2 FROM ONLY tab2_rep WHERE true -(13 rows) + Sort Key: tab2_rep.val, tab2_rep.val2 + -> Seq Scan on public.tab2_rep + Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 +(15 rows) -explain (costs off, verbose on, nodes off) delete from tab1_rep using tab2_rep +explain (verbose on, nodes off, costs off) delete from tab1_rep using tab2_rep where tab1_rep.val = tab2_rep.val and tab1_rep.val2 = tab2_rep.val2; - QUERY PLAN ----------------------------------------------------------------------------------------- - Delete on public.tab1_rep - Remote query: DELETE FROM ONLY public.tab1_rep WHERE ctid = $3 - -> Hash Join - Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid, tab2_rep.ctid - Hash Cond: ((tab1_rep.val = tab2_rep.val) AND (tab1_rep.val2 = tab2_rep.val2)) - -> Data Node Scan on tab1_rep "_REMOTE_TABLE_QUERY_" - Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid - Remote query: SELECT val, val2, ctid FROM ONLY tab1_rep WHERE true - -> Hash - Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 - -> Data Node Scan on tab2_rep "_REMOTE_TABLE_QUERY_" + QUERY PLAN +----------------------------------------------------------------------------------------------- + Remote Subquery Scan on any + -> Delete on public.tab1_rep + -> Merge Join + Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid, tab2_rep.ctid + Merge Cond: ((tab1_rep.val = tab2_rep.val) AND (tab1_rep.val2 = tab2_rep.val2)) + -> Sort + Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid + Sort Key: tab1_rep.val, tab1_rep.val2 + -> Seq Scan on public.tab1_rep + Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid + -> Sort Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 - Remote query: SELECT ctid, val, val2 FROM ONLY tab2_rep WHERE true -(13 rows) + Sort Key: tab2_rep.val, tab2_rep.val2 + -> Seq Scan on public.tab2_rep + Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 +(15 rows) -reset enable_remotejoin; drop table tab1_rep; drop table tab2_rep; drop table tab3_rep; diff --git a/src/test/regress/sql/xc_FQS.sql b/src/test/regress/sql/xc_FQS.sql index b7e01805cb..ef1e9dc21c 100644 --- a/src/test/regress/sql/xc_FQS.sql +++ b/src/test/regress/sql/xc_FQS.sql @@ -1,3 +1,42 @@ +-- A function to create table on specified nodes +create or replace function cr_table(tab_schema varchar, nodenums int[], distribution varchar) +returns void language plpgsql as $$ +declare + cr_command varchar; + nodes varchar[]; + nodename varchar; + nodenames_query varchar; + nodenames varchar; + node int; + sep varchar; + tmp_node int; + num_nodes int; +begin + nodenames_query := 'SELECT node_name FROM pgxc_node WHERE node_type = ''D'''; + cr_command := 'CREATE TABLE ' || tab_schema || ' DISTRIBUTE BY ' || distribution || ' TO NODE ('; + for nodename in execute nodenames_query loop + nodes := array_append(nodes, nodename); + end loop; + nodenames := ''; + sep := ''; + num_nodes := array_length(nodes, 1); + foreach node in array nodenums loop + tmp_node := node; + if (tmp_node < 1 or tmp_node > num_nodes) then + tmp_node := tmp_node % num_nodes; + if (tmp_node < 1) then + tmp_node := num_nodes; + end if; + end if; + nodenames := nodenames || sep || nodes[tmp_node]; + sep := ', '; + end loop; + cr_command := cr_command || nodenames; + cr_command := cr_command || ')'; + + execute cr_command; +end; +$$; -- This file contains tests for Fast Query Shipping (FQS) for queries involving -- a single table @@ -64,7 +103,7 @@ update tab1_rr set val2 = 1000 where val = 7; explain (verbose on, nodes off, costs off) update tab1_rr set val2 = 1000 where val = 7; select * from tab1_rr where val = 7; delete from tab1_rr where val = 7; -explain verbose delete from tab1_rr where val = 7; +explain (verbose on, costs off) delete from tab1_rr where val = 7; select * from tab1_rr where val = 7; -- Testset 2 for distributed tables (by hash) @@ -74,7 +113,7 @@ insert into tab1_hash values (2, 4); insert into tab1_hash values (5, 3); insert into tab1_hash values (7, 8); insert into tab1_hash values (9, 2); -explain verbose insert into tab1_hash values (9, 2); +explain (verbose on, costs off) insert into tab1_hash values (9, 2); -- simple select -- should get FQSed select val, val2 + 2, case val when val2 then 'val and val2 are same' else 'val and val2 are not same' end from tab1_hash where val2 = 4; @@ -129,7 +168,7 @@ update tab1_hash set val2 = 1000 where val = 7; explain (verbose on, nodes off, costs off) update tab1_hash set val2 = 1000 where val = 7; select * from tab1_hash where val = 7; delete from tab1_hash where val = 7; -explain verbose delete from tab1_hash where val = 7; +explain (verbose on, costs off) delete from tab1_hash where val = 7; select * from tab1_hash where val = 7; -- Testset 3 for distributed tables (by modulo) @@ -139,7 +178,7 @@ insert into tab1_modulo values (2, 4); insert into tab1_modulo values (5, 3); insert into tab1_modulo values (7, 8); insert into tab1_modulo values (9, 2); -explain verbose insert into tab1_modulo values (9, 2); +explain (verbose on, costs off) insert into tab1_modulo values (9, 2); -- simple select -- should get FQSed select val, val2 + 2, case val when val2 then 'val and val2 are same' else 'val and val2 are not same' end from tab1_modulo where val2 = 4; @@ -194,7 +233,7 @@ update tab1_modulo set val2 = 1000 where val = 7; explain (verbose on, nodes off, costs off) update tab1_modulo set val2 = 1000 where val = 7; select * from tab1_modulo where val = 7; delete from tab1_modulo where val = 7; -explain verbose delete from tab1_modulo where val = 7; +explain (verbose on, costs off) delete from tab1_modulo where val = 7; select * from tab1_modulo where val = 7; -- Testset 4 for replicated tables, for replicated tables, unless the expression @@ -230,7 +269,7 @@ update tab1_replicated set val2 = 1000 where val = 7; explain (verbose on, nodes off, costs off) update tab1_replicated set val2 = 1000 where val = 7; select * from tab1_replicated where val = 7; delete from tab1_replicated where val = 7; -explain verbose delete from tab1_replicated where val = 7; +explain (verbose on, costs off) delete from tab1_replicated where val = 7; select * from tab1_replicated where val = 7; drop table tab1_rr; diff --git a/src/test/regress/sql/xc_FQS_join.sql b/src/test/regress/sql/xc_FQS_join.sql index 3adfd87e5e..b4464079ca 100644 --- a/src/test/regress/sql/xc_FQS_join.sql +++ b/src/test/regress/sql/xc_FQS_join.sql @@ -15,7 +15,7 @@ declare num_nodes int; begin nodenames_query := 'SELECT node_name FROM pgxc_node WHERE node_type = ''D'''; - cr_command := 'CREATE TABLE ' || tab_schema || ' DISTRIBUTE BY ' || distribution || ' TO NODE '; + cr_command := 'CREATE TABLE ' || tab_schema || ' DISTRIBUTE BY ' || distribution || ' TO NODE ('; for nodename in execute nodenames_query loop nodes := array_append(nodes, nodename); end loop; @@ -34,9 +34,11 @@ begin sep := ', '; end loop; cr_command := cr_command || nodenames; + cr_command := cr_command || ')'; if (cmd_suffix is not null) then cr_command := cr_command || ' ' || cmd_suffix; end if; + execute cr_command; end; $$; |