You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by dj...@apache.org on 2026/10/16 07:22:03 UTC
(cloudberry) branch main updated (36141209e8a -> 342c89d58e1)
This is an automated email from the ASF dual-hosted git repository.
tuhaihe pushed a change to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
from 36141209e8a Remove the orphaned PL/Java Makefile.global
new bb9f21f12ba Let ORCA plan an in-place UPDATE of append-optimized tables
new 342c89d58e1 Fix AO row scan expanding unread columns (#965) Closes field-engineering/dev_collaboration#965
The 2 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
.../modify_table_data_corrupt_optimizer.out | 75 ++---
.../regress/expected/DML_over_joins_optimizer.out | 113 +-------
.../regress/expected/direct_dispatch_optimizer.out | 72 +++--
.../src/test/regress/expected/gporca_optimizer.out | 39 ++-
.../test/regress/expected/hash_index_optimizer.out | 8 -
.../regress/expected/partition_prune_optimizer.out | 14 +-
.../expected/qp_correlated_query_optimizer.out | 16 --
.../expected/qp_orca_fallback_optimizer.out | 2 -
.../regress/expected/qp_subquery_optimizer.out | 2 -
.../expected/qp_targeted_dispatch_optimizer.out | 8 +-
.../expected/qp_union_intersect_optimizer.out | 3 +-
.../test/regress/expected/update_gp_optimizer.out | 64 +++--
src/backend/access/appendonly/Makefile | 2 +-
.../access/appendonly/appendonly_compaction.c | 4 +-
src/backend/access/appendonly/appendonlyam.c | 38 +--
.../access/appendonly/appendonlyam_handler.c | 2 +-
src/backend/access/appendonly/memtuple_slot.c | 312 +++++++++++++++++++++
src/backend/access/common/memtuple.c | 24 +-
src/backend/executor/nodeModifyTable.c | 33 +++
.../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 5 -
src/include/access/memtup.h | 2 +
src/include/executor/tuptable.h | 31 ++
src/include/nodes/execnodes.h | 8 +
.../output/uao/parallel_update_optimizer.source | 6 +-
src/test/regress/expected/ao_locks_optimizer.out | 6 +-
src/test/regress/expected/orca_split_update.out | 104 +++++++
.../expected/orca_split_update_optimizer.out | 108 +++++++
src/test/regress/greenplum_schedule | 2 +-
src/test/regress/sql/orca_split_update.sql | 47 ++++
29 files changed, 860 insertions(+), 290 deletions(-)
create mode 100644 src/backend/access/appendonly/memtuple_slot.c
create mode 100644 src/test/regress/expected/orca_split_update.out
create mode 100644 src/test/regress/expected/orca_split_update_optimizer.out
create mode 100644 src/test/regress/sql/orca_split_update.sql
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 02/02: Fix AO row scan expanding unread columns (#965) Closes field-engineering/dev_collaboration#965
Posted by dj...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
tuhaihe pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 342c89d58e195e524ece21ddc26a08baeba69e68
Author: liushengsong <li...@hashdata.cn>
AuthorDate: Thu Sep 24 10:19:57 2026 +0800
Fix AO row scan expanding unread columns (#965) Closes field-engineering/dev_collaboration#965
## Summary
Fixes field-engineering/dev_collaboration#965: a `count(1)` over a 281 column
append-only **row** table, qualified on three date columns, was 6x slower than the same
data in a heap table and 14x slower than AOCS.
### Root cause
`AppendOnlyExecutorReadBlock_ProcessTuple()` called `memtuple_deform()` and then
`ExecStoreVirtualTuple()`, and `appendonly_slot_callbacks()` returned `TTSOpsVirtual`.
A virtual slot promises its values are already in `tts_values[]`, so
`ExecStoreVirtualTuple()` sets `tts_nvalid = natts` and `slot_getattr()` never reaches
`getsomeattrs` — the scan has no choice but to expand **every** column of **every**
tuple. Heap tables are unaffected because `TTSOpsBufferHeapTuple` deforms incrementally
up to the highest attribute number the query references.
Scan cost therefore grew with the **width of the table** instead of the columns actually
read. Measured on a release build (`-O3`, no cassert), 500k rows, query touching only
attno 2:
| table columns | AO row before | heap |
|---|---|---|
| 20 | 46.9 ms | 35.7 ms |
| 100 | 114.2 ms | 36.8 ms |
| 281 | **236.0 ms** | 39.1 ms |
This is not how the format is meant to be read. A MemTuple keeps precomputed attribute
offsets in its binding, so any attribute is reachable in constant time, and before the
PostgreSQL 12 merge (`19cd1cf4b6`) the slot carried that binding and fetched attributes
on demand. That merge dropped the MemTuple slot shape — which at the time could only
exist as extra fields in the shared `TupleTableSlot` struct — and converted the tuple
eagerly instead. PostgreSQL 12 also introduced `TupleTableSlotOps` precisely so an access
method can keep tuples in its native format, which is what this MR finally uses.
**The same code is present in GPDB 7 and Apache Cloudberry main**, both verified to show
the identical regression (GPDB 7 release: 193.8 ms vs heap 28.9 ms at 281 columns). This
is an inherited PG12-merge artifact, not something introduced by Lightning.
### Change
Add `TTSOpsMemTuple`, a slot holding the MemTuple together with the binding needed to
interpret it, expanding attributes in `getsomeattrs`. Because offsets are precomputed,
`tts_nvalid` alone is the resume point — no separate deforming cursor (the `off` field
heap and minimal tuple slots carry) is needed. `memtuple_deform_range()` reuses the
existing inlinable internal helper so full-column access keeps the old bulk-deform speed.
Two details are easy to miss when adding a slot type here, both found by running the full
regression suite:
- `gp_segment_id` and `gp_foreign_server` are answered by the slot even though no tuple
format carries them, so `getsysattr` must keep the special cases that
`tts_virtual_getsysattr` documents. Omitting them broke ~59 tests.
- Not every slot reaches the scan through `table_slot_create()`.
`AppendOnlySegmentFileFullCompaction()` built a virtual slot directly and passed it to
`appendonly_getnextslot()`; in a release build the assertion is compiled out and the
larger slot struct would be written past the end of the smaller one. It now asks the
access method for the slot type, and `ProcessTuple` additionally falls back to expanding
the tuple up front for any non-MemTuple slot, so a caller bringing its own slot cannot
be handed one it cannot read.
## Test plan
Release build (`-O3`, `debug_assertions=off`), demo cluster, 500k rows.
The two tables below pin one variable each. Query is
`SELECT count(1) FROM t WHERE c2 > 0`, i.e. it references attno 2 only, unless stated.
**1. Cost no longer depends on how wide the table is.** Query fixed on attno 2, table width varied:
| table columns | before | after | heap |
|---|---|---|---|
| 20 | 46.9 ms | 35.9 ms | 35.7 ms |
| 50 | 67.3 ms | 36.9 ms | 38.4 ms |
| 100 | 114.2 ms | 36.0 ms | 36.8 ms |
| 200 | 171.1 ms | 37.6 ms | 39.0 ms |
| **281** | **236.0 ms** | **41.4 ms** | 39.1 ms |
| 281, all columns populated | 415.0 ms | 112.7 ms | 103.6 ms |
**2. Cost now depends on the highest attribute number referenced, exactly as heap does.**
Table fixed at 281 columns, referenced column varied (`count(cN)`):
| referenced up to | after | heap |
|---|---|---|
| c2 | 37.3 ms | 36.1 ms |
| c50 | 68.2 ms | 56.4 ms |
| c140 | 121.5 ms | 95.4 ms |
| c281 | 212.4 ms | 156.2 ms |
Slopes: AO 0.63 ms per column per 500k rows, heap 0.43 ms. So the win is not that MemTuple
deforms faster than heap — it does not — it is that unread columns are no longer deformed at
all. A query reaching the last column costs what it always cost; the flat line in table 1 is
a consequence of referencing attno 2, not an unconditional property.
The reported query references attno 9, 12 and 13, so it now expands 13 columns instead of
281. `getsomeattrs` fills a prefix, so the gap columns (1-8, 10, 11) are still expanded;
skipping those would need per-column projection, which `tts_nvalid` cannot express (it is a
single watermark, so validity is necessarily a prefix) and which would mean claiming
unfilled columns are valid. Not attempted here.
Full-column access is not penalised: `count(c281)` 217.7 ms (before the change a bare scan
already cost 236.0 ms because it deformed everything), `sum(c281)` on fully populated data
415.9 ms vs 415.0 ms.
**Correctness**
- [x] `SELECT * FROM ao EXCEPT SELECT * FROM heap` and the reverse both return 0 rows, over
all 281 columns, for sparse and fully populated data
- [x] System columns unchanged: `ctid` / `tableoid` / `gp_segment_id` work, the four MVCC
columns still error (a MemTuple carries no MVCC fields; visibility lives in the visimap)
- [x] Materialize-dependent paths: CTE MATERIALIZED, ORDER BY, CTAS round trip, cursor
across statements
- [x] `make installcheck` A/B on the same cluster: **baseline 36/723 failed, with this
change 36/723 failed, failure sets identical**. The 36 are environmental in this
build (no pax_storage / iceberg AM, ORCA disabled).
- [x] Builds clean under `-O3 -Werror`
**Not done**
- [ ] No regression test added for the wide-table-narrow-projection behaviour. Both bugs
above surfaced only from the full suite, so this path is under-covered; a test
exercising a wide table plus the VACUUM / CLUSTER / CREATE INDEX paths would be
worth adding. Happy to add it to this MR if preferred.
- [ ] Not yet verified on GPDB 7 / upstream Cloudberry, though the patch applies to the
identical code there.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
(cherry picked from commit 47a358712540acfa3ecd7ee1cb778ed01d5298f4)
---
src/backend/access/appendonly/Makefile | 2 +-
.../access/appendonly/appendonly_compaction.c | 4 +-
src/backend/access/appendonly/appendonlyam.c | 38 +--
.../access/appendonly/appendonlyam_handler.c | 2 +-
src/backend/access/appendonly/memtuple_slot.c | 312 +++++++++++++++++++++
src/backend/access/common/memtuple.c | 24 +-
src/include/access/memtup.h | 2 +
src/include/executor/tuptable.h | 31 ++
8 files changed, 393 insertions(+), 22 deletions(-)
diff --git a/src/backend/access/appendonly/Makefile b/src/backend/access/appendonly/Makefile
index 430e397f2fa..24127bfe76f 100755
--- a/src/backend/access/appendonly/Makefile
+++ b/src/backend/access/appendonly/Makefile
@@ -15,7 +15,7 @@ OBJS = appendonlyam_handler.o appendonlyam.o aosegfiles.o aomd.o \
appendonlyblockdirectory.o appendonly_visimap.o \
appendonly_visimap_entry.o appendonly_visimap_store.o \
appendonly_compaction.o appendonly_visimap_udf.o \
- appendonly_blkdir_udf.o aomd_filehandler.o
+ appendonly_blkdir_udf.o aomd_filehandler.o memtuple_slot.o
include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/access/appendonly/appendonly_compaction.c b/src/backend/access/appendonly/appendonly_compaction.c
index b8cae6ebf6c..843b7330eab 100644
--- a/src/backend/access/appendonly/appendonly_compaction.c
+++ b/src/backend/access/appendonly/appendonly_compaction.c
@@ -32,6 +32,7 @@
#include "access/genam.h"
#include "access/heapam.h"
#include "access/toast_internals.h"
+#include "access/tableam.h"
#include "access/transam.h"
#include "access/heaptoast.h"
#include "access/xact.h"
@@ -443,7 +444,8 @@ AppendOnlySegmentFileFullCompaction(Relation aorel,
&compact_segno, 1, 0, NULL);
tupDesc = RelationGetDescr(aorel);
- slot = MakeSingleTupleTableSlot(tupDesc, &TTSOpsVirtual);
+ /* Use the slot type the table's access method hands out, not a fixed one. */
+ slot = table_slot_create(aorel, NULL);
slot->tts_tableOid = RelationGetRelid(aorel);
mt_bind = create_memtuple_binding(tupDesc);
diff --git a/src/backend/access/appendonly/appendonlyam.c b/src/backend/access/appendonly/appendonlyam.c
index 6aa482fe453..aeffdc210b1 100755
--- a/src/backend/access/appendonly/appendonlyam.c
+++ b/src/backend/access/appendonly/appendonlyam.c
@@ -831,29 +831,33 @@ AppendOnlyExecutorReadBlock_ProcessTuple(AppendOnlyExecutorReadBlock *executorRe
*/
Assert (slot);
{
- bool shouldFree = false;
-
Assert(executorReadBlock->mt_bind);
- ExecClearTuple(slot);
- memtuple_deform(tuple, executorReadBlock->mt_bind, slot->tts_values, slot->tts_isnull);
- slot->tts_tid = fake_ctid;
-
- if (shouldFree)
+ /*
+ * Hand the tuple to the slot as it is. Attributes are fetched from
+ * it only as the executor asks for them, so a query over a wide table
+ * does not pay for the columns it never reads.
+ *
+ * The tuple points into the read buffer and stays valid until the
+ * next block is read, which is why the slot does not own it.
+ */
+ if (TTS_IS_MEMTUPLE(slot))
+ {
+ ExecStoreMemTuple(tuple, executorReadBlock->mt_bind, slot, false);
+ slot->tts_tid = fake_ctid;
+ }
+ else
{
/*
- * Store the converted memtuple in slot->data, so that it gets free'd
- * automatically when it's no longer needed.
+ * A caller that brought its own slot type gets the tuple expanded
+ * up front, which is all a virtual slot can represent.
*/
- Assert(TTS_IS_VIRTUAL(slot));
- VirtualTupleTableSlot *vslot = (VirtualTupleTableSlot *) slot;
- Assert(vslot->data == NULL);
- Assert(!TTS_SHOULDFREE(slot));
-
- slot->tts_flags |= TTS_FLAG_SHOULDFREE;
- vslot->data = (char *) tuple;
+ ExecClearTuple(slot);
+ memtuple_deform(tuple, executorReadBlock->mt_bind,
+ slot->tts_values, slot->tts_isnull);
+ slot->tts_tid = fake_ctid;
+ ExecStoreVirtualTuple(slot);
}
- ExecStoreVirtualTuple(slot);
}
/* skip visibility test, all tuples are visible */
diff --git a/src/backend/access/appendonly/appendonlyam_handler.c b/src/backend/access/appendonly/appendonlyam_handler.c
index ac98029d4d0..92ba0f944ca 100644
--- a/src/backend/access/appendonly/appendonlyam_handler.c
+++ b/src/backend/access/appendonly/appendonlyam_handler.c
@@ -505,7 +505,7 @@ get_or_create_unique_check_desc(Relation relation, Snapshot snapshot)
static const TupleTableSlotOps *
appendonly_slot_callbacks(Relation relation)
{
- return &TTSOpsVirtual;
+ return &TTSOpsMemTuple;
}
MemTuple
diff --git a/src/backend/access/appendonly/memtuple_slot.c b/src/backend/access/appendonly/memtuple_slot.c
new file mode 100644
index 00000000000..0806e5931b6
--- /dev/null
+++ b/src/backend/access/appendonly/memtuple_slot.c
@@ -0,0 +1,312 @@
+/*-------------------------------------------------------------------------
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ * memtuple_slot.c
+ * TupleTableSlot implementation for MemTuples.
+ *
+ * Append-only row tables store tuples in the MemTuple format. Unlike heap
+ * and minimal tuples a MemTuple is not self describing: a MemTupleBinding
+ * is required to locate its attributes. That binding lives here in the
+ * slot, which is what lets the slot hand out attributes on demand instead
+ * of forcing the scan to expand every column of every tuple up front.
+ *
+ * Attribute offsets are precomputed in the binding, so each attribute is
+ * fetched independently in constant time. tts_nvalid therefore doubles as
+ * the resume point for getsomeattrs, and no separate deforming cursor (the
+ * "off" field heap and minimal tuple slots carry) is needed.
+ *
+ * IDENTIFICATION
+ * src/backend/access/appendonly/memtuple_slot.c
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "cdb/cdbvars.h"
+#include "foreign/foreign.h"
+#include "access/memtup.h"
+#include "executor/tuptable.h"
+
+/*
+ * Return the binding used to interpret this slot's tuple, creating one from
+ * the slot's tuple descriptor if the caller did not supply one. A binding
+ * built here is owned by the slot and released with it.
+ */
+static MemTupleBinding *
+tts_memtuple_bind(MemTupleTableSlot *mslot)
+{
+ if (mslot->mt_bind == NULL)
+ {
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(mslot->base.tts_mcxt);
+ mslot->mt_bind = create_memtuple_binding(mslot->base.tts_tupleDescriptor);
+ MemoryContextSwitchTo(oldcontext);
+
+ mslot->own_bind = true;
+ }
+
+ return mslot->mt_bind;
+}
+
+static void
+tts_memtuple_init(TupleTableSlot *slot)
+{
+ MemTupleTableSlot *mslot = (MemTupleTableSlot *) slot;
+
+ mslot->tuple = NULL;
+ mslot->mt_bind = NULL;
+ mslot->own_bind = false;
+}
+
+static void
+tts_memtuple_release(TupleTableSlot *slot)
+{
+ MemTupleTableSlot *mslot = (MemTupleTableSlot *) slot;
+
+ if (mslot->own_bind && mslot->mt_bind != NULL)
+ destroy_memtuple_binding(mslot->mt_bind);
+
+ mslot->mt_bind = NULL;
+ mslot->own_bind = false;
+}
+
+static void
+tts_memtuple_clear(TupleTableSlot *slot)
+{
+ MemTupleTableSlot *mslot = (MemTupleTableSlot *) slot;
+
+ if (TTS_SHOULDFREE(slot))
+ {
+ pfree(mslot->tuple);
+ slot->tts_flags &= ~TTS_FLAG_SHOULDFREE;
+ }
+
+ slot->tts_nvalid = 0;
+ slot->tts_flags |= TTS_FLAG_EMPTY;
+ ItemPointerSetInvalid(&slot->tts_tid);
+ mslot->tuple = NULL;
+}
+
+static void
+tts_memtuple_getsomeattrs(TupleTableSlot *slot, int natts)
+{
+ MemTupleTableSlot *mslot = (MemTupleTableSlot *) slot;
+ MemTupleBinding *mt_bind;
+
+ Assert(!TTS_EMPTY(slot));
+ Assert(mslot->tuple != NULL);
+ Assert(natts <= slot->tts_tupleDescriptor->natts);
+
+ mt_bind = tts_memtuple_bind(mslot);
+
+ /* Resume where the previous call stopped; each fetch is independent. */
+ memtuple_deform_range(mslot->tuple, mt_bind, slot->tts_nvalid, natts,
+ slot->tts_values, slot->tts_isnull);
+
+ slot->tts_nvalid = natts;
+}
+
+static Datum
+tts_memtuple_getsysattr(TupleTableSlot *slot, int attnum, bool *isnull)
+{
+ Assert(!TTS_EMPTY(slot));
+
+ /*
+ * A memtuple carries no system attributes, but append-optimized relations
+ * are still queried for gp_segment_id and gp_foreign_server, which are
+ * properties of the segment rather than of the tuple.
+ */
+ if (attnum == GpSegmentIdAttributeNumber)
+ {
+ *isnull = false;
+
+ return Int32GetDatum(GpIdentity.segindex);
+ }
+ else if (attnum == GpForeignServerAttributeNumber)
+ {
+ *isnull = false;
+
+ return ObjectIdGetDatum(GetForeignServerSegByRelid(slot->tts_tableOid));
+ }
+
+ elog(ERROR, "memtuple table slot does not have system attributes");
+
+ return 0; /* silence compiler warnings */
+}
+
+static void
+tts_memtuple_materialize(TupleTableSlot *slot)
+{
+ MemTupleTableSlot *mslot = (MemTupleTableSlot *) slot;
+ MemoryContext oldcontext;
+
+ Assert(!TTS_EMPTY(slot));
+
+ /* Nothing outside the slot is left to depend on. */
+ if (TTS_SHOULDFREE(slot) && mslot->own_bind)
+ return;
+
+ oldcontext = MemoryContextSwitchTo(slot->tts_mcxt);
+
+ /*
+ * A borrowed binding belongs to the scan, which frees it in
+ * AppendOnlyExecutorReadBlock_Finish(), so the tuple would become
+ * unreadable once the scan ends. Build one of our own, which is
+ * equivalent because both are derived from this tuple descriptor.
+ */
+ if (!mslot->own_bind)
+ {
+ mslot->mt_bind = create_memtuple_binding(slot->tts_tupleDescriptor);
+ mslot->own_bind = true;
+ }
+
+ if (!TTS_SHOULDFREE(slot))
+ {
+ if (mslot->tuple == NULL)
+ mslot->tuple = memtuple_form(mslot->mt_bind,
+ slot->tts_values,
+ slot->tts_isnull);
+ else
+ mslot->tuple = memtuple_copy(mslot->tuple);
+
+ slot->tts_flags |= TTS_FLAG_SHOULDFREE;
+
+ /*
+ * Already deformed by-reference attributes point into the tuple we
+ * just replaced, so they have to be fetched again from the copy.
+ */
+ slot->tts_nvalid = 0;
+ }
+
+ MemoryContextSwitchTo(oldcontext);
+}
+
+static void
+tts_memtuple_copyslot(TupleTableSlot *dstslot, TupleTableSlot *srcslot)
+{
+ TupleDesc srcdesc = srcslot->tts_tupleDescriptor;
+
+ Assert(srcdesc->natts <= dstslot->tts_tupleDescriptor->natts);
+
+ tts_memtuple_clear(dstslot);
+
+ slot_getallattrs(srcslot);
+
+ for (int natt = 0; natt < srcdesc->natts; natt++)
+ {
+ dstslot->tts_values[natt] = srcslot->tts_values[natt];
+ dstslot->tts_isnull[natt] = srcslot->tts_isnull[natt];
+ }
+
+ dstslot->tts_nvalid = srcdesc->natts;
+ dstslot->tts_flags &= ~TTS_FLAG_EMPTY;
+
+ /* Build a tuple of our own so we do not depend on the source's memory. */
+ tts_memtuple_materialize(dstslot);
+}
+
+static HeapTuple
+tts_memtuple_copy_heap_tuple(TupleTableSlot *slot)
+{
+ Assert(!TTS_EMPTY(slot));
+
+ slot_getallattrs(slot);
+
+ return heap_form_tuple(slot->tts_tupleDescriptor,
+ slot->tts_values,
+ slot->tts_isnull);
+}
+
+static MinimalTuple
+tts_memtuple_copy_minimal_tuple(TupleTableSlot *slot)
+{
+ Assert(!TTS_EMPTY(slot));
+
+ slot_getallattrs(slot);
+
+ return heap_form_minimal_tuple(slot->tts_tupleDescriptor,
+ slot->tts_values,
+ slot->tts_isnull);
+}
+
+const TupleTableSlotOps TTSOpsMemTuple = {
+ .base_slot_size = sizeof(MemTupleTableSlot),
+ .init = tts_memtuple_init,
+ .release = tts_memtuple_release,
+ .clear = tts_memtuple_clear,
+ .getsomeattrs = tts_memtuple_getsomeattrs,
+ .getsysattr = tts_memtuple_getsysattr,
+ .materialize = tts_memtuple_materialize,
+ .copyslot = tts_memtuple_copyslot,
+
+ /* A memtuple table slot can not "own" a heap or minimal tuple. */
+ .get_heap_tuple = NULL,
+ .get_minimal_tuple = NULL,
+ .copy_heap_tuple = tts_memtuple_copy_heap_tuple,
+ .copy_minimal_tuple = tts_memtuple_copy_minimal_tuple
+};
+
+/* --------------------------------
+ * ExecStoreMemTuple
+ *
+ * Store a MemTuple into a TTSOpsMemTuple slot. Nothing is deformed
+ * here; attributes are fetched from the tuple as the executor asks for
+ * them. 'mt_bind' describes the tuple and must stay valid for as long
+ * as the tuple does. If 'shouldFree' is true the slot takes ownership
+ * of the tuple and frees it when cleared.
+ * --------------------------------
+ */
+TupleTableSlot *
+ExecStoreMemTuple(MemTuple mtup,
+ MemTupleBinding *mt_bind,
+ TupleTableSlot *slot,
+ bool shouldFree)
+{
+ MemTupleTableSlot *mslot = (MemTupleTableSlot *) slot;
+
+ Assert(mtup != NULL);
+ Assert(mt_bind != NULL);
+ Assert(slot != NULL);
+ Assert(slot->tts_tupleDescriptor != NULL);
+ Assert(TTS_IS_MEMTUPLE(slot));
+
+ tts_memtuple_clear(slot);
+
+ Assert(!TTS_SHOULDFREE(slot));
+ Assert(TTS_EMPTY(slot));
+
+ slot->tts_flags &= ~TTS_FLAG_EMPTY;
+ slot->tts_nvalid = 0;
+ mslot->tuple = mtup;
+
+ /*
+ * Borrow the scan's binding while the slot has none of its own. Once the
+ * slot owns one it keeps it: both describe the same tuple descriptor, and
+ * the slot's outlives the scan.
+ */
+ if (!mslot->own_bind)
+ mslot->mt_bind = mt_bind;
+
+ if (shouldFree)
+ slot->tts_flags |= TTS_FLAG_SHOULDFREE;
+
+ return slot;
+}
diff --git a/src/backend/access/common/memtuple.c b/src/backend/access/common/memtuple.c
index 7129cc9807a..9eccd7c0cbb 100644
--- a/src/backend/access/common/memtuple.c
+++ b/src/backend/access/common/memtuple.c
@@ -935,13 +935,33 @@ memtuple_copy(MemTuple mtup)
return dest;
}
-static void memtuple_get_values(MemTuple mtup, MemTupleBinding *pbind, Datum *datum, bool *isnull, bool use_null_saves_aligned)
+static void memtuple_get_values_range(MemTuple mtup, MemTupleBinding *pbind, int start, int natts,
+ Datum *datum, bool *isnull, bool use_null_saves_aligned)
{
int i;
- for(i=0; i<pbind->tupdesc->natts; ++i)
+ for(i=start; i<natts; ++i)
datum[i] = memtuple_getattr_by_alignment(mtup, pbind, i+1, &isnull[i], use_null_saves_aligned);
}
+static void memtuple_get_values(MemTuple mtup, MemTupleBinding *pbind, Datum *datum, bool *isnull, bool use_null_saves_aligned)
+{
+ memtuple_get_values_range(mtup, pbind, 0, pbind->tupdesc->natts, datum, isnull, use_null_saves_aligned);
+}
+
+/*
+ * Deform attributes [start, natts) of a memtuple, leaving the rest untouched.
+ *
+ * Attribute offsets are precomputed in the binding, so an arbitrary range can
+ * be extracted without touching the attributes that precede it. This lets a
+ * scan expand only the columns a query actually reads.
+ */
+void memtuple_deform_range(MemTuple mtup, MemTupleBinding *pbind, int start, int natts,
+ Datum *datum, bool *isnull)
+{
+ Assert(start >= 0 && start <= natts && natts <= pbind->tupdesc->natts);
+ memtuple_get_values_range(mtup, pbind, start, natts, datum, isnull, true /* aligned */);
+}
+
void memtuple_deform(MemTuple mtup, MemTupleBinding *pbind, Datum *datum, bool *isnull)
{
memtuple_get_values(mtup, pbind, datum, isnull, true /* aligned */);
diff --git a/src/include/access/memtup.h b/src/include/access/memtup.h
index e3bc1cad5d3..fb4f99dd0e6 100644
--- a/src/include/access/memtup.h
+++ b/src/include/access/memtup.h
@@ -141,6 +141,8 @@ extern MemTuple memtuple_form_to(MemTupleBinding *pbind, Datum *values, bool *is
uint32 len, uint32 null_save_len, bool hasnull,
MemTuple mtup);
extern void memtuple_deform(MemTuple mtup, MemTupleBinding *pbind, Datum *datum, bool *isnull);
+extern void memtuple_deform_range(MemTuple mtup, MemTupleBinding *pbind, int start, int natts,
+ Datum *datum, bool *isnull);
extern void memtuple_deform_misaligned(MemTuple mtup, MemTupleBinding *pbind, Datum *datum, bool *isnull);
extern bool MemTupleHasExternal(MemTuple mtup, MemTupleBinding *pbind);
diff --git a/src/include/executor/tuptable.h b/src/include/executor/tuptable.h
index 23a93bcbee4..d187d476ea8 100644
--- a/src/include/executor/tuptable.h
+++ b/src/include/executor/tuptable.h
@@ -224,10 +224,17 @@ extern PGDLLIMPORT const TupleTableSlotOps TTSOpsHeapTuple;
extern PGDLLIMPORT const TupleTableSlotOps TTSOpsMinimalTuple;
extern PGDLLIMPORT const TupleTableSlotOps TTSOpsBufferHeapTuple;
+/*
+ * Append-only row tables store tuples in the MemTuple format, which needs a
+ * MemTupleBinding to be interpreted. See access/appendonly/memtuple_slot.c.
+ */
+extern PGDLLIMPORT const TupleTableSlotOps TTSOpsMemTuple;
+
#define TTS_IS_VIRTUAL(slot) ((slot)->tts_ops == &TTSOpsVirtual)
#define TTS_IS_HEAPTUPLE(slot) ((slot)->tts_ops == &TTSOpsHeapTuple)
#define TTS_IS_MINIMALTUPLE(slot) ((slot)->tts_ops == &TTSOpsMinimalTuple)
#define TTS_IS_BUFFERTUPLE(slot) ((slot)->tts_ops == &TTSOpsBufferHeapTuple)
+#define TTS_IS_MEMTUPLE(slot) ((slot)->tts_ops == &TTSOpsMemTuple)
/*
* Tuple table slot implementations.
@@ -292,6 +299,24 @@ typedef struct MinimalTupleTableSlot
uint32 off; /* saved state for slot_deform_heap_tuple */
} MinimalTupleTableSlot;
+/*
+ * Slot for a MemTuple, the format used by append-only row tables.
+ *
+ * A MemTuple is not self describing, so the binding needed to locate its
+ * attributes is kept here alongside the tuple. Attributes are fetched on
+ * demand in getsomeattrs; tts_nvalid is the resume point.
+ */
+typedef struct MemTupleTableSlot
+{
+ pg_node_attr(abstract)
+
+ TupleTableSlot base;
+
+ MemTuple tuple; /* physical tuple, or NULL if none */
+ MemTupleBinding *mt_bind; /* binding describing tuple */
+ bool own_bind; /* did this slot create mt_bind itself? */
+} MemTupleTableSlot;
+
/*
* TupIsNull -- is a TupleTableSlot empty?
*/
@@ -325,6 +350,12 @@ extern TupleTableSlot *ExecStoreMinimalTuple(MinimalTuple mtup,
bool shouldFree);
extern void ExecForceStoreMinimalTuple(MinimalTuple mtup, TupleTableSlot *slot,
bool shouldFree);
+/* in access/appendonly/memtuple_slot.c */
+extern TupleTableSlot *ExecStoreMemTuple(MemTuple mtup,
+ MemTupleBinding *mt_bind,
+ TupleTableSlot *slot,
+ bool shouldFree);
+
extern TupleTableSlot *ExecStoreVirtualTuple(TupleTableSlot *slot);
extern TupleTableSlot *ExecStoreAllNullTuple(TupleTableSlot *slot);
extern void ExecStoreHeapTupleDatum(Datum data, TupleTableSlot *slot);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org
(cloudberry) 01/02: Let ORCA plan an in-place UPDATE of append-optimized tables
Posted by dj...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
tuhaihe pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit bb9f21f12babf3260e9507b110040ace6ab51d6a
Author: liushengsong <ls...@gmail.com>
AuthorDate: Tue Sep 22 17:28:01 2026 +0800
Let ORCA plan an in-place UPDATE of append-optimized tables
Any UPDATE on an AO, AOCS or PAX table that does not modify a
distribution key column made GPORCA give up and silently fall back to
the Postgres planner:
INFO: GPORCA failed to produce a plan, falling back to Postgres-based
planner
DETAIL: DXL-to-PlStmt Translation: Attribute number 7 not found in
project list
With optimizer_trace_fallback off, which is the default, none of this
reaches the client; only EXPLAIN's "Optimizer:" line gives it away.
Since PG14 a non-split UPDATE builds the new tuple from the plan slot
plus the old tuple, and ExecModifyTable fetches that old tuple by tid.
Append-optimized tables cannot do that lookup -- it would mean scanning
the block directory and the visibility map per tuple, and the block
directory is not guaranteed to exist -- so appendonly_tuple_fetch_row_
version() errors out with "feature not supported on appendoptimized
relations".
The planner works around this by adding a wholerow junk column for such
tables (see the GPDB_14_MERGE_FIXME in add_row_identity_columns), which
makes oldtuple non-NULL and skips the fetch. ORCA has no such column,
so CTranslatorDXLToPlStmt::TranslateDXLDml() forced a split update
instead. But CExpressionPreprocessor::ConvertSplitUpdateToInPlaceUpdate()
had already downgraded the plan to an in-place update -- it only looks at
whether a distribution or partition key column is modified, never at the
access method -- so the DMLAction column the forced split needs was not
in the plan, and the translation failed.
The old tuple is only needed for the columns the subplan does not
assign. ORCA's non-split UPDATE assigns every live column, so nothing
reads it. Record that in ri_projectNewNeedsOldTuple while building the
update projection and skip the fetch when it is false, which lets an
append-optimized table run the plan, and drop the forced split.
This is what the FIXME asks for: the update plan now has the same shape
whether it comes from the planner or from ORCA, and it matches what the
storage engine already does, since appendonly_tuple_update() is itself a
delete followed by an append. Iceberg needs no special case either --
its UPDATE writes the position delete and the new row in one
ExecForeignUpdate call.
The skip is limited to append-optimized relations for now. Letting heap
take it as well would remove the wholerow junk column the planner ships
for those tables, but needs the trigger, EPQ and cross-partition update
paths audited first.
Forcing the split the other way round was tried first and rejected: it
made nine queries that update the same row twice in one statement fail
with "multiple updates to a row by the same query is not allowed", which
only a split update raises.
The answer files change where a recorded fallback disappears, where an
UPDATE plan gains its ORCA shape, and in ao_locks and
uao/parallel_update_{row,column}, which go back to locking all segments
because ORCA does not do direct dispatch for these updates.
See: Issue#603
(cherry picked from commit befb03fea31fd7d4f75a0ab4cdd624f83705777a)
---
.../modify_table_data_corrupt_optimizer.out | 75 +++++++-------
.../regress/expected/DML_over_joins_optimizer.out | 113 +++------------------
.../regress/expected/direct_dispatch_optimizer.out | 72 +++++++------
.../src/test/regress/expected/gporca_optimizer.out | 39 +++----
.../test/regress/expected/hash_index_optimizer.out | 8 --
.../regress/expected/partition_prune_optimizer.out | 14 +--
.../expected/qp_correlated_query_optimizer.out | 16 ---
.../expected/qp_orca_fallback_optimizer.out | 2 -
.../regress/expected/qp_subquery_optimizer.out | 2 -
.../expected/qp_targeted_dispatch_optimizer.out | 8 +-
.../expected/qp_union_intersect_optimizer.out | 3 +-
.../test/regress/expected/update_gp_optimizer.out | 64 +++++++-----
src/backend/executor/nodeModifyTable.c | 33 ++++++
.../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 5 -
src/include/nodes/execnodes.h | 8 ++
.../output/uao/parallel_update_optimizer.source | 6 +-
src/test/regress/expected/ao_locks_optimizer.out | 6 +-
src/test/regress/expected/orca_split_update.out | 104 +++++++++++++++++++
.../expected/orca_split_update_optimizer.out | 108 ++++++++++++++++++++
src/test/regress/greenplum_schedule | 2 +-
src/test/regress/sql/orca_split_update.sql | 47 +++++++++
21 files changed, 467 insertions(+), 268 deletions(-)
diff --git a/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out b/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out
index 576d61483a8..eef3815de4e 100644
--- a/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out
+++ b/contrib/pax_storage/src/test/isolation2/expected/modify_table_data_corrupt_optimizer.out
@@ -94,28 +94,28 @@ ABORT
-- For planner, this will error out
explain (costs off) update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.b;
- QUERY PLAN
-------------------------------------------------------------------------------------
- Update on tab1
- -> Hash Join
- Hash Cond: (tab3.b = tab1.b)
- -> Seq Scan on tab3
- -> Hash
- -> Redistribute Motion 3:3 (slice1; segments: 3)
- Hash Key: tab1.b
- -> Hash Join
- Hash Cond: (tab2.a = tab1.a)
- -> Seq Scan on tab2
- -> Hash
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: tab1.a
- -> Seq Scan on tab1
- Optimizer: Postgres query optimizer
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Update on tab1
+ -> Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: tab1.b
+ -> Hash Join
+ Hash Cond: (tab2.a = tab1.a)
+ -> Seq Scan on tab2
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Hash Join
+ Hash Cond: (tab3.b = tab1.b)
+ -> Seq Scan on tab3
+ -> Hash
+ -> Seq Scan on tab1
+ Optimizer: GPORCA
(15 rows)
begin;
BEGIN
update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.b;
-ERROR: distribution key of the tuple (0, 1) doesn't belong to current segment (actually from seg0) (nodeModifyTable.c:1790) (seg1 127.0.0.1:7003 pid=31750) (nodeModifyTable.c:1790)
+UPDATE 1
abort;
ABORT
@@ -149,28 +149,29 @@ ABORT
-- For orca, this will error out
explain (costs off) update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.a;
- QUERY PLAN
-------------------------------------------------------------------------------------------
- Update on tab1
- -> Explicit Redistribute Motion 3:3 (slice1; segments: 3)
- -> Hash Join
- Hash Cond: (tab3.a = tab1.b)
- -> Seq Scan on tab3
- -> Hash
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Hash Join
- Hash Cond: (tab2.a = tab1.a)
- -> Seq Scan on tab2
- -> Hash
- -> Redistribute Motion 3:3 (slice3; segments: 3)
- Hash Key: tab1.a
- -> Seq Scan on tab1
- Optimizer: Postgres query optimizer
-(15 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Update on tab1
+ -> Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: tab1.b
+ -> Hash Join
+ Hash Cond: (tab3.a = tab1.b)
+ -> Seq Scan on tab3
+ -> Hash
+ -> Broadcast Motion 3:3 (slice2; segments: 3)
+ -> Hash Join
+ Hash Cond: (tab2.a = tab1.a)
+ -> Seq Scan on tab2
+ -> Hash
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on tab1
+ Optimizer: GPORCA
+(16 rows)
begin;
BEGIN
update tab1 set a = 999 from tab2, tab3 where tab1.a = tab2.a and tab1.b = tab3.a;
-UPDATE 2
+ERROR: distribution key of the tuple (0, 1) doesn't belong to current segment (actually from seg0) (nodeModifyTable.c:1790) (seg1 127.0.0.1:7003 pid=31750) (nodeModifyTable.c:1790)
abort;
ABORT
diff --git a/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out b/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out
index b098d7c2df5..3a848d52520 100644
--- a/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/DML_over_joins_optimizer.out
@@ -24,11 +24,7 @@ insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
analyze r;
analyze s;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -45,11 +41,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -64,29 +56,22 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
explain update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
- Update on s (cost=47.71..49.13 rows=0 width=0)
- -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=47.71..49.13 rows=33 width=52)
- -> Result (cost=47.71..48.46 rows=33 width=52)
- -> HashAggregate (cost=47.71..48.05 rows=33 width=52)
- Group Key: (RowIdExpr)
- -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=3.92..47.63 rows=33 width=52)
- Hash Key: (RowIdExpr)
- -> Hash Join (cost=3.92..46.96 rows=33 width=52)
- Hash Cond: (r.b = s.a)
- -> Seq Scan on r (cost=0.00..34.33 rows=3333 width=10)
- -> Hash (cost=2.67..2.67 rows=100 width=50)
- -> Broadcast Motion 3:3 (slice3; segments: 3) (cost=0.00..2.67 rows=100 width=50)
- -> Seq Scan on s (cost=0.00..1.33 rows=33 width=50)
- Optimizer: Postgres query optimizer
-(14 rows)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ Update on s (cost=0.00..865.12 rows=34 width=1)
+ -> Hash Semi Join (cost=0.00..862.77 rows=100 width=18)
+ Hash Cond: (s.a = r.b)
+ -> Seq Scan on s (cost=0.00..431.00 rows=100 width=18)
+ Filter: (NOT (a IS NULL))
+ -> Hash (cost=431.13..431.13 rows=10000 width=4)
+ -> Result (cost=0.00..431.13 rows=10000 width=4)
+ -> Redistribute Motion 3:3 (slice1; segments: 3) (cost=0.00..431.11 rows=10000 width=4)
+ Hash Key: r.b
+ -> Seq Scan on r (cost=0.00..431.05 rows=3334 width=4)
+ Optimizer: GPORCA
+(11 rows)
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
explain delete from s where exists (select 1 from r where s.a = r.b);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
@@ -121,11 +106,7 @@ create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -142,11 +123,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -161,8 +138,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion3.sql
@@ -182,11 +157,7 @@ create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -203,11 +174,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -222,8 +189,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion4.sql
@@ -243,11 +208,7 @@ create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -264,11 +225,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -283,8 +240,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
-- Test: heap_motion5.sql
@@ -304,11 +259,7 @@ create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 1 from s where r.a = s.a;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = r.b + 1 from s where r.a in (select a from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
@@ -325,11 +276,7 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update r set b = r.b + 4 from s where r.b = s.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
update r set b = b + 1 where b in (select b from s);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
delete from s using r where r.a = s.b;
delete from r;
delete from s;
@@ -344,8 +291,6 @@ delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 28 not found in project list
delete from s where exists (select 1 from r where s.a = r.b);
------------------------------------------------------------
-- Update with Motion:
@@ -370,8 +315,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
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)
@@ -415,8 +358,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
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)
@@ -460,8 +401,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
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)
@@ -505,8 +444,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
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)
@@ -550,8 +487,6 @@ update s set a = r.a from r where r.b = s.b;
-- Statement contains correlated subquery
------------------------------------------------------------
update s set b = (select min(a) from r where b = s.b);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
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)
@@ -929,8 +864,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
(2 rows)
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 14 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+---+-----
@@ -940,8 +873,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 13 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+----+-----
@@ -950,8 +881,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
(2 rows)
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 13 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
----+----+-----
@@ -961,8 +890,6 @@ SELECT a,b,c FROM update_test ORDER BY a,c;
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 14 not found in project list
SELECT a,b,c FROM update_test ORDER BY a,c;
a | b | c
-----+----+-----
@@ -986,8 +913,6 @@ INSERT INTO t2 (id, data1, data2) VALUES (4, 3, 104);
-- (depending upon exactly where the data is stored, which will vary depending
-- upon the number of segments; in my case, I used only 2 segments).
UPDATE t1 SET data2 = t2.data2 FROM t2 WHERE t1.data1 = t2.data1;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 22 not found in project list
SELECT * from t1;
id | data1 | data2
----+-------+-------
@@ -1183,8 +1108,6 @@ select sales.* from sales,s,r where sales.id = s.b and sales.month = r.b+1;
(10 rows)
update sales set month = month+1 from r,s where sales.id = s.b and sales.month = r.b+1;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 34 not found in project list
select sales.* from sales,s,r where sales.id = s.b and sales.month = r.b+2;
id | year | month | day | region
----+------+-------+-----+-----------
@@ -1210,8 +1133,6 @@ select sales.* from sales where id in (select s.b from s, r where s.a = r.b) and
(4 rows)
update sales 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 Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 52 not found in project list
select sales.* from sales 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
----+------+-------+-----+------------
@@ -1264,8 +1185,6 @@ select r.* from r,s,sales where s.a = sales.day and sales.month = r.b;
(6 rows)
update r set b = r.b + 1 from s,sales where s.a = sales.day and sales.month = r.b;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 34 not found in project list
select r.* from r,s,sales where s.a = sales.day and sales.month = r.b-1;
a | b
---+----
@@ -1606,8 +1525,6 @@ select * from s where a = 4 and a in (select b from m);
(2 rows)
update s set b = b + 1 where a = 4 and a in (select b from m);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
select * from s where a = 4 and a in (select b from m);
a | b
---+----
@@ -1716,8 +1633,6 @@ select * from s where a = 4 and a in (select b from m);
PREPARE plan2 AS update s set b = b + 1 where a = 4 and a in (select b from m);
EXECUTE plan2;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
select * from s where a = 4 and a in (select b from m);
a | b
---+----
diff --git a/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out b/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out
index d6fd64b18f4..57e499bb091 100644
--- a/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/direct_dispatch_optimizer.out
@@ -50,8 +50,9 @@ INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
-- DO direct dispatch
-- Known_opt_diff: MPP-21346
update direct_test set value = 'horse' where key = 100;
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
-- verify
select * from direct_test order by key, value;
INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
@@ -123,8 +124,9 @@ INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
-- DO direct dispatch
-- Known_opt_diff: MPP-21346
update direct_test_two_column set value = 'horse' where key1 = 100 and key2 = 101;
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
-- verify
select * from direct_test_two_column order by key1, key2, value;
INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
@@ -176,17 +178,17 @@ INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL cont
-- hash all these values to the same content! (and so test would change)
--
update direct_test set value = 'pig' where key in (1,2,3,4,5);
-INFO: (slice 0) Dispatch command to ALL contents: 1 0 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 1 0 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
update direct_test_two_column set value = 'pig' where key1 = 100 and key2 in (1,2,3,4);
-INFO: (slice 0) Dispatch command to PARTIAL contents: 1 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to PARTIAL contents: 1 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
update direct_test_two_column set value = 'pig' where key1 in (100,101,102,103,104) and key2 in (1);
-INFO: (slice 0) Dispatch command to ALL contents: 1 0 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 1 0 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
update direct_test_two_column set value = 'pig' where key1 in (100,101) and key2 in (1,2);
-INFO: (slice 0) Dispatch command to PARTIAL contents: 1 2
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to PARTIAL contents: 1 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
-- Multiple row update, where clause lists values which all hash to same segment
-- DO direct dispatch
-- CAN'T IMPLEMENT THIS TEST BECAUSE THE # of segments changes again (unless we use a # of segments function, and exploit the simple nature of int4 hashing -- can we do that?)
@@ -274,8 +276,9 @@ INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
prepare test_update (int) as update direct_test set value = 'boo' where key = $1;
-- Known_opt_diff: MPP-21346
execute test_update(2);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
select * from direct_test;
INFO: (slice 1) Dispatch command to ALL contents: 0 1 2
key | value
@@ -342,9 +345,10 @@ INFO: (slice 1) Dispatch command to SINGLE content
-- main plan is direct dispatch and also has init plans
update direct_dispatch_bar set id2 = 1 where id1 = 1 and exists (select * from direct_dispatch_foo where id = 2);
-INFO: (slice 2) Dispatch command to SINGLE content
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to PARTIAL contents: 0 1
+INFO: (slice 2) Dispatch command to ALL contents: 0 1 2
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: (slice 1) Dispatch command to SINGLE content
+INFO: Distributed transaction command 'Distributed Commit (one-phase)' to ALL contents: 0 1 2
-- init plan to see how transaction escalation happens
-- Known_opt_diff: MPP-21346
delete from direct_dispatch_foo where id = (select max(id2) from direct_dispatch_bar where id1 = 5);
@@ -528,23 +532,29 @@ INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGL
-- update case
prepare p2 as update test_prepare set j =2 where i =$1;
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
execute p2(1);
-INFO: (slice 0) Dispatch command to SINGLE content
-INFO: Distributed transaction command 'Distributed Commit (one-phase)' to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 0 1 2
+INFO: Distributed transaction command 'Distributed Commit Prepared' to ALL contents: 0 1 2
-- select case
prepare p3 as select * from test_prepare where i =$1;
execute p3(1);
@@ -1526,9 +1536,9 @@ INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SING
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;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
abort;
-INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to SINGLE content
+INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 0 1 2
-- insert
begin;
-- only consider target's policy, will direct dispatch
diff --git a/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out b/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out
index de85e6ac814..5c93839b7a3 100644
--- a/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/gporca_optimizer.out
@@ -10509,8 +10509,6 @@ show optimizer;
(1 row)
update can_set_tag_target set y = y + 1;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 12 not found in project list
select count(1) from can_set_tag_audit;
count
-------
@@ -14504,30 +14502,25 @@ update window_agg_test t
set i = tt.i
from (select (min(i) over (order by j)) as i, j from window_agg_test) tt
where t.j = tt.j;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
- Update on window_agg_test t (cost=3699.81..185385.16 rows=0 width=0)
- -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) (cost=3699.81..185385.16 rows=2471070 width=78)
- -> Hash Join (cost=3699.81..135963.76 rows=2471070 width=78)
- Hash Cond: (tt.j = t.j)
- -> Redistribute Motion 1:3 (slice2; segments: 1) (cost=2446.06..6966.31 rows=28700 width=40)
- Hash Key: tt.j
- -> Subquery Scan on tt (cost=2446.06..5818.31 rows=86100 width=40)
- -> WindowAgg (cost=2446.06..4957.31 rows=86100 width=8)
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Update on window_agg_test t (cost=0.00..862.02 rows=1 width=1)
+ -> Result (cost=0.00..0.00 rows=0 width=0)
+ -> Explicit Redistribute Motion 1:3 (slice1; segments: 1) (cost=0.00..862.00 rows=1 width=18)
+ -> Hash Join (cost=0.00..862.00 rows=1 width=18)
+ Hash Cond: (t.j = window_agg_test.j)
+ -> Gather Motion 3:1 (slice2; segments: 3) (cost=0.00..431.00 rows=1 width=14)
+ -> Seq Scan on window_agg_test t (cost=0.00..431.00 rows=1 width=14)
+ -> Hash (cost=431.00..431.00 rows=1 width=8)
+ -> WindowAgg (cost=0.00..431.00 rows=1 width=8)
Order By: window_agg_test.j
- -> Gather Motion 3:1 (slice3; segments: 3) (cost=2446.06..3665.81 rows=86100 width=8)
+ -> Gather Motion 3:1 (slice3; segments: 3) (cost=0.00..431.00 rows=1 width=8)
Merge Key: window_agg_test.j
- -> Sort (cost=2446.06..2517.81 rows=28700 width=8)
+ -> Sort (cost=0.00..431.00 rows=1 width=8)
Sort Key: window_agg_test.j
- -> Seq Scan on window_agg_test (cost=0.00..321.00 rows=28700 width=8)
- -> Hash (cost=895.00..895.00 rows=28700 width=46)
- -> Redistribute Motion 3:3 (slice4; segments: 3) (cost=0.00..895.00 rows=28700 width=46)
- Hash Key: t.j
- -> Seq Scan on window_agg_test t (cost=0.00..321.00 rows=28700 width=46)
- Optimizer: Postgres query optimizer
-(19 rows)
+ -> Seq Scan on window_agg_test (cost=0.00..431.00 rows=1 width=8)
+ Optimizer: GPORCA
+(16 rows)
----------------------------------
-- Test ORCA support for const TVF
diff --git a/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out b/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out
index 5d26bf6dfa8..c6d731598d6 100644
--- a/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/hash_index_optimizer.out
@@ -156,8 +156,6 @@ SELECT * FROM hash_f8_heap
UPDATE hash_i4_heap
SET random = 1
WHERE hash_i4_heap.seqno = 1492;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i1492, h.random AS i1
FROM hash_i4_heap h
WHERE h.random = 1;
@@ -180,8 +178,6 @@ SELECT h.seqno AS i20000
UPDATE hash_name_heap
SET random = '0123456789abcdef'::name
WHERE hash_name_heap.seqno = 6543;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i6543, h.random AS c0_to_f
FROM hash_name_heap h
WHERE h.random = '0123456789abcdef'::name;
@@ -206,8 +202,6 @@ SELECT h.seqno AS emptyset
UPDATE hash_txt_heap
SET random = '0123456789abcdefghijklmnop'::text
WHERE hash_txt_heap.seqno = 4002;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i4002, h.random AS c0_to_p
FROM hash_txt_heap h
WHERE h.random = '0123456789abcdefghijklmnop'::text;
@@ -230,8 +224,6 @@ SELECT h.seqno AS t20000
UPDATE hash_f8_heap
SET random = '-1234.1234'::float8
WHERE hash_f8_heap.seqno = 8906;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 11 not found in project list
SELECT h.seqno AS i8096, h.random AS f1234_1234
FROM hash_f8_heap h
WHERE h.random = '-1234.1234'::float8;
diff --git a/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out b/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out
index f5303b87634..83c7364d7df 100644
--- a/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/partition_prune_optimizer.out
@@ -4615,13 +4615,15 @@ explain (costs off) delete from inh_lp where a = 1;
-- Ensure we don't exclude normal relations when we only expect to exclude
-- inheritance children
explain (costs off) update inh_lp1 set value = 10 where a = 2;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+--------------------------------------------
Update on inh_lp1
- -> Seq Scan on inh_lp1
- Filter: (a = 2)
- Optimizer: Postgres query optimizer
-(4 rows)
+ -> Result
+ -> Result
+ -> Result
+ One-Time Filter: false
+ Optimizer: GPORCA
+(6 rows)
drop table inh_lp cascade;
NOTICE: drop cascades to 2 other objects
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out
index e1e10d4eb9b..84e0e4cf28b 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_correlated_query_optimizer.out
@@ -1607,8 +1607,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = (select y from qp_csq_t2 where x=a) where b < 8;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 21 not found in project list
select * from qp_csq_t4 order by a;
a | b
----+---
@@ -1619,8 +1617,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = 9999 where qp_csq_t4.a = (select max(x) from qp_csq_t2);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 23 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1631,8 +1627,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = (select max(y) from qp_csq_t2 where x=a) where qp_csq_t4.a = (select min(x) from qp_csq_t2);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1643,8 +1637,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = 8888 where (select (y*2)>b from qp_csq_t2 where a=x);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 22 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1655,8 +1647,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update qp_csq_t4 set a = 3333 where qp_csq_t4.a in (select x from qp_csq_t2);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 25 not found in project list
select * from qp_csq_t4 order by a;
a | b
------+---
@@ -1667,8 +1657,6 @@ select * from qp_csq_t4 order by a;
(4 rows)
update D set i = 11111 from C where C.i = D.i and exists (select C.j from C,B where C.j = B.j and D.j < 10);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 45 not found in project list
select * from D;
i | j
-------+----
@@ -1680,8 +1668,6 @@ select * from D;
(5 rows)
update D set i = 22222 from C where C.i = D.i and not exists (select C.j from C,B where C.j = B.j and D.j < 10);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 45 not found in project list
select * from D;
i | j
-------+----
@@ -2405,8 +2391,6 @@ analyze product;
commit;
UPDATE product SET product_price = product_price * .9
where product_name NOT IN (SELECT DISTINCT product_name FROM product_order);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 35 not found in project list
SELECT * FROM product order by product_name;
product_name | product_price | quantity_on_hand | last_stock_date
--------------+---------------+------------------+-----------------
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out
index e0a411212f6..fb8cdce2f86 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_orca_fallback_optimizer.out
@@ -53,8 +53,6 @@ DETAIL: Falling back to Postgres-based planner because GPORCA does not support
set optimizer_enable_dml_constraints=on;
explain update constr_tab set b = 10;
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 13 not found in project list
QUERY PLAN
-----------------------------------------------------------------------
Update on constr_tab (cost=0.00..271.00 rows=0 width=0)
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out
index 4a5a3be132b..cacc38d3daf 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_subquery_optimizer.out
@@ -1633,8 +1633,6 @@ select * from TblUp1;
(3 rows)
update TblUp2 set a=100 where a not in (select a from TblUp4);
-INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
-DETAIL: DXL-to-PlStmt Translation: Attribute number 26 not found in project list
select * from TblUp2;
a | b
-----+---
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out
index cbbbcfdce26..4464a24a003 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_targeted_dispatch_optimizer.out
@@ -87,12 +87,12 @@ INFO: (slice 1) Dispatch command to SINGLE content
(1 row)
update key_value_table set value=300 where key =200;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
savepoint s;
update key_value_table set value=200 where key =300;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
update key_value_table set value=300 where key =200;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
rollback to s;
commit;
INFO: Distributed transaction command 'Distributed Prepare' to ALL contents: 2 0 1
@@ -107,7 +107,7 @@ INFO: (slice 1) Dispatch command to SINGLE content
savepoint s;
update key_value_table set value=200 where key =300;
-INFO: (slice 0) Dispatch command to SINGLE content
+INFO: (slice 0) Dispatch command to ALL contents: 0 1 2
rollback;
INFO: Distributed transaction command 'Distributed Abort (No Prepared)' to ALL contents: 2 0 1
savepoint s;
diff --git a/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out b/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out
index da58390502c..845c01846c6 100644
--- a/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/qp_union_intersect_optimizer.out
@@ -1736,7 +1736,8 @@ reset optimizer_segments;
--SELECT COUNT(DISTINCT(a)) FROM dml_union_r;
-- @description union_update_test31: Negative Tests more than one row returned by a sub-query used as an expression
UPDATE dml_union_r SET b = ( SELECT a FROM dml_union_r EXCEPT ALL SELECT a FROM dml_union_s);
-ERROR: more than one row returned by a subquery used as an expression
+DETAIL: Expected no more than one row to be returned by expression
+ERROR: one or more assertions failed
--
-- Test mixing a set-returning function, which can be evaluated anywhere,
-- (it has General locus) and a diststributed table, in an Append.
diff --git a/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out b/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out
index 50aa3dbb064..014fc5ab235 100644
--- a/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out
+++ b/contrib/pax_storage/src/test/regress/expected/update_gp_optimizer.out
@@ -122,36 +122,46 @@ EXPLAIN (COSTS OFF) UPDATE keo1 SET user_vie_act_cntr_marg_cum = 234.682 FROM
(SELECT min (keo4.keo_para_budget_date) FROM keo4)))
) t1
WHERE t1.user_vie_project_code_pk = keo1.user_vie_project_code_pk;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on keo1
- InitPlan 3 (returns $2) (slice3)
- -> Aggregate
- InitPlan 2 (returns $1) (slice5)
- -> Gather Motion 3:1 (slice6; segments: 3)
- InitPlan 1 (returns $0) (slice7)
- -> Aggregate
- -> Gather Motion 3:1 (slice8; segments: 3)
- -> Seq Scan on keo4
- -> Seq Scan on keo4 keo4_1
- Filter: ((keo_para_budget_date)::text = $0)
- -> Gather Motion 3:1 (slice4; segments: 3)
- -> Seq Scan on keo3
- Filter: ((bky_per)::text = ($1)::text)
-> Hash Join
- Hash Cond: ((a.user_vie_project_code_pk)::text = (b.projects_pk)::text)
- -> Hash Join
- Hash Cond: ((a.user_vie_project_code_pk)::text = (keo1.user_vie_project_code_pk)::text)
- -> Broadcast Motion 3:3 (slice1; segments: 3)
- -> Seq Scan on keo1 a
- Filter: ((user_vie_fiscal_year_period_sk)::text = $2)
- -> Hash
- -> Seq Scan on keo1
+ Hash Cond: ((keo1.user_vie_project_code_pk)::text = (a.user_vie_project_code_pk)::text)
+ -> Seq Scan on keo1
-> Hash
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- -> Seq Scan on keo2 b
- Optimizer: Postgres query optimizer
-(27 rows)
+ -> Broadcast Motion 3:3 (slice1; segments: 3)
+ -> Hash Join
+ Hash Cond: ((a.user_vie_project_code_pk)::text = (b.projects_pk)::text)
+ -> Hash Join
+ Hash Cond: ((max((keo3.sky_per)::text)) = (a.user_vie_fiscal_year_period_sk)::text)
+ -> Redistribute Motion 1:3 (slice2; segments: 1)
+ -> Aggregate
+ -> Hash Join
+ Hash Cond: ((keo3.bky_per)::text = (keo4_1.keo_para_required_period)::text)
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on keo3
+ -> Hash
+ -> Assert
+ Assert Cond: ((row_number() OVER (?)) = 1)
+ -> WindowAgg
+ -> Gather Motion 3:1 (slice4; segments: 3)
+ -> Hash Join
+ Hash Cond: ((min((keo4.keo_para_budget_date)::text)) = (keo4_1.keo_para_budget_date)::text)
+ -> Redistribute Motion 1:3 (slice5; segments: 1)
+ -> Aggregate
+ -> Gather Motion 3:1 (slice6; segments: 3)
+ -> Seq Scan on keo4
+ -> Hash
+ -> Broadcast Motion 3:3 (slice7; segments: 3)
+ -> Seq Scan on keo4 keo4_1
+ -> Hash
+ -> Broadcast Motion 3:3 (slice8; segments: 3)
+ -> Seq Scan on keo1 a
+ -> Hash
+ -> Broadcast Motion 3:3 (slice9; segments: 3)
+ -> Seq Scan on keo2 b
+ Optimizer: GPORCA
+(37 rows)
UPDATE keo1 SET user_vie_act_cntr_marg_cum = 234.682 FROM
( SELECT a.user_vie_project_code_pk FROM keo1 a INNER JOIN keo2 b
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4671b2bd892..6ab7bba836a 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -755,6 +755,28 @@ ExecInitUpdateProjection(ModifyTableState *mtstate,
resultRelInfo->ri_newTupleSlot,
&mtstate->ps);
+ /*
+ * The projection copies a column from the old tuple whenever the subplan
+ * does not assign it. Record whether that happens at all: when the
+ * subplan assigns every live column the old tuple is never read, and the
+ * caller can skip fetching it. ORCA emits such a target list for a
+ * non-split UPDATE, which is the only way an append-optimized table can
+ * run one, since it cannot fetch a tuple by tid.
+ */
+ resultRelInfo->ri_projectNewNeedsOldTuple = false;
+ for (int attno = 1; attno <= relDesc->natts; attno++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(relDesc, attno - 1);
+
+ if (attr->attisdropped)
+ continue;
+ if (!list_member_int(updateColnos, attno))
+ {
+ resultRelInfo->ri_projectNewNeedsOldTuple = true;
+ break;
+ }
+ }
+
resultRelInfo->ri_projectNewInfoValid = true;
}
@@ -4319,6 +4341,17 @@ ExecModifyTable(PlanState *pstate)
/* Use the wholerow junk attr as the old tuple. */
ExecForceStoreHeapTuple(oldtuple, oldSlot, false);
}
+ else if (!resultRelInfo->ri_projectNewNeedsOldTuple &&
+ RelationIsNonblockRelation(resultRelInfo->ri_RelationDesc))
+ {
+ /*
+ * The plan slot already carries every column, so the
+ * old tuple is not read. Skipping the fetch is what
+ * lets append-optimized tables run a non-split UPDATE
+ * at all: they cannot fetch a tuple by tid.
+ */
+ ExecStoreAllNullTuple(oldSlot);
+ }
else
{
/* Fetch the most recent version of old tuple. */
diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
index 3abfd5fed5a..4b9ae09b062 100644
--- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
+++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
@@ -5337,11 +5337,6 @@ CTranslatorDXLToPlStmt::TranslateDXLDml(
IMDId *mdid_target_table = phy_dml_dxlop->GetDXLTableDescr()->MDId();
const IMDRelation *md_rel = m_md_accessor->RetrieveRel(mdid_target_table);
- if (md_rel->IsNonBlockTable())
- {
- isSplit = true; // AO tables are always use split updates
- }
-
if (md_rel->IsPartitioned())
{
dml->forceTupleRouting = true;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index ce0c00347be..ffb88de79cc 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -498,6 +498,14 @@ typedef struct ResultRelInfo
/* Have the projection and the slots above been initialized? */
bool ri_projectNewInfoValid;
+ /*
+ * Does ri_projectNew read any column from the old tuple? It does not when
+ * the subplan assigns every live column of the relation, which is what
+ * ORCA emits for a non-split UPDATE. Append-optimized tables cannot fetch
+ * a tuple by tid at all, so this is what lets them run such a plan.
+ */
+ bool ri_projectNewNeedsOldTuple;
+
/* updates do LockTuple() before oldtup read; see README.tuplock */
bool ri_needLockTagTuple;
diff --git a/src/test/isolation2/output/uao/parallel_update_optimizer.source b/src/test/isolation2/output/uao/parallel_update_optimizer.source
index 35077903916..2a4c63ec5a3 100644
--- a/src/test/isolation2/output/uao/parallel_update_optimizer.source
+++ b/src/test/isolation2/output/uao/parallel_update_optimizer.source
@@ -21,9 +21,9 @@ UPDATE 1
ao | ExclusiveLock | relation | master
(1 row)
2: SELECT * FROM locktest_segments WHERE coalesce = 'ao';
- coalesce | mode | locktype | node
-----------+---------------+----------+-----------
- ao | ExclusiveLock | relation | 1 segment
+ coalesce | mode | locktype | node
+----------+---------------+----------+------------
+ ao | ExclusiveLock | relation | n segments
(1 row)
-- The case here should update a tuple at the same seg with(2).
-- Under jump hash, (2) and (3) are on the same seg(seg0).
diff --git a/src/test/regress/expected/ao_locks_optimizer.out b/src/test/regress/expected/ao_locks_optimizer.out
index ad89902c8a1..5a723c32950 100644
--- a/src/test/regress/expected/ao_locks_optimizer.out
+++ b/src/test/regress/expected/ao_locks_optimizer.out
@@ -99,9 +99,9 @@ SELECT * FROM locktest_master where coalesce = 'ao_locks_table' or
SELECT * FROM locktest_segments where coalesce = 'ao_locks_table' or
coalesce like 'aovisimap%' or coalesce like 'aoseg%';
- coalesce | mode | locktype | node
------------------+------------------+----------+-----------
- ao_locks_table | ExclusiveLock | relation | 1 segment
+ coalesce | mode | locktype | node
+-----------------+------------------+----------+------------
+ ao_locks_table | ExclusiveLock | relation | n segments
aovisimap table | RowExclusiveLock | relation | 1 segment
aovisimap index | RowExclusiveLock | relation | 1 segment
(3 rows)
diff --git a/src/test/regress/expected/orca_split_update.out b/src/test/regress/expected/orca_split_update.out
new file mode 100644
index 00000000000..5c8d9ac53e6
--- /dev/null
+++ b/src/test/regress/expected/orca_split_update.out
@@ -0,0 +1,104 @@
+--
+-- An UPDATE that does not move the row between segments runs as an in-place
+-- update on append-optimized tables too: the storage engine turns it into a
+-- delete plus an append by itself, exactly as the Postgres planner has always
+-- assumed. ORCA used to be unable to emit that plan -- the executor fetched
+-- the old tuple by tid, which AO cannot do -- so the DXL-to-PlStmt translation
+-- forced a split update, failed to find the DMLAction column, and silently
+-- fell back to the planner.
+--
+CREATE SCHEMA orca_split_update;
+SET search_path TO orca_split_update;
+-- make a fallback visible instead of silent
+SET optimizer_trace_fallback = on;
+CREATE TABLE ao_su (id bigint, v bigint) USING ao_row DISTRIBUTED BY (id);
+CREATE TABLE aocs_su (id bigint, v bigint) USING ao_column DISTRIBUTED BY (id);
+CREATE TABLE heap_su (id bigint, v bigint) USING heap DISTRIBUTED BY (id);
+INSERT INTO ao_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO aocs_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO heap_su SELECT g, g FROM generate_series(1, 10) g;
+-- Updating a non-distribution column: the row stays on its segment, so the
+-- plan is a plain ModifyTable with neither a Split Update nor a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+-------------------------------------
+ Update on ao_su
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE aocs_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+-------------------------------------
+ Update on aocs_su
+ -> Seq Scan on aocs_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+-- Updating the distribution column moves the row, which still needs the split
+-- and a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET id = 999 WHERE id = 1;
+ QUERY PLAN
+---------------------------------------------------------------
+ Update on ao_su
+ -> Explicit Redistribute Motion 1:3 (slice1; segments: 1)
+ -> Split Update
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+-- Heap has always taken this path; it must be unaffected.
+EXPLAIN (COSTS OFF) UPDATE heap_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+-------------------------------------
+ Update on heap_su
+ -> Seq Scan on heap_su
+ Filter: (id = 1)
+ Optimizer: Postgres query optimizer
+(4 rows)
+
+-- The update must not leave the old tuple version behind.
+UPDATE ao_su SET v = 999 WHERE id = 1;
+UPDATE aocs_su SET v = 999 WHERE id = 1;
+UPDATE heap_su SET v = 999 WHERE id = 1;
+SELECT count(*) AS ao_rows FROM ao_su;
+ ao_rows
+---------
+ 10
+(1 row)
+
+SELECT count(*) AS aocs_rows FROM aocs_su;
+ aocs_rows
+-----------
+ 10
+(1 row)
+
+SELECT id, v FROM ao_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM aocs_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM heap_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+DROP SCHEMA orca_split_update CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ao_su
+drop cascades to table aocs_su
+drop cascades to table heap_su
diff --git a/src/test/regress/expected/orca_split_update_optimizer.out b/src/test/regress/expected/orca_split_update_optimizer.out
new file mode 100644
index 00000000000..6a223aa131e
--- /dev/null
+++ b/src/test/regress/expected/orca_split_update_optimizer.out
@@ -0,0 +1,108 @@
+--
+-- An UPDATE that does not move the row between segments runs as an in-place
+-- update on append-optimized tables too: the storage engine turns it into a
+-- delete plus an append by itself, exactly as the Postgres planner has always
+-- assumed. ORCA used to be unable to emit that plan -- the executor fetched
+-- the old tuple by tid, which AO cannot do -- so the DXL-to-PlStmt translation
+-- forced a split update, failed to find the DMLAction column, and silently
+-- fell back to the planner.
+--
+CREATE SCHEMA orca_split_update;
+SET search_path TO orca_split_update;
+-- make a fallback visible instead of silent
+SET optimizer_trace_fallback = on;
+CREATE TABLE ao_su (id bigint, v bigint) USING ao_row DISTRIBUTED BY (id);
+CREATE TABLE aocs_su (id bigint, v bigint) USING ao_column DISTRIBUTED BY (id);
+CREATE TABLE heap_su (id bigint, v bigint) USING heap DISTRIBUTED BY (id);
+INSERT INTO ao_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO aocs_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO heap_su SELECT g, g FROM generate_series(1, 10) g;
+-- Updating a non-distribution column: the row stays on its segment, so the
+-- plan is a plain ModifyTable with neither a Split Update nor a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+--------------------------
+ Update on ao_su
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE aocs_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+---------------------------
+ Update on aocs_su
+ -> Seq Scan on aocs_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(4 rows)
+
+-- Updating the distribution column moves the row, which still needs the split
+-- and a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET id = 999 WHERE id = 1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Update on ao_su
+ -> Result
+ One-Time Filter: true
+ -> Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: id
+ -> Split Update
+ -> Seq Scan on ao_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(10 rows)
+
+-- Heap has always taken this path; it must be unaffected.
+EXPLAIN (COSTS OFF) UPDATE heap_su SET v = 999 WHERE id = 1;
+ QUERY PLAN
+---------------------------
+ Update on heap_su
+ -> Seq Scan on heap_su
+ Filter: (id = 1)
+ Optimizer: GPORCA
+(4 rows)
+
+-- The update must not leave the old tuple version behind.
+UPDATE ao_su SET v = 999 WHERE id = 1;
+UPDATE aocs_su SET v = 999 WHERE id = 1;
+UPDATE heap_su SET v = 999 WHERE id = 1;
+SELECT count(*) AS ao_rows FROM ao_su;
+ ao_rows
+---------
+ 10
+(1 row)
+
+SELECT count(*) AS aocs_rows FROM aocs_su;
+ aocs_rows
+-----------
+ 10
+(1 row)
+
+SELECT id, v FROM ao_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM aocs_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+SELECT id, v FROM heap_su WHERE id <= 2 ORDER BY id;
+ id | v
+----+-----
+ 1 | 999
+ 2 | 2
+(2 rows)
+
+DROP SCHEMA orca_split_update CASCADE;
+NOTICE: drop cascades to 3 other objects
+DETAIL: drop cascades to table ao_su
+drop cascades to table aocs_su
+drop cascades to table heap_su
diff --git a/src/test/regress/greenplum_schedule b/src/test/regress/greenplum_schedule
index 5e2a83ee5a5..29a51740eba 100755
--- a/src/test/regress/greenplum_schedule
+++ b/src/test/regress/greenplum_schedule
@@ -65,7 +65,7 @@ test: leastsquares opr_sanity_gp decode_expr bitmapscan bitmapscan_ao case_gp li
# below test(s) inject faults so each of them need to be in a separate group
test: gpcopy
-test: orca_static_pruning orca_groupingsets_fallbacks
+test: orca_static_pruning orca_groupingsets_fallbacks orca_split_update
test: filter gpctas gpdist gpdist_opclasses gpdist_legacy_opclasses matrix sublink table_functions olap_setup complex opclass_ddl information_schema guc_env_var gp_explain distributed_transactions explain_format olap_plans gp_copy_dtx
# below test(s) inject faults so each of them need to be in a separate group
test: guc_gp
diff --git a/src/test/regress/sql/orca_split_update.sql b/src/test/regress/sql/orca_split_update.sql
new file mode 100644
index 00000000000..717cc5b7098
--- /dev/null
+++ b/src/test/regress/sql/orca_split_update.sql
@@ -0,0 +1,47 @@
+--
+-- An UPDATE that does not move the row between segments runs as an in-place
+-- update on append-optimized tables too: the storage engine turns it into a
+-- delete plus an append by itself, exactly as the Postgres planner has always
+-- assumed. ORCA used to be unable to emit that plan -- the executor fetched
+-- the old tuple by tid, which AO cannot do -- so the DXL-to-PlStmt translation
+-- forced a split update, failed to find the DMLAction column, and silently
+-- fell back to the planner.
+--
+CREATE SCHEMA orca_split_update;
+SET search_path TO orca_split_update;
+
+-- make a fallback visible instead of silent
+SET optimizer_trace_fallback = on;
+
+CREATE TABLE ao_su (id bigint, v bigint) USING ao_row DISTRIBUTED BY (id);
+CREATE TABLE aocs_su (id bigint, v bigint) USING ao_column DISTRIBUTED BY (id);
+CREATE TABLE heap_su (id bigint, v bigint) USING heap DISTRIBUTED BY (id);
+
+INSERT INTO ao_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO aocs_su SELECT g, g FROM generate_series(1, 10) g;
+INSERT INTO heap_su SELECT g, g FROM generate_series(1, 10) g;
+
+-- Updating a non-distribution column: the row stays on its segment, so the
+-- plan is a plain ModifyTable with neither a Split Update nor a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET v = 999 WHERE id = 1;
+EXPLAIN (COSTS OFF) UPDATE aocs_su SET v = 999 WHERE id = 1;
+
+-- Updating the distribution column moves the row, which still needs the split
+-- and a Motion.
+EXPLAIN (COSTS OFF) UPDATE ao_su SET id = 999 WHERE id = 1;
+
+-- Heap has always taken this path; it must be unaffected.
+EXPLAIN (COSTS OFF) UPDATE heap_su SET v = 999 WHERE id = 1;
+
+-- The update must not leave the old tuple version behind.
+UPDATE ao_su SET v = 999 WHERE id = 1;
+UPDATE aocs_su SET v = 999 WHERE id = 1;
+UPDATE heap_su SET v = 999 WHERE id = 1;
+
+SELECT count(*) AS ao_rows FROM ao_su;
+SELECT count(*) AS aocs_rows FROM aocs_su;
+SELECT id, v FROM ao_su WHERE id <= 2 ORDER BY id;
+SELECT id, v FROM aocs_su WHERE id <= 2 ORDER BY id;
+SELECT id, v FROM heap_su WHERE id <= 2 ORDER BY id;
+
+DROP SCHEMA orca_split_update CASCADE;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org