leborchuk commented on PR #741:
URL: https://github.com/apache/cloudberry/pull/741#issuecomment-2571579694
Sorry, I tried to understand whats could be wrong but failed. All works fine
in my dev environment.
The query
```
SELECT COUNT(c) = 4 AS tr, COUNT(DISTINCT gp_segment_id) = 1 AS tr
FROM complex_ttbl
WHERE re(c) = 0 AND im(c) = 0;
```
works great on current head, and execution plan is the same after I added
patch Support intermediate aggs in Orca plans (#13707):
```
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=0.00..431.00 rows=1 width=16)
Output: (count(c) = 4), (count(gp_segment_id) = 1)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1
width=12)
Output: gp_segment_id, (PARTIAL count(c))
-> Partial GroupAggregate (cost=0.00..431.00 rows=1 width=12)
Output: gp_segment_id, PARTIAL count(c)
Group Key: complex_ttbl.gp_segment_id
-> Sort (cost=0.00..431.00 rows=1 width=20)
Output: c, gp_segment_id
Sort Key: complex_ttbl.gp_segment_id
-> Seq Scan on public.complex_ttbl (cost=0.00..431.00
rows=1 width=20)
Output: c, gp_segment_id
Filter: ((re(complex_ttbl.c) = '0'::double
precision) AND (im(complex_ttbl.c) = '0'::double precision))
Optimizer: Pivotal Optimizer (GPORCA)
(14 rows)
```
The only difference between head and fix I cherry-picked I see in the test
query
```
CREATE TABLE onek (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
);
copy onek from '/home/xifos/git/cloudberry/src/test/regress/data/onek.data';
analyze onek;
SET optimizer_trace_fallback to on;
select ten, count(four), sum(DISTINCT four) from onek
group by ten order by ten;
```
The new explain plan is (gporca does not fallback to postgres optimizer
anymore)
```
postgres=# explain verbose select ten, count(four), sum(DISTINCT four) from
onek
group by ten order by ten;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.14 rows=10
width=20)
Output: ten, (count(four)), (sum(four))
Merge Key: ten
-> Sort (cost=0.00..431.14 rows=4 width=20)
Output: ten, (count(four)), (sum(four))
Sort Key: onek.ten
-> Finalize HashAggregate (cost=0.00..431.14 rows=4 width=20)
Output: ten, count(four), sum(four)
Group Key: onek.ten
-> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..431.14 rows=8 width=16)
Output: four, ten, (PARTIAL count(four))
Hash Key: ten
-> Partial HashAggregate (cost=0.00..431.14 rows=8
width=16)
Output: four, ten, PARTIAL count(four)
Group Key: onek.ten, onek.four
-> Redistribute Motion 3:3 (slice3; segments:
3) (cost=0.00..431.14 rows=8 width=16)
Output: four, ten, (PARTIAL count(four))
Hash Key: ten, four
-> Streaming Partial HashAggregate
(cost=0.00..431.14 rows=8 width=16)
Output: four, ten, PARTIAL count(four)
Group Key: onek.ten, onek.four
-> Seq Scan on public.onek
(cost=0.00..431.05 rows=334 width=8)
Output: four, ten
Optimizer: Pivotal Optimizer (GPORCA)
(24 rows)
```
--
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]
