Skip to content

ORCA chooses a slower coordinator plan for a correlated window subquery #2335

Open

Description

@Alena0704

Apache Cloudberry version

Apache Cloudberry main, REL_2_STABLE.

Local cluster with one coordinator and three primary segments on the same machine. Assertions enabled; C/C++ compilation flags include -O0 -g.

What happened

For a correlated IN subquery containing count(*) OVER (), default ORCA gathers the outer and inner inputs to the coordinator and evaluates the subplan there for all 64 outer rows. The inner relation contains one million rows; the materialized inner input is scanned 64 times on the coordinator.

The PostgreSQL planner broadcasts the inner input and evaluates the correlated subplan on the three segments. The same PR binary can also produce a correct distributed ORCA plan when optimizer_force_multistage_agg=on.

Measured EXPLAIN ANALYZE execution times:

Configuration Correlated subplan execution Execution time Result
PostgreSQL planner (optimizer=off) Segments 2167.517 ms 48
Default ORCA (optimizer=on) Coordinator 5116.686 ms 48
ORCA with optimizer_force_multistage_agg=on Segments 2161.909 ms 48

Default ORCA is approximately 2.4 times slower than its distributed alternative in this reproduction.

What you think should happen instead

ORCA should account for the cost of repeatedly scanning the materialized inner relation and the benefit of evaluating different outer rows concurrently on the segments. For this reproduction, it should prefer the faster distributed alternative without requiring optimizer_force_multistage_agg=on.

The observed problem is plan selection. The exact cause in costing, estimation, or search has not yet been established. The forced setting is used here to demonstrate an available alternative, rather than as a proposed general fix.

How to reproduce

Run the following in psql on a multi-segment cluster using the tested commit. Every SELECT count(*) must return 48. The script creates temporary tables and rolls back its changes.

-- Reproducer for PR #2274 commit 7b6a212b249b.
-- Run: psql -X -f cloudberry-orca-correlated-window-repro.sql
-- Requires a multi-segment Cloudberry cluster. Tested with three segments.
-- Every SELECT count(*) below must return 48.
-- All tables/settings are temporary or transaction-local.
\set ON_ERROR_STOP on
\pset pager off
BEGIN;
SET LOCAL statement_timeout = '60s';
SET LOCAL optimizer_trace_fallback = on;

CREATE TEMP TABLE pr2274_inner(k int, v int) DISTRIBUTED BY(k);
INSERT INTO pr2274_inner
SELECT g % 1000, g FROM generate_series(1,1000000) g;

CREATE TEMP TABLE pr2274_outer(id int, k int, n bigint) DISTRIBUTED BY(id);
INSERT INTO pr2274_outer
SELECT g, g % 1000, CASE WHEN g % 4 = 0 THEN 1001 ELSE 1000 END
FROM generate_series(1,64) g;
ANALYZE pr2274_inner, pr2274_outer;


-- PostgreSQL planner
SET LOCAL optimizer = off;
SELECT count(*)
FROM pr2274_outer o
WHERE o.n IN (
    SELECT count(*) OVER ()
    FROM pr2274_inner i
    WHERE i.k = o.k
);

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT count(*)
FROM pr2274_outer o
WHERE o.n IN (
    SELECT count(*) OVER ()
    FROM pr2274_inner i
    WHERE i.k = o.k
);

-- ORCA defaults
SET LOCAL optimizer = on;
SET LOCAL optimizer_force_multistage_agg = off;
SET LOCAL optimizer_penalize_broadcast_threshold = 100000;
SELECT count(*)
FROM pr2274_outer o
WHERE o.n IN (
    SELECT count(*) OVER ()
    FROM pr2274_inner i
    WHERE i.k = o.k
);

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT count(*)
FROM pr2274_outer o
WHERE o.n IN (
    SELECT count(*) OVER ()
    FROM pr2274_inner i
    WHERE i.k = o.k
);

-- ORCA with forced multistage aggregation
SET LOCAL optimizer_force_multistage_agg = on;
SELECT count(*)
FROM pr2274_outer o
WHERE o.n IN (
    SELECT count(*) OVER ()
    FROM pr2274_inner i
    WHERE i.k = o.k
);

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY ON)
SELECT count(*)
FROM pr2274_outer o
WHERE o.n IN (
    SELECT count(*) OVER ()
    FROM pr2274_inner i
    WHERE i.k = o.k
);

ROLLBACK;

Operating System

any

Anything else

This was observed while reviewing #2274. I suspect the plan-selection issue already existed but was masked by the incorrect pull-up. By blocking that unsafe transformation, the fix appears to expose the slower correlated plan.

Additional diagnostic observations:

  • Setting only optimizer_penalize_broadcast_threshold=0 retains the coordinator plan: 5117.476 ms, result 48.
  • Combining it with optimizer_force_multistage_agg=on produces a distributed plan: 2148.029 ms, result 48.
  • Thus, removing the broadcast penalty alone is insufficient to obtain the faster plan in this case.

Full measured plans follow. Estimated costs are included; cost values from the PostgreSQL planner and ORCA are not directly comparable.

