MIME-Version: 1.0
References: 
 <CAMbWs48jzLrPt1J_00ZcPZXWUQKawQOFE8ROc-ADiYqsqrpBNw@mail.gmail.com>
 <CAJgoLk+d_P5sKrx-SZt01Acm_j0QnWn6aKJzFJ=waRu_3C8AoQ@mail.gmail.com>
In-Reply-To: 
 <CAJgoLk+d_P5sKrx-SZt01Acm_j0QnWn6aKJzFJ=waRu_3C8AoQ@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Sun, 31 May 2026 19:28:37 +0800
Message-ID: 
 <CAHewXNkdhBrQR3mRmLw6FThgXd-tD2LmgfLreb+OeiyXmO2PPQ@mail.gmail.com>
Subject: Re: Eager aggregation, take 3
To: Radim Marek <radim@boringsql.com>
Cc: Richard Guo <guofenglinux@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXNkdhBrQR3mRmLw6FThgXd-tD2LmgfLreb%2BOeiyXmO2PPQ%40mail.gmail.com>
Precedence: bulk

Radim Marek <radim@boringsql.com> =E4=BA=8E2026=E5=B9=B45=E6=9C=8829=E6=97=
=A5=E5=91=A8=E4=BA=94 23:55=E5=86=99=E9=81=93=EF=BC=9A
>
> Hey Richard,
>
> I might be out of my depth here - but while testing RegreSQL as correctne=
ss/performance harness on PostgreSQL it picked up a problem with the wrong-=
results case during eager aggregation.
>
> It reproduces on current HEAD (commit 2670cc298f42cd7b1c426bf7ccfb0652d8e=
0b347 now) with enable_eager_aggregate enabled.
>
> My testing environment
>   - Linux aarch64, gcc 12 (Debian)
>   - macOS arm64, Apple clang 21
>     (PostgreSQL 19devel on aarch64-apple-darwin25.5.0)
>
> =3D=3D How to reproduce
>
>   CREATE TEMP TABLE c(id int, country text);
>   CREATE TEMP TABLE o(customer_id int);
>   INSERT INTO c VALUES (1,'US'),(2,'US'),(3,'DE'),(4,'DE'),(5,'DE');
>   INSERT INTO o VALUES (1),(3);   -- only customers 1 and 3 have a row in=
 o
>
>   SELECT c.country, count(*) AS n
>   FROM c
>   WHERE NOT EXISTS (SELECT 1 FROM o WHERE o.customer_id =3D c.id)
>   GROUP BY c.country
>   ORDER BY c.country;
>
> Expected results (everywhere except master)
>
>  country | n
> ---------+---
>  DE      | 2
>  US      | 1
> (2 rows)
>
> The actual result with enable_eager_aggregate =3D on (default)
>
>  country | n
> ---------+---
>  DE      | 0
>  US      | 0
> (2 rows)
>
> With SET enable_eager_aggregate =3D off, the result is correct (DE=3D2, U=
S=3D1), as it is on PG18.
>
> Query Plan
>
>                                                             QUERY PLAN
> -------------------------------------------------------------------------=
----------------------------------------------------------
>  Sort  (cost=3D108.19..108.69 rows=3D200 width=3D40) (actual time=3D0.195=
..0.197 rows=3D2.00 loops=3D1)
>    Sort Key: c.country
>    Sort Method: quicksort  Memory: 25kB
>    Buffers: local hit=3D2
>    ->  Finalize HashAggregate  (cost=3D98.55..100.55 rows=3D200 width=3D4=
0) (actual time=3D0.183..0.186 rows=3D2.00 loops=3D1)
>          Group Key: c.country
>          Batches: 1  Memory Usage: 32kB
>          Buffers: local hit=3D2
>          ->  Hash Anti Join  (cost=3D52.75..95.37 rows=3D635 width=3D40) =
(actual time=3D0.177..0.179 rows=3D3.00 loops=3D1)
>                Hash Cond: (c.id =3D o.customer_id)
>                Buffers: local hit=3D2
>                ->  Seq Scan on c  (cost=3D0.00..22.70 rows=3D1270 width=
=3D36) (actual time=3D0.024..0.025 rows=3D5.00 loops=3D1)
>                      Buffers: local hit=3D1
>                ->  Hash  (cost=3D50.25..50.25 rows=3D200 width=3D12) (act=
ual time=3D0.145..0.146 rows=3D2.00 loops=3D1)
>                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
>                      Buffers: local hit=3D1
>                      ->  Partial HashAggregate  (cost=3D48.25..50.25 rows=
=3D200 width=3D12) (actual time=3D0.122..0.123 rows=3D2.00 loops=3D1)
>                            Group Key: o.customer_id
>                            Batches: 1  Memory Usage: 32kB
>                            Buffers: local hit=3D1
>                            ->  Seq Scan on o  (cost=3D0.00..35.50 rows=3D=
2550 width=3D4) (actual time=3D0.002..0.003 rows=3D2.00 loops=3D1)
>                                  Buffers: local hit=3D1
>  Planning Time: 0.294 ms
>  Execution Time: 0.255 ms
> (24 rows)
>
> If this is already known or in progress, apologies for the noise.
Thanks for the report. This is a bug.
When we use eager_agg, it can reduce many tuples before doing a join
on the partial agg side.
After partial agg, when we are doing a join,  the matched rows will be
significantly reduced.
This is also the effect we want to achieve from eager_agg.

