apache / apache/cloudberry

[Bug] ORCA misses direct dispatch for multi-value predicates (IN / OR of equalities) on the distribution key

Open
#1,839 1 comment 0 reactions 0 assignees View on GitHub
type: Bug
Dominant language
C
Stars
1.4k
Forks
247
Avg merge
4d 3h
Merged PRs (30d)
39

Description

### Apache Cloudberry version

All versions

### What happened

For a predicate that restricts the distribution key to a small set of
constants, e.g. `WHERE dist_key IN (c1, c2)`, GPORCA only performs direct
dispatch when ALL values happen to hash to the SAME segment. If the values
hash to different segments, ORCA gives up entirely and dispatches the slice
to every segment, while the Postgres planner correctly direct-dispatches to
the union of the target segments.

Results are correct — this is a performance issue (missed direct dispatch),
not a wrong-results bug. I'm filing it as a bug rather than a feature request
because the optimizer side already emits the complete multi-value dispatch
info into the DXL plan, and the DXL-to-PlannedStmt translator silently drops
it (details in "Anything else").

### What you think should happen instead

ORCA should dispatch to the union of the segments the constants hash to,
like the Postgres planner does (see `MergeDirectDispatchCalculationInfo` in
`src/backend/cdb/cdbmutate.c` / `directdispatch.c` lineage). For the
reproduction below, the planner produces:

Gather Motion 2:1 (slice1; segments: 2)
INFO: (slice 1) Dispatch command to PARTIAL contents: 1 2

while ORCA produces:

Gather Motion 3:1 (slice1; segments: 3)
INFO: (slice 1) Dispatch command to ALL contents: 0 1 2

The infrastructure already supports this: `PlanSlice->directDispatch.contentIds`
is a List, and the dispatcher handles multiple content ids today (the planner
populates it with more than one; so does ORCA's own raw-values path for
`gp_segment_id IN (...)` predicates on randomly distributed tables).

### How to reproduce

On a 3-segment demo cluster:

```sql
create table t_dd (a int, b text) distributed by (a);
insert into t_dd select i, 'x' from generate_series(1, 10) i;
-- on my cluster: a=1 -> seg1, a=2,3 -> seg0, a=5 -> seg2
-- (cdbhash of int is deterministic, so a 3-segment cluster gets the same mapping)

set gp_test_print_direct_dispatch_info = on;

set optimizer = on;
explain (costs off) select * from t_dd where a in (1, 5);
-- Gather Motion 3:1 (slice1; segments: 3) <-- all segments
select * from t_dd where a in (1, 5);
-- INFO: (slice 1) Dispatch command to ALL contents: 0 1 2

explain (costs off) select * from t_dd where a in (2, 3);
-- Gather Motion 1:1 (slice1; segments: 1) <-- works, but only because
-- both values hash to seg0
set optimizer = off;
explain (costs off) select * from t_dd where a in (1, 5);
-- Gather Motion 2:1 (slice1; segments: 2) <-- planner dispatches to
select * from t_dd where a in (1, 5); -- the union {seg1, seg2}
-- INFO: (slice 1) Dispatch command to PARTIAL contents: 1 2
```
The explicit OR form (`a = 1 or a = 5`) behaves the same way.

### Operating System

Rocky Linux 9.5

### Anything else

_No response_

### Are you willing to submit PR?

- [x] Yes, I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).

Contributor guide

Open the contributing guide

Research direction

Start with the DXL-to-PlannedStmt translation path and inspect how PlanSlice->directDispatch.contentIds is populated, comparing it with MergeDirectDispatchCalculationInfo in src/backend/cdb/cdbmutate.c and the directdispatch.c lineage. Reproduce the issue using the three-segment SQL examples with optimizer on and off, including both IN and explicit OR predicates. Done means ORCA reports and dispatches only the union of target segments for multi-value distribution-key predicates.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, postgresql, sql
Domain
backend, databases, distributed-systems
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.