From 2d29155679eb98e46f8a47b6dff89231445fb9d6 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Tue, 17 Oct 2017 20:12:49 +0200 Subject: [PATCH] Remove coordinator quals, evaluated at Remote Subquery While rewriting UPDATE/DELETE commands in rewriteTargetListUD, we've been pulling all Vars from quals, and adding them to target lists. As multiple Vars may reference the same column, this sometimes produced plans with duplicate targetlist entries like this one: Update on public.t111 -> Index Scan using t1_a_idx on public.t1 Output: 100, t1.b, t1.c, t1.a, t1.a, t1.a, t1.a, t1.a, t1.a, t1.a, t1.a, t1.ctid -> ... Getting rid of the duplicate entries would be simple - before adding entry for eachh Vars, check that a matching entry does not exist yet. The question however is if we actually need any of this. The comment in rewriteTargetListUD() claims we need to add the Vars because of "coordinator quals" - which is not really defined anywhere, but it probably means quals evaluated at the Remote Subquery node. But we push all quals to the remote node, so there should not be any cases where a qual would have to be evaluated locally (or where that would be preferable). So just remove all the relevant code from rewriteHandler.c, which means we produce this plan instead: Update on public.t111 -> Index Scan using t1_a_idx on public.t1 Output: 100, t1.b, t1.c, t1.ctid -> ... This affects a number of plans in regression tests, but the changes seem fine - we simply remove unnecessary target list entries. I've also added an assert to EXPLAIN enforcing the "no quals" rule for Remote Subquery nodes. Discussion: <95e80368-1549-a921-c5e2-7e0ad9485bd3@2ndquadrant.com> --- src/backend/commands/explain.c | 2 +- src/backend/rewrite/rewriteHandler.c | 113 ------------------ src/test/regress/expected/domain.out | 6 +- src/test/regress/expected/xc_FQS.out | 36 +++--- src/test/regress/expected/xc_FQS_join.out | 36 +++--- src/test/regress/expected/xc_alter_table.out | 18 +-- src/test/regress/expected/xc_remote.out | 34 +++--- .../regress/expected/xl_plan_pushdown.out | 20 ++-- 8 files changed, 76 insertions(+), 189 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index a698e78a47..597e47f53a 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -1514,7 +1514,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_RemoteQuery: /* Remote query */ ExplainRemoteQuery((RemoteQuery *)plan, planstate, ancestors, es); - show_scan_qual(plan->qual, "Coordinator quals", planstate, ancestors, es); + Assert(!plan->qual); break; #endif #ifdef XCP diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index a033b7ff53..d2788b58c5 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -96,17 +96,6 @@ static Query *fireRIRrules(Query *parsetree, List *activeRIRs, static bool view_has_instead_trigger(Relation view, CmdType event); static Bitmapset *adjust_view_column_set(Bitmapset *cols, List *targetlist); -#ifdef PGXC -typedef struct pull_qual_vars_context -{ - List *varlist; - int sublevels_up; - int resultRelation; -} pull_qual_vars_context; -static List * pull_qual_vars(Node *node, int varno); -static bool pull_qual_vars_walker(Node *node, pull_qual_vars_context *context); -#endif - /* * AcquireRewriteLocks - * Acquire suitable locks on all the relations mentioned in the Query. @@ -1308,69 +1297,6 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos) rte->values_lists = newValues; } - -#ifdef PGXC -/* - * pull_qual_vars(Node *node, int varno) - * Extract vars from quals belonging to resultRelation. This function is mainly - * taken from pull_qual_vars_clause(), but since the later does not peek into - * subquery, we need to write this walker. - */ -static List * -pull_qual_vars(Node *node, int varno) -{ - pull_qual_vars_context context; - context.varlist = NIL; - context.sublevels_up = 0; - context.resultRelation = varno; - - query_or_expression_tree_walker(node, - pull_qual_vars_walker, - (void *) &context, - 0); - return context.varlist; -} - -static bool -pull_qual_vars_walker(Node *node, pull_qual_vars_context *context) -{ - if (node == NULL) - return false; - if (IsA(node, Var)) - { - Var *var = (Var *) node; - - /* - * Add only if this var belongs to the resultRelation and refers to the table - * from the same query. - */ - if (var->varno == context->resultRelation && - var->varlevelsup == context->sublevels_up) - { - Var *newvar = palloc(sizeof(Var)); - *newvar = *var; - newvar->varlevelsup = 0; - context->varlist = lappend(context->varlist, newvar); - } - return false; - } - if (IsA(node, Query)) - { - /* Recurse into RTE subquery or not-yet-planned sublink subquery */ - bool result; - - context->sublevels_up++; - result = query_tree_walker((Query *) node, pull_qual_vars_walker, - (void *) context, 0); - context->sublevels_up--; - return result; - } - return expression_tree_walker(node, pull_qual_vars_walker, - (void *) context); -} - -#endif /* PGXC */ - /* * rewriteTargetListUD - rewrite UPDATE/DELETE targetlist as needed * @@ -1392,45 +1318,6 @@ rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte, const char *attrname; TargetEntry *tle; -#ifdef PGXC - List *var_list = NIL; - ListCell *elt; - - /* - * In Postgres-XC, we need to evaluate quals of the parse tree and determine - * if they are Coordinator quals. If they are, their attribute need to be - * added to target list for evaluation. In case some are found, add them as - * junks in the target list. The junk status will be used by remote UPDATE - * planning to associate correct element to a clause. - * For DELETE, having such columns in target list helps to evaluate Quals - * correctly on Coordinator. - * PGXCTODO: This list could be reduced to keep only in target list the - * vars using Coordinator Quals. - */ - if (IS_PGXC_COORDINATOR && parsetree->jointree) - var_list = pull_qual_vars((Node *) parsetree->jointree, parsetree->resultRelation); - - foreach(elt, var_list) - { - Form_pg_attribute att_tup; - int numattrs = RelationGetNumberOfAttributes(target_relation); - - var = (Var *) lfirst(elt); - /* Bypass in case of extra target items like ctid */ - if (var->varattno < 1 || var->varattno > numattrs) - continue; - - - att_tup = target_relation->rd_att->attrs[var->varattno - 1]; - tle = makeTargetEntry((Expr *) var, - list_length(parsetree->targetList) + 1, - pstrdup(NameStr(att_tup->attname)), - true); - - parsetree->targetList = lappend(parsetree->targetList, tle); - } -#endif - #ifdef PGXC /* * If relation is non-replicated, we need also to identify the Datanode diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 70f15cc04c..bc750dce72 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -284,12 +284,12 @@ select * from dcomptable; explain (verbose, costs off) update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 where d1[1].i > 0; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------- Remote Subquery Scan on any (datanode_1,datanode_2) -> Update on public.dcomptable -> Seq Scan on public.dcomptable - Output: (d1[1].r := (d1[1].r - '1'::double precision))[1].i := (d1[1].i + '1'::double precision), d1, ctid + Output: (d1[1].r := (d1[1].r - '1'::double precision))[1].i := (d1[1].i + '1'::double precision), ctid Filter: (dcomptable.d1[1].i > '0'::double precision) (5 rows) diff --git a/src/test/regress/expected/xc_FQS.out b/src/test/regress/expected/xc_FQS.out index 6e839e7bc3..6fe9458781 100644 --- a/src/test/regress/expected/xc_FQS.out +++ b/src/test/regress/expected/xc_FQS.out @@ -419,7 +419,7 @@ explain (verbose on, nodes off, costs off) update tab1_rr set val2 = 1000 where QUERY PLAN ---------------------------------------------------------------- Remote Fast Query Execution - Output: 1000, tab1_rr.val, tab1_rr.xc_node_id, tab1_rr.ctid + Output: 1000, tab1_rr.xc_node_id, tab1_rr.ctid Remote query: UPDATE tab1_rr SET val2 = 1000 WHERE (val = 7) -> Update on public.tab1_rr -> Seq Scan on public.tab1_rr @@ -436,10 +436,10 @@ select * from tab1_rr where val = 7; delete from tab1_rr where val = 7; explain (verbose on, costs off) delete from tab1_rr where val = 7; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Remote Fast Query Execution - Output: tab1_rr.val, tab1_rr.xc_node_id, tab1_rr.ctid + Output: tab1_rr.xc_node_id, tab1_rr.ctid Node/s: datanode_1, datanode_2 Remote query: DELETE FROM tab1_rr WHERE (val = 7) -> Delete on public.tab1_rr @@ -832,10 +832,10 @@ explain (verbose on, nodes off, costs off, num_nodes on) select distinct val2 fr -- DMLs 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; - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------ Remote Fast Query Execution - Output: 1000, tab1_hash.val, tab1_hash.xc_node_id, tab1_hash.ctid + Output: 1000, tab1_hash.xc_node_id, tab1_hash.ctid Remote query: UPDATE tab1_hash SET val2 = 1000 WHERE (val = 7) -> Update on public.tab1_hash -> Seq Scan on public.tab1_hash @@ -852,10 +852,10 @@ select * from tab1_hash where val = 7; delete from tab1_hash where val = 7; explain (verbose on, costs off) delete from tab1_hash where val = 7; - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------- Remote Fast Query Execution - Output: tab1_hash.val, tab1_hash.xc_node_id, tab1_hash.ctid + Output: tab1_hash.xc_node_id, tab1_hash.ctid Node/s: datanode_2 Remote query: DELETE FROM tab1_hash WHERE (val = 7) -> Delete on public.tab1_hash @@ -1248,10 +1248,10 @@ explain (verbose on, nodes off, costs off, num_nodes on) select distinct val2 fr -- DMLs 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; - QUERY PLAN ---------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------- Remote Fast Query Execution - Output: 1000, tab1_modulo.val, tab1_modulo.xc_node_id, tab1_modulo.ctid + Output: 1000, tab1_modulo.xc_node_id, tab1_modulo.ctid Remote query: UPDATE tab1_modulo SET val2 = 1000 WHERE (val = 7) -> Update on public.tab1_modulo -> Seq Scan on public.tab1_modulo @@ -1268,10 +1268,10 @@ select * from tab1_modulo where val = 7; delete from tab1_modulo where val = 7; explain (verbose on, costs off) delete from tab1_modulo where val = 7; - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Remote Fast Query Execution - Output: tab1_modulo.val, tab1_modulo.xc_node_id, tab1_modulo.ctid + Output: tab1_modulo.xc_node_id, tab1_modulo.ctid Node/s: datanode_2 Remote query: DELETE FROM tab1_modulo WHERE (val = 7) -> Delete on public.tab1_modulo @@ -1558,7 +1558,7 @@ explain (verbose on, nodes off, costs off) update tab1_replicated set val2 = 100 QUERY PLAN ------------------------------------------------------------------------ Remote Fast Query Execution - Output: 1000, tab1_replicated.val, tab1_replicated.ctid + Output: 1000, tab1_replicated.ctid Remote query: UPDATE tab1_replicated SET val2 = 1000 WHERE (val = 7) -> Update on public.tab1_replicated -> Seq Scan on public.tab1_replicated @@ -1577,7 +1577,7 @@ explain (verbose on, costs off) delete from tab1_replicated where val = 7; QUERY PLAN ------------------------------------------------------------- Remote Fast Query Execution - Output: tab1_replicated.val, tab1_replicated.ctid + Output: tab1_replicated.ctid Node/s: datanode_1, datanode_2 Remote query: DELETE FROM tab1_replicated WHERE (val = 7) -> Delete on public.tab1_replicated diff --git a/src/test/regress/expected/xc_FQS_join.out b/src/test/regress/expected/xc_FQS_join.out index 3c265150c6..3b0ff79493 100644 --- a/src/test/regress/expected/xc_FQS_join.out +++ b/src/test/regress/expected/xc_FQS_join.out @@ -715,18 +715,18 @@ explain (verbose on, nodes off, costs off) select * from tab1_mod, tab3_mod -- DMLs involving JOINs are not FQSed explain (verbose on, nodes off, costs off) update tab1_mod set val2 = 1000 from tab2_mod where tab1_mod.val = tab2_mod.val and tab1_mod. val2 = tab2_mod.val2; - QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------- 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.xc_node_id, tab1_mod.ctid, tab2_mod.ctid + Output: tab1_mod.val, 1000, 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.xc_node_id, tab1_mod.ctid + Output: tab1_mod.val, tab1_mod.xc_node_id, tab1_mod.ctid, tab1_mod.val2 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 + Output: tab1_mod.val, tab1_mod.xc_node_id, tab1_mod.ctid, tab1_mod.val2 -> Materialize Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 -> Remote Subquery Scan on all @@ -741,18 +741,18 @@ explain (verbose on, nodes off, costs off) update tab1_mod set val2 = 1000 from 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 --------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------- 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 + Output: 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 + Output: tab1_mod.xc_node_id, tab1_mod.ctid, 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, tab1_mod.xc_node_id, tab1_mod.ctid + Output: tab1_mod.xc_node_id, tab1_mod.ctid, tab1_mod.val, tab1_mod.val2 -> Materialize Output: tab2_mod.ctid, tab2_mod.val, tab2_mod.val2 -> Remote Subquery Scan on all @@ -767,18 +767,18 @@ explain (verbose on, nodes off, costs off) delete from tab1_mod using tab2_mod 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 ------------------------------------------------------------------------------------------------------ + 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 + Output: tab1_rep.val, 1000, 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 + Output: tab1_rep.val, tab1_rep.ctid, tab1_rep.val2 Sort Key: tab1_rep.val, tab1_rep.val2 -> Seq Scan on public.tab1_rep - Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid + Output: tab1_rep.val, tab1_rep.ctid, tab1_rep.val2 -> Sort Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 Sort Key: tab2_rep.val, tab2_rep.val2 @@ -793,13 +793,13 @@ explain (verbose on, nodes off, costs off) delete from tab1_rep using tab2_rep 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 + Output: 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 + Output: tab1_rep.ctid, tab1_rep.val, tab1_rep.val2 Sort Key: tab1_rep.val, tab1_rep.val2 -> Seq Scan on public.tab1_rep - Output: tab1_rep.val, tab1_rep.val2, tab1_rep.ctid + Output: tab1_rep.ctid, tab1_rep.val, tab1_rep.val2 -> Sort Output: tab2_rep.ctid, tab2_rep.val, tab2_rep.val2 Sort Key: tab2_rep.val, tab2_rep.val2 diff --git a/src/test/regress/expected/xc_alter_table.out b/src/test/regress/expected/xc_alter_table.out index a12a118919..2450c21440 100644 --- a/src/test/regress/expected/xc_alter_table.out +++ b/src/test/regress/expected/xc_alter_table.out @@ -82,12 +82,12 @@ SELECT id, name FROM xc_alter_table_1 ORDER BY 1; -- Check for query generation of remote UPDATE EXPLAIN (VERBOSE true, COSTS false, NODES false) UPDATE xc_alter_table_1 SET name = 'zzz' WHERE id = currval('xc_alter_table_1_id_seq'); - QUERY PLAN ---------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------- Remote Subquery Scan on all -> Update on public.xc_alter_table_1 -> Seq Scan on public.xc_alter_table_1 - Output: id, 'zzz'::character varying(80), NULL::integer, id, xc_node_id, ctid + Output: id, 'zzz'::character varying(80), NULL::integer, xc_node_id, ctid Filter: (xc_alter_table_1.id = currval('xc_alter_table_1_id_seq'::regclass)) (5 rows) @@ -147,12 +147,12 @@ SELECT b, c FROM xc_alter_table_2 ORDER BY b; -- Check for query generation of remote UPDATE EXPLAIN (VERBOSE true, COSTS false, NODES false) UPDATE xc_alter_table_2 SET b = 'Morphee', c = false WHERE b = 'Neo'; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ Remote Subquery Scan on any -> Update on public.xc_alter_table_2 -> Seq Scan on public.xc_alter_table_2 - Output: NULL::integer, 'Morphee'::character varying(20), false, NULL::integer, NULL::integer, b, ctid + Output: NULL::integer, 'Morphee'::character varying(20), false, NULL::integer, NULL::integer, ctid Filter: ((xc_alter_table_2.b)::text = 'Neo'::text) (5 rows) @@ -192,12 +192,12 @@ SELECT a, a2, b, c FROM xc_alter_table_2 ORDER BY b; -- Check for query generation of remote UPDATE EXPLAIN (VERBOSE true, COSTS false, NODES false) UPDATE xc_alter_table_2 SET a = 200, a2 = 'CTO' WHERE b = 'John'; - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------ Remote Subquery Scan on any -> Update on public.xc_alter_table_2 -> Seq Scan on public.xc_alter_table_2 - Output: NULL::integer, b, c, NULL::integer, NULL::integer, 200, 'CTO'::character varying(20), b, ctid + Output: NULL::integer, b, c, NULL::integer, NULL::integer, 200, 'CTO'::character varying(20), ctid Filter: ((xc_alter_table_2.b)::text = 'John'::text) (5 rows) diff --git a/src/test/regress/expected/xc_remote.out b/src/test/regress/expected/xc_remote.out index 7afeeb3903..e367697cdb 100644 --- a/src/test/regress/expected/xc_remote.out +++ b/src/test/regress/expected/xc_remote.out @@ -360,12 +360,12 @@ $$begin return 3;end $$ language plpgsql; \set stmt 'update xcrem_employee E set salary = salary + salary + 0.3 * bonus WHERE SALARY > ( SELECT AVG(SALARY) FROM xcrem_employee WHERE SUBSTRING(E.WORKDEPT,1,1) = SUBSTRING(WORKDEPT, 1,1) )' :stmt; :EXP :stmt; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Remote Subquery Scan on all -> Update on public.xcrem_employee e -> Seq Scan on public.xcrem_employee e - Output: e.empno, e.firstname, e.midinit, e.lastname, e.workdept, e.phoneno, e.hiredate, e.job, e.edlevel, e.sex, e.birthdate, ((e.salary + e.salary) + (0.3 * e.bonus)), e.bonus, e.comm, e.salary, e.workdept, e.xc_node_id, e.ctid + Output: e.empno, e.firstname, e.midinit, e.lastname, e.workdept, e.phoneno, e.hiredate, e.job, e.edlevel, e.sex, e.birthdate, ((e.salary + e.salary) + (0.3 * e.bonus)), e.bonus, e.comm, e.xc_node_id, e.ctid Filter: (e.salary > (SubPlan 1)) SubPlan 1 -> Aggregate @@ -401,12 +401,12 @@ $$begin return 3;end $$ language plpgsql; \set stmt 'update xcrem_employee E set bonus = bonus + salary* 0.3 WHERE EDLEVEL > ( SELECT AVG(EDLEVEL) FROM xcrem_employee WHERE WORKDEPT = E.WORKDEPT )' :stmt; :EXP :stmt; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Remote Subquery Scan on all -> Update on public.xcrem_employee e -> Seq Scan on public.xcrem_employee e - Output: e.empno, e.firstname, e.midinit, e.lastname, e.workdept, e.phoneno, e.hiredate, e.job, e.edlevel, e.sex, e.birthdate, e.salary, (e.bonus + (e.salary * 0.3)), e.comm, e.edlevel, e.workdept, e.xc_node_id, e.ctid + Output: e.empno, e.firstname, e.midinit, e.lastname, e.workdept, e.phoneno, e.hiredate, e.job, e.edlevel, e.sex, e.birthdate, e.salary, (e.bonus + (e.salary * 0.3)), e.comm, e.xc_node_id, e.ctid Filter: ((e.edlevel)::numeric > (SubPlan 1)) SubPlan 1 -> Aggregate @@ -442,12 +442,12 @@ $$begin return 3;end $$ language plpgsql; \set stmt 'update xcrem_employee E set lastname = lastname || ''suf'' WHERE EDLEVEL > volatile_func(2)' :stmt; :EXP :stmt; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Remote Subquery Scan on all -> Update on public.xcrem_employee e -> Seq Scan on public.xcrem_employee e - Output: empno, firstname, midinit, ((lastname)::text || 'suf'::text), workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm, edlevel, xc_node_id, ctid + Output: empno, firstname, midinit, ((lastname)::text || 'suf'::text), workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm, xc_node_id, ctid Filter: (e.edlevel > volatile_func(2)) (5 rows) @@ -475,12 +475,12 @@ $$begin return 3;end $$ language plpgsql; \set stmt 'update xcrem_employee E set lastname = lastname || ''suf'', edlevel = edlevel+1 WHERE EDLEVEL > volatile_func(2)' :stmt; :EXP :stmt; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Remote Subquery Scan on all -> Update on public.xcrem_employee e -> Seq Scan on public.xcrem_employee e - Output: empno, firstname, midinit, ((lastname)::text || 'suf'::text), workdept, phoneno, hiredate, job, (edlevel + 1), sex, birthdate, salary, bonus, comm, edlevel, xc_node_id, ctid + Output: empno, firstname, midinit, ((lastname)::text || 'suf'::text), workdept, phoneno, hiredate, job, (edlevel + 1), sex, birthdate, salary, bonus, comm, xc_node_id, ctid Filter: (e.edlevel > volatile_func(2)) (5 rows) @@ -512,12 +512,12 @@ insert into xcrem_employee select * from xcrem_temptable; \set stmt 'DELETE FROM xcrem_employee E WHERE EDLEVEL > volatile_func(2)' :stmt; :EXP :stmt; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------------------ Remote Subquery Scan on all -> Delete on public.xcrem_employee e -> Seq Scan on public.xcrem_employee e - Output: edlevel, xc_node_id, ctid, empno + Output: xc_node_id, ctid, empno Filter: (e.edlevel > volatile_func(2)) (5 rows) @@ -536,7 +536,7 @@ insert into xcrem_employee select * from xcrem_temptable; Remote Subquery Scan on all -> Delete on public.xcrem_employee e -> Seq Scan on public.xcrem_employee e - Output: e.edlevel, e.workdept, e.xc_node_id, e.ctid, e.empno + Output: e.xc_node_id, e.ctid, e.empno Filter: ((e.edlevel)::numeric > (SubPlan 1)) SubPlan 1 -> Aggregate @@ -574,7 +574,7 @@ insert into xcrem_employee select * from xcrem_temptable; Remote Subquery Scan on all -> Delete on public.xcrem_employee e -> Seq Scan on public.xcrem_employee e - Output: e.salary, e.workdept, e.xc_node_id, e.ctid, e.empno + Output: e.xc_node_id, e.ctid, e.empno Filter: (e.salary > (SubPlan 1)) SubPlan 1 -> Aggregate diff --git a/src/test/regress/expected/xl_plan_pushdown.out b/src/test/regress/expected/xl_plan_pushdown.out index b854b1e39d..2abb66f794 100755 --- a/src/test/regress/expected/xl_plan_pushdown.out +++ b/src/test/regress/expected/xl_plan_pushdown.out @@ -54,7 +54,7 @@ EXPLAIN VERBOSE UPDATE xl_pp SET b=2 where a=200; QUERY PLAN --------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: 2, xl_pp.a, xl_pp.xc_node_id, xl_pp.ctid + Output: 2, xl_pp.xc_node_id, xl_pp.ctid Node/s: datanode_1 Remote query: UPDATE xl_pp SET b = 2 WHERE (a = 200) -> Update on public.xl_pp (cost=0.00..35.50 rows=10 width=18) @@ -67,7 +67,7 @@ EXPLAIN VERBOSE UPDATE xl_pp SET b=2 where a=200::bigint; QUERY PLAN --------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: 2, xl_pp.a, xl_pp.xc_node_id, xl_pp.ctid + Output: 2, xl_pp.xc_node_id, xl_pp.ctid Node/s: datanode_1 Remote query: UPDATE xl_pp SET b = 2 WHERE (a = (200)::bigint) -> Update on public.xl_pp (cost=0.00..35.50 rows=10 width=18) @@ -80,7 +80,7 @@ EXPLAIN VERBOSE DELETE FROM xl_pp where a=200; QUERY PLAN -------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: xl_pp.a, xl_pp.xc_node_id, xl_pp.ctid + Output: xl_pp.xc_node_id, xl_pp.ctid Node/s: datanode_1 Remote query: DELETE FROM xl_pp WHERE (a = 200) -> Delete on public.xl_pp (cost=0.00..35.50 rows=10 width=6) @@ -103,7 +103,7 @@ EXPLAIN VERBOSE DELETE FROM xl_pp where a=200; QUERY PLAN -------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: xl_pp.a, xl_pp.xc_node_id, xl_pp.ctid + Output: xl_pp.xc_node_id, xl_pp.ctid Node/s: datanode_1 Remote query: DELETE FROM xl_pp WHERE (a = 200) -> Delete on public.xl_pp (cost=0.00..35.50 rows=10 width=6) @@ -116,7 +116,7 @@ EXPLAIN VERBOSE DELETE FROM xl_pp where a=200::bigint; QUERY PLAN -------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: xl_pp.a, xl_pp.xc_node_id, xl_pp.ctid + Output: xl_pp.xc_node_id, xl_pp.ctid Node/s: datanode_1 Remote query: DELETE FROM xl_pp WHERE (a = (200)::bigint) -> Delete on public.xl_pp (cost=0.00..35.50 rows=10 width=6) @@ -168,7 +168,7 @@ EXPLAIN VERBOSE UPDATE xl_ppm SET b=2 where a=200; QUERY PLAN ---------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: 2, xl_ppm.a, xl_ppm.xc_node_id, xl_ppm.ctid + Output: 2, xl_ppm.xc_node_id, xl_ppm.ctid Node/s: datanode_1 Remote query: UPDATE xl_ppm SET b = 2 WHERE (a = 200) -> Update on public.xl_ppm (cost=0.00..40.00 rows=12 width=12) @@ -181,7 +181,7 @@ EXPLAIN VERBOSE UPDATE xl_ppm SET b=2 where a=200::INT2; QUERY PLAN ---------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: 2, xl_ppm.a, xl_ppm.xc_node_id, xl_ppm.ctid + Output: 2, xl_ppm.xc_node_id, xl_ppm.ctid Node/s: datanode_1 Remote query: UPDATE xl_ppm SET b = 2 WHERE (a = (200)::smallint) -> Update on public.xl_ppm (cost=0.00..40.00 rows=12 width=12) @@ -194,7 +194,7 @@ EXPLAIN VERBOSE DELETE FROM xl_ppm where a=200; QUERY PLAN --------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: xl_ppm.a, xl_ppm.xc_node_id, xl_ppm.ctid + Output: xl_ppm.xc_node_id, xl_ppm.ctid Node/s: datanode_1 Remote query: DELETE FROM xl_ppm WHERE (a = 200) -> Delete on public.xl_ppm (cost=0.00..40.00 rows=12 width=6) @@ -217,7 +217,7 @@ EXPLAIN VERBOSE DELETE FROM xl_ppm where a=200; QUERY PLAN --------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: xl_ppm.a, xl_ppm.xc_node_id, xl_ppm.ctid + Output: xl_ppm.xc_node_id, xl_ppm.ctid Node/s: datanode_1 Remote query: DELETE FROM xl_ppm WHERE (a = 200) -> Delete on public.xl_ppm (cost=0.00..40.00 rows=12 width=6) @@ -230,7 +230,7 @@ EXPLAIN VERBOSE DELETE FROM xl_ppm where a=200::INT2; QUERY PLAN --------------------------------------------------------------------------- Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) - Output: xl_ppm.a, xl_ppm.xc_node_id, xl_ppm.ctid + Output: xl_ppm.xc_node_id, xl_ppm.ctid Node/s: datanode_1 Remote query: DELETE FROM xl_ppm WHERE (a = (200)::smallint) -> Delete on public.xl_ppm (cost=0.00..40.00 rows=12 width=6) -- 2.30.2