#2128 [Ideas] Add ORCA intra-segment parallel scan support for partitioned AO tables New discussion created by Alena0704 Description

Preliminary measurements: These are noisy, uncontrolled measurements collected on my laptop with background applications running, not a clean benchmark. I plan to repeat the measurements on a virtual machine with a cold cache. Treat the timings and speedup ratios below as provisional.

For

SELECT count(*) FROM ao13

over a row-oriented append-optimized table with 13 daily partitions and 6 million rows, ORCA uses a serial

Dynamic Seq Scan

within each segment even when

enable_parallel=on

and

max_parallel_workers_per_gather=4

. The PostgreSQL planner produces

Parallel Append

over

Parallel Seq Scan

. Disabling

optimizer_enable_dynamictablescan

causes PostgreSQL fallback for this query. The parallel PostgreSQL plan reduces median execution time from 751.380 to 659.712 ms on unmodified upstream main (12.2% less time; 1.14× speedup) and from 713.039 to 649.303 ms on unmodified upstream REL_2_STABLE (8.9% less time; 1.10× speedup), with

gp_resqueue_priority=off

. Parallel Append lets multiple execution participants within each segment share the partition-scan work, rather than having one participant scan all partitions sequentially. The measured parallel plans have 12 sending participants across three physical segments, versus three for ORCA. This distributes scan and partial-aggregation work across more CPU cores; scheduling overhead, work granularity and host load limit the resulting speedup. The fallback uses the same PostgreSQL parallel execution mechanism. The limitation reproduces on both unmodified upstream branches. This appears to be a missing capability in ORCA's intra-segment parallel scan support, rather than a cost-model choice between serial and parallel dynamic scan alternatives. Parallel execution improves this query's elapsed time in these measurements, but does not close the performance gap to GP6 on the unmodified builds; the cross-system comparison is included below. Every timing and plan in this report was collected with

gp_resqueue_priority=off

on the coordinator and all three primary segments. Each mode has one warm-up followed by five measured runs. Tables report execution times in milliseconds; full plans are from measured round 5. Environment and procedure • Test date: October 14, 2026; macOS arm64 host, 14 logical CPUs, 24 GiB RAM. • Each cluster has three primary segments on the same host and database

codex_ao13_verify_20261014

. • Same table definition, data generation, distribution on

id

, 13 daily partitions and 6 million rows across builds. •

gp_resource_manager=queue

,

gp_resqueue_priority=off

,

statement_mem=128000

kB and 16 MiB shared buffers. • Cloudberry:

max_parallel_workers_per_gather=4

,

optimizer_disable_dynamic_table_scan=off

in every mode. • One persistent psql session per build; one warm-up EXPLAIN per mode, then five measured rounds, with mode order shuffled using seed 1306. Round 0 is excluded from statistics. • The build benchmarks ran sequentially. No other benchmarks or builds were launched by this investigation during measurement. Other host applications remained running; process-load samples were recorded every two seconds. This is not an isolated-host benchmark. • No OS cache flushing was performed. An explicit count was checked once per mode during warm-up: all benchmark count checks returned 6000000.

gp_resqueue_priority

is a startup setting. It was disabled through postgres startup options on every node, with cluster restart where necessary; it was not changed using a session SET. The setting was verified on every node (direct utility connections for the clean Cloudberry clusters; coordinator SHOW plus distributed pg_settings queries for GP6 and the PR build), and through SHOW in each benchmark session. The test clusters remain running with this startup override. Cluster startup option and setting verification

# Add this option to the existing postgres startup arguments on every node:
-c gp_resqueue_priority=off

SHOW gp_resqueue_priority; -- must report off The exact restart and sequential measurement procedure is in <parallel-partition-verification/analysis/run_priority_off_comparison.py|run_priority_off_comparison.py>. Preserve the appropriate dispatch/execute role arguments when restarting nodes. Reproduction SQL Create and populate this table in each test database where

ao13

does not already exist: Create, populate and analyze ao13 CREATE TABLE 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 ao13 SELECT g, '2020-01-01'::date + (g % 13), g % 97, g % 89, repeat('x', 50) FROM generate_series(1, 6000000) g; ANALYZE ao13; The SQL for each optimizer mode accompanies its plan below. In Cloudberry, disabling

optimizer_enable_dynamictablescan

prevents a specific ORCA transformation; fallback is verified for this query, not guaranteed for arbitrary queries. The distinct

optimizer_disable_dynamic_table_scan

remains

off

. Setting it to

on

enables ORCA's existing dynamic-scan-to-Append translation, which does not itself provide the parallel alternative described here. Transaction-scoped fallback reproduction BEGIN; SET LOCAL optimizer = on; SET LOCAL optimizer_enable_dynamictablescan = off; SET LOCAL optimizer_disable_dynamic_table_scan = off; SET LOCAL enable_parallel = on; SET LOCAL max_parallel_workers_per_gather = 4; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT count(*) FROM ao13; COMMIT; Cloudberry unmodified upstream main Source commit

3a586f12c2c2011989734be383ddea69aac24abb

, verified against upstream main on October 14, 2026. All 19,632 tracked files and symbolic links matched the commit; no local feature patches or PR #2094 code were applied. Cloudberry 3.0.0-devel / PostgreSQL 16.9, Apple Clang 21,

--enable-debug --enable-cassert --enable-orca

,

CFLAGS='-O0 -g'

,

CXXFLAGS='-O0 -g'

. ORCA, parallel on Reproduction SQL SET optimizer = on; SET optimizer_enable_dynamictablescan = on; SET optimizer_disable_dynamic_table_scan = off; SET enable_parallel = on; SET max_parallel_workers_per_gather = 4; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT count(*) FROM ao13; Query execution plan — measured round 5

Finalize Aggregate (actual rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3) (actual rows=3 loops=1)
         ->  Partial Aggregate (actual rows=1 loops=1)
               ->  Dynamic Seq Scan on ao13 (actual rows=2001127 loops=1)
                     Number of partitions to scan: 13 (out of 13)
                     Partitions scanned:  Avg 13.0 x 3 workers.  Max 13 parts (seg0).
 Planning Time: 10.136 ms
   (slice0)    Executor memory: 114K bytes.
   (slice1)    Executor memory: 293K bytes avg x 3x(0) workers, 293K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: GPORCA
 Execution Time: 751.380 ms

PostgreSQL, parallel off Reproduction SQL SET optimizer = off; SET optimizer_enable_dynamictablescan = on; SET optimizer_disable_dynamic_table_scan = off; SET enable_parallel = off; SET max_parallel_workers_per_gather = 4; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT count(*) FROM ao13; Query execution plan — measured round 5 ``` Finalize Aggregate (actual rows=1 loops=1) -> Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1) -> Partial Aggregate (actual rows=1 loops=1) -> Append (actual rows=2001127 loops=1) -> Seq Scan on ao13_1_prt_1 ao13_1 (actual rows=154105 loops=1) -> Seq Scan on ao13_1_prt_2 ao13_2 (actual rows=154176 loops=1) -> Seq Scan on ao13_1_prt_3 ao13_3 (actual rows=154082 loops=1) -> Seq Scan o… apache/cloudberry