GitHub user Alena0704 added a comment to the discussion: [Ideas] Add ORCA
intra-segment parallel scan support for partitioned AO tables
After merging commits for this feature, I measured again and it looks
significantly better. I don't see any remaining problems here, so I'm closing
this discussion and the follow-up issue. The report measurement is bellow.
October 23, 2026. Comparison: Cloudberry main, Apache REL_2_STABLE with the
Parallel Append backport, and OpenGPDB.
## Tables, partitions, and AO file sizes
Each table contained the same **6,000,000-row dataset**, distributed by `id`
across **3 primary segments**, with **13 daily AO row-oriented partitions**
covering January 1–13, 2020. The partition count was fixed in every test. Total
AO data size was approximately **540.6 MB per table**; the tests varied how
these data were split into physical files.
The same DDL and generated data were used for all three layouts. Example for
the one-file layout:
```sql
SET gp_appendonly_insert_files = 0;
CREATE TABLE public.ao13 (
id bigint, d date, col1 int, col2 int, pad text
)
WITH (appendonly=true, orientation=row)
DISTRIBUTED BY (id)
PARTITION BY RANGE (d) (
START ('2020-01-01'::date)
END ('2020-01-14'::date)
EVERY ('1 day'::interval)
);
INSERT INTO public.ao13
SELECT g, '2020-01-01'::date + (g % 13),
g % 97, g % 89, repeat('x', 50)
FROM generate_series(1, 6000000) g;
ANALYZE public.ao13;
```
The two `files4_*` tables are in schema `ao_files_20261015`.
| Table | `gp_appendonly_insert_files` |
`gp_appendonly_insert_files_tuples_range` | Populated files per
partition/segment | Mean physical file size, MB |
| --- | ---: | ---: | ---: | ---: |
| `public.ao13` | 0 | Not used | 1 | 13.86 |
| `files4_default` | 4 | 100000 | 2 | 6.93 |
| `files4_balanced` | 4 | 10000 | 4 | 3.47 |
The resulting totals were **39, 78, and 156 populated AO files**. Sizes above
are averages in decimal MB; individual files were not all equal in size.
Each partition/segment held 153,155–154,613 rows. With a 100,000-row rotation
interval, only two files were populated: 100,000 rows in one and 53,155–54,613
in the other, despite the configured four-file limit. A 10,000-row interval
rotated writes across all four files, producing three files of 40,000 rows and
one of 33,155–34,613 rows.
OpenGPDB used concurrent insert transactions to reproduce the same file
layouts: two writers split rows at local rank 100,000; four writers used
`((local_rank - 1) / 10000) % 4`.
The query counted the entire table, scanning all 13 partitions without a date
filter. Example for the four-file layout with ORCA Parallel Append enabled:
```sql
SET optimizer = on;
SET enable_parallel = on;
SET max_parallel_workers_per_gather = 4;
SET optimizer_enable_dynamictablescan = on;
SET optimizer_disable_dynamic_table_scan = off;
SET optimizer_enable_parallel_append = on;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM ao_files_20261015.files4_balanced;
```
The off comparison set `optimizer_enable_parallel_append=off` in the same
binary. The other layouts used the corresponding table names above. OpenGPDB
used its ORCA Dynamic Seq Scan baseline. Every result was **6,000,000**.
**Plan verification:** all 132 accepted ORCA-on plans for main and Apache
REL_2_STABLE across `-O0/-O3`, warm and cold cache, contained an executed
`Parallel Append` node with nonzero `actual rows` and `loops`, `Gather Motion
12:1`, and `Optimizer: GPORCA`. This covers 120 measured runs and 12 warm-up
runs. All 132 corresponding ORCA-off plans contained `Dynamic Seq Scan` and
`Gather Motion 3:1`, with no `Parallel Append`.
Excerpt from an [actual four-file, cold-cache -O3 plan on
main](remote-o3-20261023/cold-cache/cases/011-main-4_files-orca_append_on-r1/plan.txt):
```text
Finalize Aggregate (actual rows=1 loops=1)
-> Gather Motion 12:1 (slice1; segments: 12) (actual rows=12 loops=1)
-> Partial Aggregate (actual rows=1 loops=1)
-> Parallel Append (actual rows=495974 loops=1)
```
## Measurement conditions
Ryzen 7 3750H, 4 cores / 8 logical CPUs, approximately 13 GiB RAM, NVMe SSD;
one coordinator and three primary segments, `shared_buffers=16MB` per instance.
GCC/G++ 11.4.0, `-O0 -g` or `-O3 -g`, with debug and assertions enabled.
**Warm cache:** one warm-up per case, excluded from results, followed by five
measured runs in a persistent psql session. **Cold cache:** five measured runs,
each after stopping clusters, clearing the OS cache, and verifying zero
resident AO data pages immediately before the query. The SSD hardware cache was
not cleared. Results below are median **Execution Time in milliseconds**,
excluding planning, cache clearing, and startup.
## Performance results
Four populated AO files per partition/segment:
| Build / ORCA mode | -O0 warm | -O0 cold | -O3 warm | -O3 cold |
| --- | ---: | ---: | ---: | ---: |
| Cloudberry main, Parallel Append off | 774.271 | 938.675 | 302.049 | 411.082 |
| Cloudberry main, Parallel Append on | 584.411 | 717.117 | 215.208 | 297.893 |
| Apache REL_2_STABLE + backport, off | 1148.011 | 1257.391 | 351.606 | 459.310
|
| Apache REL_2_STABLE + backport, on | 873.218 | 917.522 | 254.689 | 331.698 |
| OpenGPDB, Dynamic Seq Scan | 992.596 | 1160.617 | 380.924 | 537.131 |
Effect of file layout, `-O3` with warm cache:
| Build / ORCA mode | 1 file | 2 files | 4 files |
| --- | ---: | ---: | ---: |
| Cloudberry main, Parallel Append off | 264.799 | 282.504 | 302.049 |
| Cloudberry main, Parallel Append on | 219.252 | 237.344 | 215.208 |
| Apache REL_2_STABLE + backport, off | 384.598 | 351.883 | 351.606 |
| Apache REL_2_STABLE + backport, on | 265.241 | 266.096 | 254.689 |
| OpenGPDB, Dynamic Seq Scan | 380.350 | 380.763 | 380.924 |
For four-file layouts, enabling Parallel Append reduced median execution time
by **23.6–28.8% on main** and **23.9–27.8% on Apache REL_2_STABLE**, depending
on optimization level and cache state. Changing the number of AO files alone
did not produce a consistent monotonic speedup. These results describe five
repetitions on this host; comparisons between builds also include differences
in PostgreSQL versions and AO implementations.
## REL_2_STABLE backports
The tested five-commit series in `open-gpdb/cloudberry REL_2_STABLE`, in
application order:
1. [Implement Parallel Append in
ORCA](https://github.com/open-gpdb/cloudberry/commit/636fce553ae4f92f88989ff32bc0e7bf2993c11e).
2. [Fix DXL translation for ParallelAppendTableScan under SET
queries](https://github.com/open-gpdb/cloudberry/commit/1ca7eac125f6d634eb2e682bd531a0ce95fdeda5).
3. [Fix
CPhysicalParallelAppendTableScan::FValidContext](https://github.com/open-gpdb/cloudberry/commit/d75f2d77ff1f4fa2ab5d35b2fc070183a9130b51).
4. [Fix parallel worker extraction for
ParallelAppend](https://github.com/open-gpdb/cloudberry/commit/27351867e5cee11eaf7cff5b39c68f8ac5897c38).
5. [Add the missing `<array>` include in
PAX](https://github.com/open-gpdb/cloudberry/commit/f495d6dbcbed42165c1018865f90bbec2f9dff6e).
GitHub link:
https://github.com/apache/cloudberry/discussions/2128#discussioncomment-19168490
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
