This is an automated email from the ASF dual-hosted git repository. maxyang pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit a0f39821178fcc226aef53784f9f5b59dfe50c5b Author: zhoujiaqi <[email protected]> AuthorDate: Mon Dec 30 11:21:29 2024 +0800 ORCA dsisable the update and delete on partitioned tables After CBDB merged with PG14, we need to provide additional support for the case where the update/delete is a dynamic (any) scan operator. This is because in `ExecModifyTable`, PG14 has an assumption: the current `resultRelInfo` will not be a root partition table. The current commit disables the update/delete generated by ORCA on the partition table, and this behavior will be supported in subsequent changes. --- src/backend/executor/nodeModifyTable.c | 13 +- .../gpopt/translate/CTranslatorQueryToDXL.cpp | 16 +++ src/include/nodes/plannodes.h | 1 + .../isolation2/expected/lockmodes_optimizer.out | 48 +++---- .../regress/expected/DML_over_joins_optimizer.out | 158 ++++++++++++++++++++ src/test/regress/expected/bfv_partition_plans.out | 103 ++++--------- .../expected/bfv_partition_plans_optimizer.out | 97 +++++++------ .../regress/expected/direct_dispatch_optimizer.out | 26 ++-- src/test/regress/expected/inherit_optimizer.out | 17 +-- .../regress/expected/partition_prune_optimizer.out | 160 +++++++++++---------- .../regress/expected/qp_dml_joins_optimizer.out | 9 +- 11 files changed, 390 insertions(+), 258 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index a9ee111273..1cbc017a81 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -2483,8 +2483,7 @@ ExecPrepareTupleRouting(ModifyTableState *mtstate, * valid target for INSERTs. This is required since a partitioned table * UPDATE to another partition becomes a DELETE+INSERT. */ - if (partRelInfo) - partrel = ExecFindPartition(mtstate, targetRelInfo, proute, slot, estate); + partrel = ExecFindPartition(mtstate, targetRelInfo, proute, slot, estate); /* * If we're capturing transition tuples, we might need to convert from the @@ -2515,8 +2514,7 @@ ExecPrepareTupleRouting(ModifyTableState *mtstate, slot = execute_attr_map_slot(map->attrMap, slot, new_slot); } - if (partRelInfo) - *partRelInfo = partrel; + *partRelInfo = partrel; return slot; } @@ -2829,10 +2827,6 @@ ExecModifyTable(PlanState *pstate) estate, node->canSetTag, false /* splitUpdate */); break; case CMD_UPDATE: - /* Prepare for tuple routing if needed. */ - if (castNode(ModifyTable, node->ps.plan)->forceTupleRouting) - slot = ExecPrepareTupleRouting(node, estate, proute, - resultRelInfo, slot, NULL); if (!AttributeNumberIsValid(action_attno)) { /* normal non-split UPDATE */ @@ -2895,9 +2889,6 @@ ExecModifyTable(PlanState *pstate) ereport(ERROR, (errmsg("unknown action = %d", action))); break; case CMD_DELETE: - if (castNode(ModifyTable, node->ps.plan)->forceTupleRouting) - planSlot = ExecPrepareTupleRouting(node, estate, proute, - resultRelInfo, slot, NULL); slot = ExecDelete(node, resultRelInfo, tupleid, oldtuple, planSlot, &node->mt_epqstate, estate, segid, diff --git a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp index 28e3679bcc..c4dbd9dad7 100644 --- a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp +++ b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp @@ -1188,6 +1188,14 @@ CTranslatorQueryToDXL::TranslateDeleteQueryToDXL() &m_context->m_has_distributed_tables); const IMDRelation *md_rel = m_md_accessor->RetrieveRel(table_descr->MDId()); + // CBDB_MERGE_FIXME: Support DML operations on partitioned tables + if (md_rel->IsPartitioned()) + { + // GPDB_12_MERGE_FIXME: Support DML operations on partitioned tables + GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature, + GPOS_WSZ_LIT("DML(delete) on partitioned tables")); + } + // make note of the operator classes used in the distribution key NoteDistributionPolicyOpclasses(rte); @@ -1255,6 +1263,14 @@ CTranslatorQueryToDXL::TranslateUpdateQueryToDXL() GPOS_WSZ_LIT("UPDATE with constraints")); } + // CBDB_MERGE_FIXME: Support DML operations on partitioned tables + if (md_rel->IsPartitioned()) + { + // GPDB_12_MERGE_FIXME: Support DML operations on partitioned tables + GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature, + GPOS_WSZ_LIT("DML(update) on partitioned tables")); + } + // make note of the operator classes used in the distribution key NoteDistributionPolicyOpclasses(rte); diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index e32cc36558..9afa2bec1b 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -424,6 +424,7 @@ typedef struct ModifyTable Node *onConflictWhere; /* WHERE for ON CONFLICT UPDATE */ Index exclRelRTI; /* RTI of the EXCLUDED pseudo relation */ List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */ + /* CBDB_CHERRYPICK_FIXME: need enable it */ bool forceTupleRouting; /* dynamic scans require tuple routing */ } ModifyTable; diff --git a/src/test/isolation2/expected/lockmodes_optimizer.out b/src/test/isolation2/expected/lockmodes_optimizer.out index fe29f29b3c..ed247a8a1b 100644 --- a/src/test/isolation2/expected/lockmodes_optimizer.out +++ b/src/test/isolation2/expected/lockmodes_optimizer.out @@ -1041,12 +1041,12 @@ BEGIN DELETE 10 -- on QD, there's a lock on the root and the target partition 1: select * from show_locks_lockmodes; - locktype | mode | granted | relation -----------+-----------------+---------+--------------------------------- - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_2 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml - relation | ExclusiveLock | t | t_lockmods_part_tbl_dml + locktype | mode | granted | relation +----------+-----------------+---------+------------------------------------- + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 + relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 (4 rows) 1: ROLLBACK; ROLLBACK @@ -1080,12 +1080,12 @@ BEGIN UPDATE 1 -- on QD, there's a lock on the root and the target partition 1: select * from show_locks_lockmodes; - locktype | mode | granted | relation -----------+-----------------+---------+--------------------------------- - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_2 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml - relation | ExclusiveLock | t | t_lockmods_part_tbl_dml + locktype | mode | granted | relation +----------+-----------------+---------+------------------------------------- + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 + relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del + relation | ExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 (4 rows) 1: ROLLBACK; ROLLBACK @@ -2131,12 +2131,12 @@ BEGIN DELETE 10 -- on QD, there's a lock on the root and the target partition 1: select * from show_locks_lockmodes; - locktype | mode | granted | relation -----------+------------------+---------+--------------------------------- - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_2 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml - relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml + locktype | mode | granted | relation +----------+------------------+---------+------------------------------------- + relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 + relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 + relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del + relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del (4 rows) 1: ROLLBACK; ROLLBACK @@ -2148,12 +2148,12 @@ BEGIN UPDATE 1 -- on QD, there's a lock on the root and the target partition 1: select * from show_locks_lockmodes; - locktype | mode | granted | relation -----------+------------------+---------+--------------------------------- - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_2 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml_1_prt_1 - relation | AccessShareLock | t | t_lockmods_part_tbl_dml - relation | RowExclusiveLock | t | t_lockmods_part_tbl_dml + locktype | mode | granted | relation +----------+------------------+---------+------------------------------------- + relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_2 + relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del_1_prt_1 + relation | AccessShareLock | t | t_lockmods_part_tbl_upd_del + relation | RowExclusiveLock | t | t_lockmods_part_tbl_upd_del (4 rows) 1: ROLLBACK; ROLLBACK diff --git a/src/test/regress/expected/DML_over_joins_optimizer.out b/src/test/regress/expected/DML_over_joins_optimizer.out index 61387bfb52..c0fadaeae7 100644 --- a/src/test/regress/expected/DML_over_joins_optimizer.out +++ b/src/test/regress/expected/DML_over_joins_optimizer.out @@ -425,7 +425,11 @@ delete from s where b = (select min(a) from r where b = s.b); -- Update partition key (requires moving tuples from one partition to another) ------------------------------------------------------------ update p set c = c + 1 where c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables update p set c = c + 1 where b in (select b from s) and c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select tableoid::regclass, c, count(*) from p group by 1, 2; tableoid | c | count ---------------+---+------- @@ -470,7 +474,11 @@ delete from s where b = (select min(a) from r where b = s.b); -- Update partition key (requires moving tuples from one partition to another) ------------------------------------------------------------ update p set c = c + 1 where c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables update p set c = c + 1 where b in (select b from s where b = 36); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select tableoid::regclass, c, count(*) from p group by 1, 2; tableoid | c | count ---------------+---+------- @@ -515,7 +523,11 @@ delete from s where b = (select min(a) from r where b = s.b); -- Update partition key (requires moving tuples from one partition to another) ------------------------------------------------------------ update p set c = c + 1 where c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables update p set c = c + 1 where b in (select b from s) and c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select tableoid::regclass, c, count(*) from p group by 1, 2; tableoid | c | count ---------------+---+------- @@ -560,7 +572,11 @@ delete from s where b = (select min(a) from r where b = s.b); -- Update partition key (requires moving tuples from one partition to another) ------------------------------------------------------------ update p set c = c + 1 where c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables update p set c = c + 1 where b in (select b from s) and c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select tableoid::regclass, c, count(*) from p group by 1, 2; tableoid | c | count ---------------+---+------- @@ -605,7 +621,11 @@ delete from s where b = (select min(a) from r where b = s.b); -- Update partition key (requires moving tuples from one partition to another) ------------------------------------------------------------ update p set c = c + 1 where c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables update p set c = c + 1 where b in (select b from s) and c = 0; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select tableoid::regclass, c, count(*) from p group by 1, 2; tableoid | c | count ---------------+---+------- @@ -647,14 +667,24 @@ DETAIL: Feature not supported: Unexpected target list entries in ProjectSet nod -- Motion on p, append node, hash agg ------------------------------------------------------------ update p set b = b + 1 where a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables delete from p where p.a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.b and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- Updates with motion: -- No motion, colocated distribution key ------------------------------------------------------------ delete from p where a in (select a from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.a and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- No motion of s ------------------------------------------------------------ @@ -708,14 +738,24 @@ DETAIL: Feature not supported: Unexpected target list entries in ProjectSet nod -- Motion on p, append node, hash agg ------------------------------------------------------------ update p set b = b + 1 where a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables delete from p where p.a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.b and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- Updates with motion: -- No motion, colocated distribution key ------------------------------------------------------------ delete from p where a in (select a from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.a and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- No motion of s ------------------------------------------------------------ @@ -769,14 +809,24 @@ DETAIL: Feature not supported: Unexpected target list entries in ProjectSet nod -- Motion on p, append node, hash agg ------------------------------------------------------------ update p set b = b + 1 where a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables delete from p where p.a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.b and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- Updates with motion: -- No motion, colocated distribution key ------------------------------------------------------------ delete from p where a in (select a from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.a and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- No motion of s ------------------------------------------------------------ @@ -830,14 +880,24 @@ DETAIL: Feature not supported: Unexpected target list entries in ProjectSet nod -- Motion on p, append node, hash agg ------------------------------------------------------------ update p set b = b + 1 where a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables delete from p where p.a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.b and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- Updates with motion: -- No motion, colocated distribution key ------------------------------------------------------------ delete from p where a in (select a from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.a and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- No motion of s ------------------------------------------------------------ @@ -891,14 +951,24 @@ DETAIL: Feature not supported: Unexpected target list entries in ProjectSet nod -- Motion on p, append node, hash agg ------------------------------------------------------------ update p set b = b + 1 where a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables delete from p where p.a in (select b from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.b and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- Updates with motion: -- No motion, colocated distribution key ------------------------------------------------------------ delete from p where a in (select a from r where a = p.c); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables delete from p using r where p.a = r.a and r.a = p.c; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables ------------------------------------------------------------ -- No motion of s ------------------------------------------------------------ @@ -1296,6 +1366,8 @@ select r.* from r,s,sales where s.a = sales.day and sales.month = r.b-1; delete from r; delete from s; delete from sales_par; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3; INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node @@ -1319,6 +1391,8 @@ select sales_par.* from sales_par where id in (select s.b from s, r where s.a = (2 rows) update sales_par set region = 'new_region' where id in (select s.b from s, r where s.a = r.b) and day in (select a from r); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select sales_par.* from sales_par where id in (select s.b from s, r where s.a = r.b) and day in (select a from r); id | year | month | day | region ----+------+-------+-----+------------ @@ -1337,6 +1411,8 @@ select sales_par.* from sales_par,s,r where sales_par.id = s.b and sales_par.mon (5 rows) update sales_par set month = month+1 from r,s where sales_par.id = s.b and sales_par.month = r.b+1; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select sales_par.* from sales_par,s,r where sales_par.id = s.b and sales_par.month = r.b+2; id | year | month | day | region ----+------+-------+-----+------------ @@ -1355,6 +1431,8 @@ select sales_par.* from sales_par where id in (select s.b-1 from s,r where s.a = (2 rows) delete from sales_par where id in (select s.b-1 from s,r where s.a = r.b); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables select sales_par.* from sales_par where id in (select s.b-1 from s,r where s.a = r.b); id | year | month | day | region ----+------+-------+-----+-------- @@ -1583,6 +1661,8 @@ DETAIL: Feature not supported: Multi-level partitioned tables delete from s; delete from m; delete from sales_par; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables insert into s select generate_series(1, 100), generate_series(1, 100) * 3; INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node @@ -1646,6 +1726,8 @@ select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) (4 rows) update sales_par set month = month+1 from s where sales_par.id in (s.b, s.b+1) and region = 'europe'; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) and region='europe'; id | year | month | day | region ----+------+-------+-----+-------- @@ -1663,6 +1745,8 @@ select * from sales_par where region='asia' and id in (select b from s where a = (1 row) delete from sales_par where region='asia' and id in (select b from s where a = 1); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables select * from sales_par where region='asia' and id in (select b from s where a = 1); id | year | month | day | region ----+------+-------+-----+-------- @@ -1675,6 +1759,8 @@ select * from sales_par where region='asia' and id in (select b from m where a = (1 row) delete from sales_par where region='asia' and id in (select b from m where a = 2); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables select * from sales_par where region='asia' and id in (select b from m where a = 2); id | year | month | day | region ----+------+-------+-----+-------- @@ -1687,6 +1773,8 @@ select * from sales_par where region='asia' and id in (select b from m where a = delete from s; delete from m; delete from sales_par; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables insert into s select generate_series(1, 100), generate_series(1, 100) * 3; INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node @@ -1753,6 +1841,8 @@ select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) PREPARE plan3 AS update sales_par set month = month+1 from s where sales_par.id in (s.b, s.b+1) and region = 'europe'; EXECUTE plan3; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select distinct sales_par.* from sales_par,s where sales_par.id in (s.b, s.b+1) and region='europe'; id | year | month | day | region ----+------+-------+-----+-------- @@ -1771,6 +1861,8 @@ select * from sales_par where region='asia' and id in (select b from s where a = PREPARE plan4 AS delete from sales_par where region='asia' and id in (select b from s where a = 1); EXECUTE plan4; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables select * from sales_par where region='asia' and id in (select b from s where a = 1); id | year | month | day | region ----+------+-------+-----+-------- @@ -1784,6 +1876,8 @@ select * from sales_par where region='asia' and id in (select b from m where a = PREPARE plan5 AS delete from sales_par where region='asia' and id in (select b from m where a = 2); EXECUTE plan5; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables select * from sales_par where region='asia' and id in (select b from m where a = 2); id | year | month | day | region ----+------+-------+-----+-------- @@ -1917,83 +2011,147 @@ insert into part_neq_dis_2 select generate_series(1,40), generate_series(1,40); -- T1 - distribution partitioned column, T2 - distributed partitioned column -- a) non-default partition delete from part_eq_dis_1 where a = (select c from part_eq_dis_2 where c=1); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where a = (select c from part_eq_dis_2 where c=21); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - distribution partitioned column, T2 - non-distributed non-partitioned column -- a) non-default partition delete from part_eq_dis_1 where a = (select d from part_eq_dis_2 where c=2); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where a = (select d from part_eq_dis_2 where c=22); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - distribution partitioned column, T2 - distributed non-partitioned column -- a) non-default partition delete from part_eq_dis_1 where a = (select c from part_neq_dis_2 where c=3); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where a = (select c from part_neq_dis_2 where c=23); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - distribution partitioned column, T2 - non-distributed partitioned column -- a) non-default partition delete from part_eq_dis_1 where a = (select d from part_neq_dis_2 where c=4); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where a = (select d from part_neq_dis_2 where c=24); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution non-partitioned column, T2 - distributed partitioned column -- a) non-default partition delete from part_eq_dis_1 where b = (select c from part_eq_dis_2 where c=5); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where b = (select c from part_eq_dis_2 where c=25); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution non-partitioned column, T2 - non-distributed non-partitioned column -- a) non-default partition delete from part_eq_dis_1 where b = (select d from part_eq_dis_2 where c=6); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where b = (select d from part_eq_dis_2 where c=26); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution non-partitioned column, T2 - distributed non-partitioned column -- a) non-default partition delete from part_eq_dis_1 where b = (select c from part_neq_dis_2 where c=7); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where b = (select c from part_neq_dis_2 where c=27); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution non-partitioned column, T2 - non-distributed partitioned column -- a) non-default partition delete from part_eq_dis_1 where b = (select d from part_neq_dis_2 where c=8); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_eq_dis_1 where b = (select d from part_neq_dis_2 where c=28); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - distribution non-partitioned column, T2 - distributed partitioned column -- a) non-default partition delete from part_neq_dis_1 where a = (select c from part_eq_dis_2 where c=9); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where a = (select c from part_eq_dis_2 where c=29); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - distribution non-partitioned column, T2 - non-distributed non-partitioned column -- a) non-default partition delete from part_neq_dis_1 where a = (select d from part_eq_dis_2 where c=10); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where a = (select d from part_eq_dis_2 where c=30); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - distribution non-partitioned column, T2 - non-distributed partitioned column -- a) non-default partition delete from part_neq_dis_1 where a = (select d from part_neq_dis_2 where c=11); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where a = (select d from part_neq_dis_2 where c=31); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - distribution non-partitioned column, T2 - distributed non-partitioned column -- a) non-default partition delete from part_neq_dis_1 where a = (select c from part_neq_dis_2 where c=12); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where a = (select c from part_neq_dis_2 where c=32); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution partitioned column, T2 - distributed partitioned column -- a) non-default partition delete from part_neq_dis_1 where b = (select c from part_eq_dis_2 where c=13); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where b = (select c from part_eq_dis_2 where c=33); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution partitioned column, T2 - non-distributed non-partitioned column -- a) non-default partition delete from part_neq_dis_1 where b = (select d from part_eq_dis_2 where c=14); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where b = (select d from part_eq_dis_2 where c=34); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution partitioned column, T2 - distributed non-partitioned column -- a) non-default partition delete from part_neq_dis_1 where b = (select d from part_neq_dis_2 where c=15); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where b = (select d from part_neq_dis_2 where c=35); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- T1 - non-distribution partitioned column, T2 - non-distributed partitioned column -- a) non-default partition delete from part_neq_dis_1 where b = (select c from part_neq_dis_2 where c=16); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables -- b) default partition delete from part_neq_dis_1 where b = (select c from part_neq_dis_2 where c=36); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables select * from part_eq_dis_1; a | b ----+---- diff --git a/src/test/regress/expected/bfv_partition_plans.out b/src/test/regress/expected/bfv_partition_plans.out index 2ca7d3f9dd..f5ac4f3c5b 100644 --- a/src/test/regress/expected/bfv_partition_plans.out +++ b/src/test/regress/expected/bfv_partition_plans.out @@ -1177,11 +1177,11 @@ HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sur CREATE INDEX index_delete_from_indexed_pt ON delete_from_indexed_pt USING bitmap(b); INSERT INTO delete_from_indexed_pt SELECT i, i%6 FROM generate_series(1, 10)i; EXPLAIN (COSTS OFF) DELETE FROM delete_from_indexed_pt WHERE b=1; - QUERY PLAN ------------------------------------------------------------------------ + QUERY PLAN +----------------------------------------------------------------------------------- Delete on delete_from_indexed_pt - Delete on delete_from_indexed_pt_1_prt_1 - -> Bitmap Heap Scan on delete_from_indexed_pt_1_prt_1 + Delete on delete_from_indexed_pt_1_prt_1 delete_from_indexed_pt_1 + -> Bitmap Heap Scan on delete_from_indexed_pt_1_prt_1 delete_from_indexed_pt_1 Recheck Cond: (b = 1) -> Bitmap Index Scan on delete_from_indexed_pt_1_prt_1_b_idx Index Cond: (b = 1) @@ -1213,84 +1213,39 @@ INSERT INTO delete_from_pt SELECT i, i%6 FROM generate_series(1, 10)i; INSERT INTO t VALUES (1); ANALYZE delete_from_pt, t; EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF, ANALYZE) DELETE FROM delete_from_pt WHERE b IN (SELECT b FROM delete_from_pt, t WHERE t.a=delete_from_pt.b); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Delete on delete_from_pt (actual rows=0 loops=1) - Delete on delete_from_pt_1_prt_1 - Delete on delete_from_pt_1_prt_2 - Delete on delete_from_pt_1_prt_3 - -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (actual rows=1 loops=1) - -> Hash Semi Join (actual rows=2 loops=1) - Hash Cond: (delete_from_pt_1_prt_1.b = t.a) - Extra Text: (seg1) Hash chain length 2.0 avg, 2 max, using 1 of 65536 buckets. - -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=3 loops=1) - Hash Key: delete_from_pt_1_prt_1.b - -> Seq Scan on delete_from_pt_1_prt_1 (actual rows=3 loops=1) - -> Hash (actual rows=2 loops=1) - Buckets: 65536 Batches: 1 Memory Usage: 513kB - -> Redistribute Motion 3:3 (slice3; segments: 3) (actual rows=2 loops=1) - Hash Key: t.a - -> Hash Join (actual rows=1 loops=1) - Hash Cond: (delete_from_pt_1_prt_1_1.b = t.a) - Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 65536 buckets. - -> Append (actual rows=3 loops=1) - Partition Selectors: $1 - -> Seq Scan on delete_from_pt_1_prt_1 delete_from_pt_1_prt_1_1 (actual rows=3 loops=1) - -> Seq Scan on delete_from_pt_1_prt_2 delete_from_pt_1_prt_2_1 (never executed) - -> Seq Scan on delete_from_pt_1_prt_3 delete_from_pt_1_prt_3_1 (never executed) - -> Hash (actual rows=1 loops=1) - Buckets: 65536 Batches: 1 Memory Usage: 513kB - -> Partition Selector (selector id: $1) (actual rows=1 loops=1) - -> Broadcast Motion 3:3 (slice4; segments: 3) (actual rows=1 loops=1) - -> Seq Scan on t (actual rows=1 loops=1) - -> Explicit Redistribute Motion 3:3 (slice5; segments: 3) (actual rows=0 loops=1) - -> Hash Semi Join (actual rows=0 loops=1) - Hash Cond: (delete_from_pt_1_prt_2.b = t.a) - -> Redistribute Motion 3:3 (slice6; segments: 3) (actual rows=0 loops=1) - Hash Key: delete_from_pt_1_prt_2.b - -> Seq Scan on delete_from_pt_1_prt_2 (actual rows=3 loops=1) - -> Hash (actual rows=2 loops=1) - Buckets: 65536 Batches: 1 Memory Usage: 513kB - -> Redistribute Motion 3:3 (slice7; segments: 3) (actual rows=2 loops=1) - Hash Key: t.a + Delete on delete_from_pt_1_prt_1 delete_from_pt_2 + Delete on delete_from_pt_1_prt_2 delete_from_pt_3 + Delete on delete_from_pt_1_prt_3 delete_from_pt_4 + -> Hash Semi Join (actual rows=1 loops=1) + Hash Cond: (delete_from_pt.b = t.a) + Extra Text: (seg0) Hash chain length 2.0 avg, 2 max, using 1 of 131072 buckets. + -> Append (actual rows=3 loops=1) + Partition Selectors: $1 + -> Seq Scan on delete_from_pt_1_prt_1 delete_from_pt_2 (actual rows=3 loops=1) + -> Seq Scan on delete_from_pt_1_prt_2 delete_from_pt_3 (never executed) + -> Seq Scan on delete_from_pt_1_prt_3 delete_from_pt_4 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> Partition Selector (selector id: $1) (actual rows=2 loops=1) + -> Broadcast Motion 3:3 (slice1; segments: 3) (actual rows=2 loops=1) -> Hash Join (actual rows=1 loops=1) - Hash Cond: (delete_from_pt_1_prt_1_1.b = t.a) - Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 65536 buckets. + Hash Cond: (delete_from_pt_1.b = t.a) + Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. -> Append (actual rows=3 loops=1) Partition Selectors: $2 - -> Seq Scan on delete_from_pt_1_prt_1 delete_from_pt_1_prt_1_1 (actual rows=3 loops=1) - -> Seq Scan on delete_from_pt_1_prt_2 delete_from_pt_1_prt_2_1 (never executed) - -> Seq Scan on delete_from_pt_1_prt_3 delete_from_pt_1_prt_3_1 (never executed) + -> Seq Scan on delete_from_pt_1_prt_1 delete_from_pt_5 (actual rows=3 loops=1) + -> Seq Scan on delete_from_pt_1_prt_2 delete_from_pt_6 (never executed) + -> Seq Scan on delete_from_pt_1_prt_3 delete_from_pt_7 (never executed) -> Hash (actual rows=1 loops=1) - Buckets: 65536 Batches: 1 Memory Usage: 513kB + Buckets: 262144 Batches: 1 Memory Usage: 2049kB -> Partition Selector (selector id: $2) (actual rows=1 loops=1) - -> Broadcast Motion 3:3 (slice8; segments: 3) (actual rows=1 loops=1) - -> Seq Scan on t (actual rows=1 loops=1) - -> Explicit Redistribute Motion 3:3 (slice9; segments: 3) (actual rows=0 loops=1) - -> Hash Semi Join (actual rows=0 loops=1) - Hash Cond: (delete_from_pt_1_prt_3.b = t.a) - -> Redistribute Motion 3:3 (slice10; segments: 3) (actual rows=0 loops=1) - Hash Key: delete_from_pt_1_prt_3.b - -> Seq Scan on delete_from_pt_1_prt_3 (actual rows=0 loops=1) - -> Hash (actual rows=2 loops=1) - Buckets: 65536 Batches: 1 Memory Usage: 513kB - -> Redistribute Motion 3:3 (slice11; segments: 3) (actual rows=2 loops=1) - Hash Key: t.a - -> Hash Join (actual rows=1 loops=1) - Hash Cond: (delete_from_pt_1_prt_1_1.b = t.a) - Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 65536 buckets. - -> Append (actual rows=3 loops=1) - Partition Selectors: $3 - -> Seq Scan on delete_from_pt_1_prt_1 delete_from_pt_1_prt_1_1 (actual rows=3 loops=1) - -> Seq Scan on delete_from_pt_1_prt_2 delete_from_pt_1_prt_2_1 (never executed) - -> Seq Scan on delete_from_pt_1_prt_3 delete_from_pt_1_prt_3_1 (never executed) - -> Hash (actual rows=1 loops=1) - Buckets: 65536 Batches: 1 Memory Usage: 513kB - -> Partition Selector (selector id: $3) (actual rows=1 loops=1) - -> Broadcast Motion 3:3 (slice12; segments: 3) (actual rows=1 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) -> Seq Scan on t (actual rows=1 loops=1) Optimizer: Postgres query optimizer -(75 rows) +(30 rows) SELECT * FROM delete_from_pt order by a; a | b diff --git a/src/test/regress/expected/bfv_partition_plans_optimizer.out b/src/test/regress/expected/bfv_partition_plans_optimizer.out index b5e8b866ff..9d5be75da2 100644 --- a/src/test/regress/expected/bfv_partition_plans_optimizer.out +++ b/src/test/regress/expected/bfv_partition_plans_optimizer.out @@ -56,6 +56,8 @@ NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. insert into mpp3061 values(1); update mpp3061 set i = 2 where i = 1; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(update) on partitioned tables select tableoid::regclass, * from mpp3061 where i = 2; tableoid | i -----------------+--- @@ -1225,6 +1227,8 @@ NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution select count_operator('delete from mpp6247_foo using mpp6247_bar where mpp6247_foo.c1 = mpp6247_bar.c1 and mpp6247_foo.dt = ''2009-05-03''', 'Hash Join'); INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: SIRV functions +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables count_operator ---------------- 1 @@ -1239,16 +1243,22 @@ HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sur CREATE INDEX index_delete_from_indexed_pt ON delete_from_indexed_pt USING bitmap(b); INSERT INTO delete_from_indexed_pt SELECT i, i%6 FROM generate_series(1, 10)i; EXPLAIN (COSTS OFF) DELETE FROM delete_from_indexed_pt WHERE b=1; - QUERY PLAN ---------------------------------------------------------------------------- +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables + QUERY PLAN +----------------------------------------------------------------------------------- Delete on delete_from_indexed_pt - -> Dynamic Seq Scan on delete_from_indexed_pt delete_from_indexed_pt_1 - Number of partitions to scan: 1 (out of 3) - Filter: (b = 1) - Optimizer: Pivotal Optimizer (GPORCA) -(5 rows) + Delete on delete_from_indexed_pt_1_prt_1 delete_from_indexed_pt_1 + -> Bitmap Heap Scan on delete_from_indexed_pt_1_prt_1 delete_from_indexed_pt_1 + Recheck Cond: (b = 1) + -> Bitmap Index Scan on delete_from_indexed_pt_1_prt_1_b_idx + Index Cond: (b = 1) + Optimizer: Postgres query optimizer +(7 rows) DELETE FROM delete_from_indexed_pt WHERE b=1; +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables SELECT * FROM delete_from_indexed_pt; a | b ----+--- @@ -1273,46 +1283,41 @@ INSERT INTO delete_from_pt SELECT i, i%6 FROM generate_series(1, 10)i; INSERT INTO t VALUES (1); ANALYZE delete_from_pt, t; EXPLAIN (COSTS OFF, TIMING OFF, SUMMARY OFF, ANALYZE) DELETE FROM delete_from_pt WHERE b IN (SELECT b FROM delete_from_pt, t WHERE t.a=delete_from_pt.b); - QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: Feature not supported: DML(delete) on partitioned tables + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------- Delete on delete_from_pt (actual rows=0 loops=1) - -> Hash Join (actual rows=1 loops=1) - Hash Cond: (delete_from_pt_1.b = delete_from_pt_2.b) - Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. - -> Dynamic Seq Scan on delete_from_pt delete_from_pt_1 (actual rows=3 loops=1) - Number of partitions to scan: 3 (out of 3) - Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). - -> Hash (actual rows=1 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2049kB - -> Partition Selector (selector id: $0) (actual rows=1 loops=1) - -> Broadcast Motion 3:3 (slice1; segments: 3) (actual rows=1 loops=1) - -> GroupAggregate (actual rows=1 loops=1) - Group Key: delete_from_pt_2.b - -> Sort (actual rows=2 loops=1) - Sort Key: delete_from_pt_2.b - Sort Method: quicksort Memory: 75kB - Executor Memory: 178kB Segments: 3 Max: 60kB (segment 0) - -> Redistribute Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1) - Hash Key: delete_from_pt_2.b - -> GroupAggregate (actual rows=1 loops=1) - Group Key: delete_from_pt_2.b - -> Sort (actual rows=1 loops=1) - Sort Key: delete_from_pt_2.b - Sort Method: quicksort Memory: 75kB - Executor Memory: 178kB Segments: 3 Max: 60kB (segment 0) - -> Hash Join (actual rows=1 loops=1) - Hash Cond: (delete_from_pt_2.b = t.a) - Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. - -> Dynamic Seq Scan on delete_from_pt delete_from_pt_2 (actual rows=3 loops=1) - Number of partitions to scan: 3 (out of 3) - Partitions scanned: Avg 1.0 x 3 workers. Max 1 parts (seg0). - -> Hash (actual rows=1 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2049kB - -> Partition Selector (selector id: $1) (actual rows=1 loops=1) - -> Broadcast Motion 3:3 (slice3; segments: 3) (actual rows=1 loops=1) - -> Seq Scan on t (actual rows=1 loops=1) - Optimizer: Pivotal Optimizer (GPORCA) -(37 rows) + Delete on delete_from_pt_1_prt_1 delete_from_pt_2 + Delete on delete_from_pt_1_prt_2 delete_from_pt_3 + Delete on delete_from_pt_1_prt_3 delete_from_pt_4 + -> Hash Semi Join (actual rows=1 loops=1) + Hash Cond: (delete_from_pt.b = t.a) + Extra Text: (seg0) Hash chain length 2.0 avg, 2 max, using 1 of 131072 buckets. + -> Append (actual rows=3 loops=1) + Partition Selectors: $1 + -> Seq Scan on delete_from_pt_1_prt_1 delete_from_pt_2 (actual rows=3 loops=1) + -> Seq Scan on delete_from_pt_1_prt_2 delete_from_pt_3 (never executed) + -> Seq Scan on delete_from_pt_1_prt_3 delete_from_pt_4 (never executed) + -> Hash (actual rows=2 loops=1) + Buckets: 131072 Batches: 1 Memory Usage: 1025kB + -> Partition Selector (selector id: $1) (actual rows=2 loops=1) + -> Broadcast Motion 3:3 (slice1; segments: 3) (actual rows=2 loops=1) + -> Hash Join (actual rows=1 loops=1) + Hash Cond: (delete_from_pt_1.b = t.a) + Extra Text: (seg0) Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. + -> Append (actual rows=3 loops=1) + Partition Selectors: $2 + -> Seq Scan on delete_from_pt_1_prt_1 delete_from_pt_5 (actual rows=3 loops=1) + -> Seq Scan on delete_from_pt_1_prt_2 delete_from_pt_6 (never executed) + -> Seq Scan on delete_from_pt_1_prt_3 delete_from_pt_7 (never executed) + -> Hash (actual rows=1 loops=1) + Buckets: 262144 Batches: 1 Memory Usage: 2049kB + -> Partition Selector (selector id: $2) (actual rows=1 loops=1) + -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) + -> Seq Scan on t (actual rows=1 loops=1) + Optimizer: Postgres query optimizer +(30 rows) SELECT * FROM delete_from_pt order by a; a | b diff --git a/src/test/regress/expected/direct_dispatch_optimizer.out b/src/test/regress/expected/direct_dispatch_optimizer.out index d262f73e81..de8aeef58e 100644 --- a/src/test/regress/expected/direct_dispatch_optimizer.out +++ b/src/test/regress/expected/direct_dispatch_optimizer.out @@ -877,13 +877,13 @@ begin; -- orca does not handle direct dispatch for DELETE or UPDATE now -- also orca does not handle DELETE/UPDATE for partitioned tables now. explain (costs off) delete from t_hash_partition where r_regionkey=1; - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Delete on t_hash_partition - -> Dynamic Seq Scan on t_hash_partition t_hash_partition_1 - Number of partitions to scan: 1 (out of 3) + Delete on t_hash_partition_1_prt_region1 t_hash_partition_1 + -> Seq Scan on t_hash_partition_1_prt_region1 t_hash_partition_1 Filter: (r_regionkey = 1) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: Postgres query optimizer (5 rows) delete from t_hash_partition where r_regionkey=1; @@ -901,19 +901,19 @@ begin; -- orca does not handle direct dispatch for DELETE or UPDATE now -- also orca does not handle DELETE/UPDATE for partitioned tables now. explain (costs off) update t_hash_partition set r_name = 'CHINA' where r_regionkey=1; - QUERY PLAN ---------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Update on t_hash_partition - -> Dynamic Seq Scan on t_hash_partition t_hash_partition_1 - Number of partitions to scan: 1 (out of 3) + Update on t_hash_partition_1_prt_region1 t_hash_partition_1 + -> Seq Scan on t_hash_partition_1_prt_region1 t_hash_partition_1 Filter: (r_regionkey = 1) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: Postgres query optimizer (5 rows) update t_hash_partition set r_name = 'CHINA' where r_regionkey=1; -INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 +INFO: (slice 0) Dispatch command to SINGLE content abort; -INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 +INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content begin; -- orca does not handle direct dispatch for DELETE or UPDATE now update t_hash_partition_1_prt_region1 set r_name = 'CHINA' where r_regionkey=1; @@ -954,7 +954,6 @@ INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL begin; -- root & leaf policy mismatch, will not direct dispatch delete from t_hash_partition where r_regionkey=1; -INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 abort; INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 @@ -968,7 +967,6 @@ INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL begin; -- root & leaf policy mismatch, will not direct dispatch update t_hash_partition set r_name = 'CHINA' where r_regionkey=1; -INFO: (slice 1) Dispatch command to ALL contents: 0 1 2 INFO: (slice 0) Dispatch command to ALL contents: 0 1 2 abort; INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2 diff --git a/src/test/regress/expected/inherit_optimizer.out b/src/test/regress/expected/inherit_optimizer.out index e07dd92bd1..a226521214 100644 --- a/src/test/regress/expected/inherit_optimizer.out +++ b/src/test/regress/expected/inherit_optimizer.out @@ -669,16 +669,13 @@ select tableoid::regclass::text as relname, parted_tab.* from parted_tab order b -- modifies partition key, but no rows will actually be updated explain update parted_tab set a = 2 where false; - QUERY PLAN -------------------------------------------------------------------- - Update on parted_tab (cost=0.00..0.00 rows=0 width=1) - -> Result (cost=0.00..0.00 rows=0 width=30) - -> Result (cost=0.00..0.00 rows=0 width=26) - -> Split (cost=0.00..0.00 rows=0 width=26) - -> Result (cost=0.00..0.00 rows=0 width=26) - One-Time Filter: false - Optimizer: Pivotal Optimizer (GPORCA) -(7 rows) + QUERY PLAN +-------------------------------------------------------- + Update on parted_tab (cost=0.00..0.00 rows=0 width=0) + -> Result (cost=0.00..0.00 rows=0 width=0) + One-Time Filter: false + Optimizer: Postgres query optimizer +(4 rows) drop table parted_tab; -- Check UPDATE with multi-level partitioned inherited target diff --git a/src/test/regress/expected/partition_prune_optimizer.out b/src/test/regress/expected/partition_prune_optimizer.out index 35e10f773c..7c9c1bcdd0 100644 --- a/src/test/regress/expected/partition_prune_optimizer.out +++ b/src/test/regress/expected/partition_prune_optimizer.out @@ -2761,32 +2761,30 @@ truncate ab; insert into ab values (1, 1), (1, 2), (1, 3), (2, 1); explain (analyze, costs off, summary off, timing off) update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1); - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------- Update on ab_a1 (actual rows=0 loops=1) - -> Result (actual rows=6 loops=1) - -> Split (actual rows=6 loops=1) - -> Nested Loop (actual rows=3 loops=1) - Join Filter: true - -> Dynamic Seq Scan on ab_a1 ab_a1_1 (actual rows=3 loops=1) - Number of partitions to scan: 3 (out of 3) - Partitions scanned: Avg 3.0 x 3 workers. Max 3 parts (seg0). - -> Materialize (actual rows=1 loops=4) - -> Broadcast Motion 3:3 (slice1; segments: 3) (actual rows=1 loops=1) - -> Hash Join (actual rows=1 loops=1) - Hash Cond: ((1) = ab_a2.b) - Extra Text: (seg2) Hash chain length 1.0 avg, 1 max, using 1 of 262144 buckets. - -> Result (actual rows=1 loops=1) - One-Time Filter: (gp_execution_segment() = 2) - -> Result (actual rows=1 loops=1) - -> Hash (actual rows=1 loops=1) - Buckets: 262144 Batches: 1 Memory Usage: 2049kB - -> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1) - -> Dynamic Seq Scan on ab_a2 (actual rows=1 loops=1) - Number of partitions to scan: 3 (out of 3) - Partitions scanned: Avg 3.0 x 3 workers. Max 3 parts (seg0). - Optimizer: Pivotal Optimizer (GPORCA) -(23 rows) + Update on ab_a1_b1 ab_a1_1 + Update on ab_a1_b2 ab_a1_2 + Update on ab_a1_b3 ab_a1_3 + InitPlan 1 (returns $0) (slice2) + -> Result (actual rows=1 loops=1) + -> Nested Loop (actual rows=3 loops=1) + -> Broadcast Motion 3:3 (slice1; segments: 3) (actual rows=1 loops=1) + -> Append (actual rows=1 loops=1) + -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1 loops=1) + Filter: (b = $0) + -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed) + Filter: (b = $0) + -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed) + Filter: (b = $0) + -> Materialize (actual rows=3 loops=1) + -> Append (actual rows=3 loops=1) + -> Seq Scan on ab_a1_b1 ab_a1_1 (actual rows=1 loops=1) + -> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1) + -> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1 loops=1) + Optimizer: Postgres query optimizer +(21 rows) select tableoid::regclass, * from ab; tableoid | a | b @@ -3517,23 +3515,23 @@ explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); (5 rows) explain (costs off) update pp_arrpart set a = a where a = '{1}'; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +-------------------------------------------- Update on pp_arrpart - -> Dynamic Seq Scan on pp_arrpart pp_arrpart_1 - Number of partitions to scan: 1 (out of 2) + Update on pp_arrpart1 pp_arrpart_1 + -> Seq Scan on pp_arrpart1 pp_arrpart_1 Filter: (a = '{1}'::integer[]) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: Postgres query optimizer (5 rows) explain (costs off) delete from pp_arrpart where a = '{1}'; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +-------------------------------------------- Delete on pp_arrpart - -> Dynamic Seq Scan on pp_arrpart pp_arrpart_1 - Number of partitions to scan: 1 (out of 2) + Delete on pp_arrpart1 pp_arrpart_1 + -> Seq Scan on pp_arrpart1 pp_arrpart_1 Filter: (a = '{1}'::integer[]) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: Postgres query optimizer (5 rows) drop table pp_arrpart; @@ -3671,23 +3669,23 @@ explain (costs off) select * from pp_lp where a = 1; (5 rows) explain (costs off) update pp_lp set value = 10 where a = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------- Update on pp_lp - -> Dynamic Seq Scan on pp_lp pp_lp_1 - Number of partitions to scan: 1 (out of 2) + Update on pp_lp1 pp_lp_1 + -> Seq Scan on pp_lp1 pp_lp_1 Filter: (a = 1) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: Postgres query optimizer (5 rows) explain (costs off) delete from pp_lp where a = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------- Delete on pp_lp - -> Dynamic Seq Scan on pp_lp pp_lp_1 - Number of partitions to scan: 1 (out of 2) + Delete on pp_lp1 pp_lp_1 + -> Seq Scan on pp_lp1 pp_lp_1 Filter: (a = 1) - Optimizer: Pivotal Optimizer (GPORCA) + Optimizer: Postgres query optimizer (5 rows) set enable_partition_pruning = off; @@ -3703,24 +3701,32 @@ explain (costs off) select * from pp_lp where a = 1; (5 rows) explain (costs off) update pp_lp set value = 10 where a = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------- Update on pp_lp - -> Dynamic Seq Scan on pp_lp pp_lp_1 - Number of partitions to scan: 1 (out of 2) - Filter: (a = 1) - Optimizer: Pivotal Optimizer (GPORCA) -(5 rows) + Update on pp_lp1 pp_lp_1 + Update on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) + Optimizer: Postgres query optimizer +(9 rows) explain (costs off) delete from pp_lp where a = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------- Delete on pp_lp - -> Dynamic Seq Scan on pp_lp pp_lp_1 - Number of partitions to scan: 1 (out of 2) - Filter: (a = 1) - Optimizer: Pivotal Optimizer (GPORCA) -(5 rows) + Delete on pp_lp1 pp_lp_1 + Delete on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) + Optimizer: Postgres query optimizer +(9 rows) set constraint_exclusion = 'off'; -- this should not affect the result. explain (costs off) select * from pp_lp where a = 1; @@ -3734,24 +3740,32 @@ explain (costs off) select * from pp_lp where a = 1; (5 rows) explain (costs off) update pp_lp set value = 10 where a = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------- Update on pp_lp - -> Dynamic Seq Scan on pp_lp pp_lp_1 - Number of partitions to scan: 1 (out of 2) - Filter: (a = 1) - Optimizer: Pivotal Optimizer (GPORCA) -(5 rows) + Update on pp_lp1 pp_lp_1 + Update on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) + Optimizer: Postgres query optimizer +(9 rows) explain (costs off) delete from pp_lp where a = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------- Delete on pp_lp - -> Dynamic Seq Scan on pp_lp pp_lp_1 - Number of partitions to scan: 1 (out of 2) - Filter: (a = 1) - Optimizer: Pivotal Optimizer (GPORCA) -(5 rows) + Delete on pp_lp1 pp_lp_1 + Delete on pp_lp2 pp_lp_2 + -> Append + -> Seq Scan on pp_lp1 pp_lp_1 + Filter: (a = 1) + -> Seq Scan on pp_lp2 pp_lp_2 + Filter: (a = 1) + Optimizer: Postgres query optimizer +(9 rows) drop table pp_lp; -- Ensure enable_partition_prune does not affect non-partitioned tables. diff --git a/src/test/regress/expected/qp_dml_joins_optimizer.out b/src/test/regress/expected/qp_dml_joins_optimizer.out index 7c65e78fcb..daa153f165 100644 --- a/src/test/regress/expected/qp_dml_joins_optimizer.out +++ b/src/test/regress/expected/qp_dml_joins_optimizer.out @@ -2617,8 +2617,7 @@ SELECT COUNT(*) FROM dml_heap_pt_s; (1 row) DELETE FROM dml_heap_pt_s WHERE a = (SELECT dml_heap_pt_r.a FROM dml_heap_pt_r,dml_heap_pt_s WHERE dml_heap_pt_r.a = dml_heap_pt_s.a); -ERROR: one or more assertions failed (seg1 10.138.0.30:7003 pid=706789) -DETAIL: Expected no more than one row to be returned by expression +ERROR: more than one row returned by a subquery used as an expression SELECT COUNT(*) FROM dml_heap_pt_s; count ------- @@ -3323,8 +3322,7 @@ SELECT SUM(a) FROM dml_heap_pt_r; (1 row) UPDATE dml_heap_pt_r SET a = ( SELECT DISTINCT(b) FROM dml_heap_pt_s ) FROM dml_heap_pt_s WHERE dml_heap_pt_r.b = dml_heap_pt_s.a; -ERROR: one or more assertions failed (seg0 slice1 10.138.0.30:7002 pid=527664) -DETAIL: Expected no more than one row to be returned by expression +ERROR: more than one row returned by a subquery used as an expression SELECT SUM(a) FROM dml_heap_pt_r; sum ------ @@ -3339,8 +3337,7 @@ SELECT SUM(b) FROM dml_heap_pt_r; (1 row) UPDATE dml_heap_pt_r SET b = (SELECT dml_heap_pt_r.b FROM dml_heap_pt_r,dml_heap_pt_s WHERE dml_heap_pt_r.a = dml_heap_pt_s.a ); -ERROR: one or more assertions failed (seg0 10.138.0.30:7002 pid=527290) -DETAIL: Expected no more than one row to be returned by expression +ERROR: more than one row returned by a subquery used as an expression SELECT SUM(b) FROM dml_heap_pt_r; sum ------- --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
