[Proposal] Enhanced ORCA Parallel Planning to Align with PostgreSQL Planner

[yjhjstz]

Proposers

@yjhjstz

Proposal Status

Under Discussion

Abstract

ORCA (Pivotal Query Optimizer) currently has limited parallel planning capabilities, This creates an inconsistency where:

• PostgreSQL planner can generate comprehensive parallel plans
• ORCA lacks equivalent parallel planning sophistication
• Users must disable ORCA (set optimizer=off) to fully utilize parallel features

Motivation

Extend ORCA to generate parallel execution plans that align with PostgreSQL's parallel planning approach, while maintaining
compatibility with Cloudberry's MPP architecture.

Implementation

Extend ORCA's path generation to create parallel-aware operators:

• CPhysicalParallelSeqScan - Parallel sequential scans
• CPhysicalParallelIndexScan - Parallel index scans
• CPhysicalParallelBitmapHeapScan - Parallel bitmap heap scans
• CPhysicalParallelHash - Parallel hash operations
• CPhysicalParallelHashJoin - Parallel hash joins
• CPhysicalParallelAgg - Parallel aggregation
• CPhysicalParallelSort - Parallel sorting

2. Parallel-Aware Join Planning

Implement parallel join strategies similar to PostgreSQL:
// Parallel hash join with shared hash table
class CPhysicalParallelHashJoin : public CPhysicalHashJoin {
// Enable parallel-aware hash table sharing
// Handle worker coordination for hash table building
// Manage locus for HashedWorkers distribution
};

3. Parallel Cost Model Integration

Enhance ORCA's cost model to account for parallel execution:

• CPU cost reduction based on parallel_workers
• Memory cost adjustments for shared resources
• I/O cost distribution across workers
• Startup cost penalties for worker coordination

4. Parallel Motion Nodes

Rollout/Adoption Plan

Benefits

1. Performance Consistency - Users get parallel execution regardless of optimizer choice
2. Feature Parity - ORCA matches PostgreSQL planner capabilities
3. Enhanced Scalability - Better utilization of multi-core systems
4. Simplified Configuration - No need to disable ORCA for parallel workloads
5. Boost tpcds, tpch

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

[my-ship-it]

Thanks for the proposal. I think it is doable for ORCA, and would bring noticeable performance improvement for ORCA plan.

[my-ship-it]

In fact, we would need to add severals transforms and a new CDistributionSpec.

[yjhjstz]

class CDistributionSpecRandom : public CDistributionSpec
{
public:
    enum EDistributionLevel
    {
        EdlSegment,     // Cross-segment distribution
        EdlWorker       // Intra-segment distribution to worker threads
    };
    // New members
    EDistributionLevel m_edl;                 // Distribution level
    ULONG m_ulWorkers;                        // Number of workers
    CDistributionSpec *m_pdsSegmentBase;      // Base segment distribution
};

expand CDistributionSpecRandom.

[my-ship-it]

class CDistributionSpecRandom : public CDistributionSpec
{
public:
    enum EDistributionLevel
    {
        EdlSegment,     // Cross-segment distribution
        EdlWorker       // Intra-segment distribution to worker threads
    };
    // New members
    EDistributionLevel m_edl;                 // Distribution level
    ULONG m_ulWorkers;                        // Number of workers
    CDistributionSpec *m_pdsSegmentBase;      // Base segment distribution
};

expand CDistributionSpecRandom.

After going through following inherited classes from CDistributionSpec:
Inherited directly:

• CDistributionSpecAny
• CDistributionSpecRandom
• CDistributionSpecUniversal
• CDistributionSpecNonSingleton
• CDistributionSpecRouted
• CDistributionSpecReplicated
• CDistributionSpecSingleton

And inherited indirectly:

• CDistributionSpecHashed（from CDistributionSpecRandom）
• CDistributionSpecStrictRandom（from CDistributionSpecRandom）
• CDistributionSpecStrictHashed（from CDistributionSpecHashed）
• CDistributionSpecHashedNoOp（from CDistributionSpecHashed）
• CDistributionSpecStrictSingleton（from CDistributionSpecSingleton）
• CDistributionSpecNonReplicated（from CDistributionSpecSingleton）

We also need to consider CDistributionSpecReplicated for replicated table at least.
For example,

create table rt1(a int, b int) with(parallel_workers=2) distributed replicated;
create table rt2(a int, b int) distributed replicated;

explain(locus, costs off) select * from rt1 join rt2 on rt2.b = rt1.a;
                    QUERY PLAN
--------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)
   Locus: Entry
   ->  Parallel Hash Join
         Locus: SegmentGeneralWorkers
         Parallel Workers: 2
         Hash Cond: (rt2.b = rt1.a)
         ->  Parallel Seq Scan on rt2
               Locus: SegmentGeneralWorkers
               Parallel Workers: 2
         ->  Parallel Hash
               Locus: SegmentGeneral
               ->  Parallel Seq Scan on rt1
                     Locus: SegmentGeneralWorkers
                     Parallel Workers: 2

[yjhjstz]

Based on ORCA's design patterns, the class hierarchy uses inheritance rather than composition for extending distribution specifications.

so use class CDistributionSpecWorkerRandom : public CDistributionSpecRandom and

class CDistributionSpecWorkerReplicated: public CDistributionSpecReplicated

[my-ship-it]

It does make sense. So we need to adapt logic of FCompatible, and take care of SetDistrRequests in Physical Ops.

[yjhjstz]

milestone 1: #1398