PostgreSQL planner
 Finalize Aggregate  (cost=1.30..1.31 rows=1 width=8) (actual rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=1.24..1.29 rows=3 width=8) (actual rows=3 loops=1)
         ->  Partial Aggregate  (cost=1.24..1.25 rows=1 width=8) (actual rows=1 loops=1)
               ->  Seq Scan on pr2274_outer o  (cost=0.00..1.21 rows=11 width=0) (actual rows=19 loops=1)
                     Filter: (SubPlan 1)
                     Rows Removed by Filter: 5
                     SubPlan 1
                       ->  WindowAgg  (cost=0.00..44534.67 rows=1000000 width=8) (actual rows=334 loops=21)
                             ->  Result  (cost=0.00..32034.67 rows=1000000 width=0) (actual rows=1000 loops=24)
                                   Filter: (i.k = o.k)
                                   ->  Materialize  (cost=0.00..22034.67 rows=1000000 width=4) (actual rows=1000000 loops=24)
                                         ->  Broadcast Motion 3:3  (slice2; segments: 3)  (cost=0.00..17034.67 rows=1000000 width=4) (actual rows=1000000 loops=1)
                                               ->  Seq Scan on pr2274_inner i  (cost=0.00..3701.33 rows=333333 width=4) (actual rows=340000 loops=1)
 Planning Time: 0.137 ms
   (slice0)    Executor memory: 55K bytes.
   (slice1)    Executor memory: 39516K bytes avg x 3x(0) workers, 39519K bytes max (seg2).  Work_mem: 55068K bytes max.
   (slice2)    Executor memory: 111K bytes avg x 3x(0) workers, 111K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 2167.517 ms
Default ORCA
 Aggregate  (cost=0.00..5670705.01 rows=1 width=8) (actual rows=1 loops=1)
   ->  Result  (cost=0.00..5670705.01 rows=64 width=1) (actual rows=48 loops=1)
         Filter: (SubPlan 1)
         ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=64 width=12) (actual rows=64 loops=1)
               ->  Seq Scan on pr2274_outer o  (cost=0.00..431.00 rows=22 width=12) (actual rows=24 loops=1)
                     Filter: (NOT (k IS NULL))
         SubPlan 1
           ->  WindowAgg  (cost=0.00..4671.29 rows=1000 width=8) (actual rows=251 loops=64)
                 ->  Result  (cost=0.00..4671.29 rows=1000 width=1) (actual rows=1000 loops=64)
                       Filter: (i.k = o.k)
                       ->  Materialize  (cost=0.00..460.09 rows=1000000 width=4) (actual rows=1000000 loops=64)
                             ->  Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..456.09 rows=1000000 width=4) (actual rows=1000000 loops=1)
                                   ->  Seq Scan on pr2274_inner i  (cost=0.00..438.70 rows=333334 width=4) (actual rows=340000 loops=1)
 Planning Time: 12.364 ms
   (slice0)    Executor memory: 39532K bytes.  Work_mem: 55068K bytes max.
   (slice1)    Executor memory: 14K bytes avg x 3x(0) workers, 14K bytes max (seg0).
   (slice2)    Executor memory: 111K bytes avg x 3x(0) workers, 111K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: GPORCA
 Execution Time: 5116.686 ms
ORCA with forced multistage aggregation
 Finalize Aggregate  (cost=0.00..102400000005652368.00 rows=1 width=8) (actual rows=1 loops=1)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..102400000005652368.00 rows=1 width=8) (actual rows=3 loops=1)
         ->  Partial Aggregate  (cost=0.00..102400000005652368.00 rows=1 width=8) (actual rows=1 loops=1)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..102400000005652368.00 rows=22 width=1) (actual rows=18 loops=1)
                     ->  Seq Scan on pr2274_outer o  (cost=0.00..102400000005652368.00 rows=22 width=1) (actual rows=19 loops=1)
                           Filter: ((NOT (k IS NULL)) AND (SubPlan 1))
                           Rows Removed by Filter: 5
                           SubPlan 1
                             ->  WindowAgg  (cost=0.00..100000000004656.39 rows=1000 width=8) (actual rows=334 loops=21)
                                   ->  Result  (cost=0.00..100000000004656.39 rows=1000 width=1) (actual rows=1000 loops=24)
                                         Filter: (i.k = o.k)
                                         ->  Materialize  (cost=0.00..100000000000445.19 rows=1000000 width=4) (actual rows=1000000 loops=24)
                                               ->  Broadcast Motion 3:3  (slice3; segments: 3)  (cost=0.00..100000000000441.19 rows=1000000 width=4) (actual rows=1000000 loops=1)
                                                     ->  Seq Scan on pr2274_inner i  (cost=0.00..438.70 rows=333334 width=4) (actual rows=340000 loops=1)
 Planning Time: 12.843 ms
   (slice0)    Executor memory: 58K bytes.
   (slice1)    Executor memory: 17K bytes avg x 3x(0) workers, 17K bytes max (seg1).
   (slice2)    Executor memory: 39510K bytes avg x 3x(0) workers, 39513K bytes max (seg2).  Work_mem: 55068K bytes max.
   (slice3)    Executor memory: 111K bytes avg x 3x(0) workers, 111K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: GPORCA
 Execution Time: 2161.909 ms

Are you willing to submit PR?

  • Yes, I am willing to submit a PR!

Code of Conduct

Activity

  1. self-assigned this
    on Nov 16, 2026
  2. changed the title [-][Bug][/-] [+]ORCA chooses a slower coordinator plan for a correlated window subquery[/+] on Nov 16, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

type: Performancecloudberry runs slow on some particular query

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions