Re: Eager aggregation, take 3

On Thu, Oct 2, 2025 at 10:13 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> On Thu, Oct 2, 2025 at 8:55 AM Matheus Alcantara
> <matheusssilv97(at)gmail(dot)com> wrote:
> > The query 31 seems bad, I don't know if I'm doing something completely
> > wrong but I've just setup a TPC-DS database and then executed the query
> > on master and with the v23 patch and I got these results:
> >
> > Master:
> > Planning Time: 3.191 ms
> > Execution Time: 16950.619 ms
> >
> > Patch:
> > Planning Time: 3.257 ms
> > Execution Time: 3848355.646 ms

> Thanks for reporting this. It does seem odd. I checked the TPC-DS
> benchmarking on v13 and found that the execution time for query 31,
> with and without eager aggregation, is as follows:
>
> EAGER-AGG-OFF EAGER-AGG-ON
> q31 10463.536 ms 10244.175 ms
>
> There appears to be a regression between v13 and v23. Looking into
> it...

I noticed something interesting while comparing the two EXPLAIN
(ANALYZE) outputs: the patched version uses parallel plans, whereas
the master does not. To rule that out as a factor, I ran "SET
max_parallel_workers_per_gather TO 0;" and re-ran query 31 on both
master and the patched version. This time, I got a positive result.

-- on master
Planning Time: 5.281 ms
Execution Time: 7222.665 ms

-- on patched
Planning Time: 4.855 ms
Execution Time: 5977.287 ms

It seems eager aggregation doesn't cope well with parallel plans for
this query. Looking into it.

- Richard