But we should be careful about anti-join. Because we will ignore the
matched row. The aggregate of unmatched rows seems wrong.
And I can get the wrong results from the semi-join, too.
For example:
postgres=3D# CREATE TEMP TABLE c(id int, country text);
CREATE TEMP TABLE o(customer_id int);
INSERT INTO c VALUES (1,'US'),(2,'US'),(3,'DE'),(4,'DE'),(5,'DE');
INSERT INTO o VALUES (1),(3);
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 2
postgres=3D# insert into o values (1);
INSERT 0 1
-- correct result
postgres=3D#  SELECT c.country, count(*) AS n
FROM c
WHERE EXISTS (SELECT 1 FROM o WHERE o.customer_id =3D c.id)
GROUP BY c.country
ORDER BY c.country;
 country | n
---------+---
 DE      | 1
 US      | 1
(2 rows)

I do some hacks that make the cost of the path created in
make_grouped_join_rel() very small.
So we can get a partial agg plan, as follow:

postgres=3D# explain SELECT c.country, count(*) AS n
FROM c
WHERE EXISTS (SELECT 1 FROM o WHERE o.customer_id =3D c.id)
GROUP BY c.country
ORDER BY c.country;
                                       QUERY PLAN
---------------------------------------------------------------------------=
--------------
 Finalize GroupAggregate  (cost=3D31.56..38.32 rows=3D200 width=3D40)
   Group Key: c.country
   ->  Sort  (cost=3D31.56..33.15 rows=3D635 width=3D40)
         Sort Key: c.country
         ->  Hash Semi Join  (cost=3D1.00..2.00 rows=3D635 width=3D40)
               Hash Cond: (c.id =3D o.customer_id)
               ->  Seq Scan on c  (cost=3D0.00..22.70 rows=3D1270 width=3D3=
6)
               ->  Hash  (cost=3D200.91..200.91 rows=3D200 width=3D12)
                     ->  Partial GroupAggregate  (cost=3D179.78..200.91
rows=3D200 width=3D12)
                           Group Key: o.customer_id
                           ->  Sort  (cost=3D179.78..186.16 rows=3D2550 wid=
th=3D4)
                                 Sort Key: o.customer_id
                                 ->  Seq Scan on o  (cost=3D0.00..35.50
rows=3D2550 width=3D4)
(13 rows)

postgres=3D#  SELECT c.country, count(*) AS n
FROM c
WHERE EXISTS (SELECT 1 FROM o WHERE o.customer_id =3D c.id)
GROUP BY c.country
ORDER BY c.country;
 country | n
---------+---
 DE      | 1
 US      | 2
(2 rows)

You can see that the count(us) has 2. Because partial agg
pre-aggregates the results for country =3D1.
However, for the semantics of semi-join, it returns once a match is found.

I haven't thought about it too deeply yet. Maybe we can do something
in the make_grouped_join_rel().
...
if (sjinfo->jointype =3D=3D JOIN_ANTI || sjinfo->jointype =3D=3D JOIN_SEMI)
    return;
...
The fixes above can temporarily resolve these issues. But it seems too stri=
ct.

--=20
Thanks,
Tender Wang