Alena0704 commented on code in PR #2027:
URL: https://github.com/apache/cloudberry/pull/2027#discussion_r4083610079
##########
src/backend/commands/analyze.c:
##########
@@ -4977,6 +4978,28 @@ merge_leaf_stats(VacAttrStatsP stats,
if (valid)
{
+ /*
+ * The leaves' values are summed, which is only right
when leaves hold
+ * disjoint values, e.g. for the partitioning key. A
value repeated in
+ * every partition is counted once per partition, so
the sum grows
+ * with the number of partitions and ORCA overestimates
the output of
+ * a local aggregate. A segment cannot have more
distinct values than
+ * the whole table, so clamp to the root's ndistinct
times the number
+ * of segments.
+ */
+ double root_ndistinct = stats->stadistinct < 0
?
+ -stats->stadistinct * totalTuples :
stats->stadistinct;
+
+ if (root_ndistinct > 0)
+ {
+ GpPolicy *policy =
GpPolicyFetch(stats->attr->attrelid);
Review Comment:
Added the `pfree(policy)`, thanks.
The cap is only an upper bound. When a value sits on a single segment it is
still too high by the number of segments, and ORCA multiplies the per-column
values, so with k grouping columns the error is numsegments^k and the
multi-stage plan is still lost. Instead of capping, the merged value is now the
root's ndistinct times the average number of segments a value sits on, taken
from the partitions themselves. The sum of the partitions is still kept as an
upper bound, and the average is at most the number of segments, so the value
never exceeds the root's ndistinct times the number of segments either.
**Reproduction** (3 segments, ORCA). `k` has 13 values and is independent of
the partitioning column, so every partition holds all of them, and `a` to `f`
are bijections of `k`, so each of their values sits on one segment:
```sql
CREATE TABLE ndvbs_dk (id bigint, pk int, k int,
a text, b text, c text, d text, e text, f text)
DISTRIBUTED BY (k)
PARTITION BY RANGE (pk) (START (1) END (14) EVERY (1));
INSERT INTO ndvbs_dk
SELECT g, (g % 13) + 1, (g / 13) % 13,
md5((((g / 13) % 13) )::text), md5((((g / 13) % 13) +
100)::text),
md5((((g / 13) % 13) + 200)::text), md5((((g / 13) % 13) +
300)::text),
md5((((g / 13) % 13) + 400)::text), md5((((g / 13) % 13) +
500)::text)
FROM generate_series(1, 6000000) g;
ANALYZE ndvbs_dk;
SET optimizer = on;
EXPLAIN (ANALYZE) SELECT a, b, c, d, e, f, count(*)
FROM ndvbs_dk GROUP BY a, b, c, d, e, f;
```
Before: a partition has 13, the root gets 39, and all 2.3M rows per segment
go through the motion to produce 13 groups.
```
Gather Motion 3:1 (cost=0.00..4334.14 rows=15307 width=200) (actual
time=11650.023..11650.023 rows=13 loops=1)
-> HashAggregate (cost=0.00..4322.73 rows=5103 width=200) (actual
time=11650.023..11650.023 rows=5 loops=1)
-> Redistribute Motion 3:3 (cost=0.00..2613.36 rows=2000000
width=192) (actual time=2.000..8058.016 rows=2307694 loops=1)
-> Dynamic Seq Scan on ndvbs_dk (cost=0.00..697.20
rows=2000000 width=192) (actual rows=2307695 loops=1)
Execution Time: 11651.606 ms
```
After: the root gets 13, and 5 rows go through the motion.
```
Gather Motion 3:1 (cost=0.00..3141.49 rows=15307 width=200) (actual
time=3744.007..3744.007 rows=13 loops=1)
-> Finalize HashAggregate (cost=0.00..3130.08 rows=5103 width=200)
(actual time=3743.007..3743.007 rows=5 loops=1)
-> Redistribute Motion 3:3 (cost=0.00..3125.15 rows=5103
width=200) (actual time=2461.005..3743.007 rows=5 loops=1)
-> Streaming Partial HashAggregate (cost=0.00..3121.96
rows=5103 width=200) (actual time=3742.007..3742.007 rows=5 loops=1)
-> Dynamic Seq Scan on ndvbs_dk (cost=0.00..697.20
rows=2000000 width=192) (actual rows=2307695 loops=1)
Execution Time: 3744.836 ms
```
--
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]
