Re: Eager aggregation, take 3

On Thu, Oct 2, 2025 at 10:13 AM Richard Guo <guofenglinux@gmail.com> wrote:
> On Thu, Oct 2, 2025 at 8:55 AM Matheus Alcantara
> <matheusssilv97@gmail.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