Alena0704 opened a new pull request, #2027:
URL: https://github.com/apache/cloudberry/pull/2027
ORCA does not use two-stage aggregation on partitioned tables
ANALYZE adds up the per-segment ndistinct of all partitions to get the value
for the parent table (`STATISTIC_KIND_NDV_BY_SEGMENTS`). When the same values
are in every partition, they are counted once per partition, so the value is
too big.
ORCA uses this value to guess how many rows are left after the partial
aggregate. With a value that is too big, ORCA thinks the partial aggregate
does not help and sends all rows through the motion. The more partitions and
grouping columns, the bigger the error.
The fix caps the value: a segment cannot have more distinct values than the
whole table.
**Reproduction** (3 segments, ORCA):
```sql
CREATE TABLE ndvbs_bench (id bigint, pk int, a text, b text, c text, d text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (pk) (START (1) END (14) EVERY (1)); -- 13
partitions
-- a, b, c, d: 5 values each, repeated in every partition; 625 groups in
total
INSERT INTO ndvbs_bench
SELECT g, (g % 13) + 1, md5((g % 5)::text), md5(((g / 5) % 5)::text),
md5(((g / 25) % 5)::text), md5(((g / 125) % 5)::text)
FROM generate_series(1, 20000000) g;
ANALYZE ndvbs_bench;
SET optimizer = on;
EXPLAIN (ANALYZE) SELECT a, b, c, d, count(*) FROM ndvbs_bench GROUP BY a,
b, c, d;
```
**Before** (`REL_2_STABLE`): one-stage plan, all 7M rows go through the
motion.
```
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..9093.03 rows=47
width=140) (actual time=15231.369..15232.369 rows=625 loops=1)
-> HashAggregate (cost=0.00..9093.00 rows=16 width=140) (actual
time=15231.369..15231.369 rows=220 loops=1)
Group Key: a, b, c, d
-> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..5474.87 rows=6666667 width=132) (actual time=3.000..7932.192
rows=7040000 loops=1)
Hash Key: a, b, c, d
-> Dynamic Seq Scan on ndvbs_bench (cost=0.00..1083.67
rows=6666667 width=132) (actual time=0.000..3109.075 rows=6667848 loops=1)
Number of partitions to scan: 13 (out of 13)
Planning Time: 15.531 ms
Optimizer: GPORCA
Execution Time: 15233.284 ms
```
**After**: two-stage plan, only 660 rows go through the motion.
```
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..6340.17 rows=47
width=140) (actual time=5547.134..5548.134 rows=625 loops=1)
-> Finalize HashAggregate (cost=0.00..6340.14 rows=16 width=140)
(actual time=5526.134..5526.134 rows=220 loops=1)
Group Key: a, b, c, d
-> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..6339.45 rows=1268 width=140) (actual time=3703.090..5526.134
rows=660 loops=1)
Hash Key: a, b, c, d
-> Streaming Partial HashAggregate (cost=0.00..6338.90
rows=1268 width=140) (actual time=3703.090..3703.090 rows=625 loops=1)
Group Key: a, b, c, d
-> Dynamic Seq Scan on ndvbs_bench
(cost=0.00..1083.67 rows=6666667 width=132) (actual time=0.000..1347.033
rows=6667848 loops=1)
Number of partitions to scan: 13 (out of 13)
Planning Time: 33.956 ms
Optimizer: GPORCA
Execution Time: 5555.216 ms
```
### Type of Change
- [x] Bug fix (non-breaking change)
- [ ] New feature (non-breaking change)
- [ ] Breaking change (fix or feature with breaking changes)
- [ ] Documentation update
### Breaking Changes
<!-- Remove if not applicable. If yes, explain impact and migration path -->
### Test Plan
<!-- How did you test these changes? -->
- [ ] Unit tests added/updated
- [ ] Integration tests added/updated
- [ ] Passed `make installcheck`
- [ ] Passed `make -C src/test installcheck-cbdb-parallel`
### Impact
<!-- Remove sections that don't apply -->
**Performance:**
<!-- Any performance implications? -->
**User-facing changes:**
<!-- Any changes visible to users? -->
**Dependencies:**
<!-- New dependencies or version changes? -->
### Checklist
- [x] Followed [contribution
guide](https://cloudberry.apache.org/contribute/code)
- [ ] Added/updated documentation
- [ ] Reviewed code for security implications
- [ ] This PR contains AI-assisted code generation
- [x] Requested review from [cloudberry
committers](https://github.com/orgs/apache/teams/cloudberry-committers)
### Additional Context
<!-- Any other information that would help reviewers? Remove if none -->
### CI Skip Instructions
<!--
To skip CI builds, add the appropriate CI skip identifier to your PR title.
The identifier must:
- Be in square brackets []
- Include the word "ci" and either "skip" or "no"
- Only use for documentation-only changes or when absolutely necessary
-->
---
<!-- Join our community:
- Mailing list:
[[email protected]](https://lists.apache.org/[email protected])
(subscribe: [email protected])
- Discussions: https://github.com/apache/cloudberry/discussions -->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
