MIME-Version: 1.0
References: <CAMbWs48jzLrPt1J_00ZcPZXWUQKawQOFE8ROc-ADiYqsqrpBNw@mail.gmail.com>
 <87il22cj51.fsf@163.com> <CAMbWs49=eAd2W9jCtGhaZPPp+SOC_2rg16RTG74xAht=hkr5JQ@mail.gmail.com>
 <CAMbWs49Nc4M3H+eCf1+8w8piDyEECjRb-gK_JMF4VvcyWwGEVQ@mail.gmail.com>
 <CAMbWs49E_dR0nobsExsyetpnBpHObLTsQLsEbWKQLkh0omPxNg@mail.gmail.com>
 <CAMbWs49B_qUiHvu2EqLHZRpLr3p_+QPBs50n2=L5ibYzniwTzA@mail.gmail.com>
 <CAMbWs48KCQtDymnYi4M=Vz+WMzo3fkBxffJsyk6VX6hOXXv+VA@mail.gmail.com>
 <CAMbWs49sv_MuOYqqrtmBN_oYf8VSQ2BXDwXaTpJTn_YfwyYdWQ@mail.gmail.com>
 <CAMbWs49U8Sddx_fGszPdvA3jp_nheynxaqm5Y4NqMV21VBYAuQ@mail.gmail.com>
 <CAMbWs4-LwyOg9ga+NVF7yQbMi0ZsZdN1G_sO2v=YJHV18=19+A@mail.gmail.com>
 <CALA8mJquG_zCJXfVwash5LKqHGtZXQmq7RfTSaRDUzGYeW=7Rw@mail.gmail.com>
 <CAMbWs4_EjgcBib5+y1LYcGB3EK3Y6R+OOxGKfJo42fDovadk1g@mail.gmail.com>
 <CALA8mJqe0anNM8_V6cOeOQnCHUTQggn7iOQNyQr1VaN_xMjz+w@mail.gmail.com>
 <CAMbWs48eE-s-jCicC8pSVfXk8Ws-ZvUKnsw8qH-DkVBdYv0eJQ@mail.gmail.com>
 <CAMbWs483a7-8M0pDttG44r-+8Gevn9VG0xNceE3WpkEQxJXPZw@mail.gmail.com>
 <CAHewXNmYM6DvR_kaxDL0w0fz9BwKbac+TSU3QS10aA3cXHyMmA@mail.gmail.com>
 <CA+TgmoaxH=P63hLYgyJJcEbMRnw3xi16d=HxFi1j-m7MhH6W_w@mail.gmail.com>
 <CAMbWs4-Q-CHadH1ub_oAv3sJ_NHt0GUO4nce3LBhpPmp1v6Z_Q@mail.gmail.com> <CAHewXNk7VWJCvn817tHR0DjyX36+UgdtMdYrC7R7onvqRoD_GA@mail.gmail.com>
In-Reply-To: <CAHewXNk7VWJCvn817tHR0DjyX36+UgdtMdYrC7R7onvqRoD_GA@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 29 Aug 2024 09:02:10 -0400
Message-ID: <CA+TgmoZoJSg1gdL-c9CEVdvA0s60_krpSwKsdY2j_L1MO0hH-Q@mail.gmail.com>
Subject: Re: Eager aggregation, take 3
To: Tender Wang <tndrwang@gmail.com>
Cc: Richard Guo <guofenglinux@gmail.com>, Paul George <p.a.george19@gmail.com>, 
	Andy Fan <zhihuifan1213@163.com>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>, pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BTgmoZoJSg1gdL-c9CEVdvA0s60_krpSwKsdY2j_L1MO0hH-Q%40mail.gmail.com>
Precedence: bulk

On Wed, Aug 28, 2024 at 11:38=E2=80=AFPM Tender Wang <tndrwang@gmail.com> w=
rote:
> I upload EXPLAIN(COSTS ON, ANALYZE) test to [1].
> I ran the same query three times, and I chose the third time result.
> You can check 19_off_explain.out and 19_on_explain.out.

So, in 19_off_explain.out, we got this:

         ->  Finalize GroupAggregate  (cost=3D666986.48..667015.35
rows=3D187 width=3D142) (actual time=3D272.649..334.318 rows=3D900 loops=3D=
1)
               ->  Gather Merge  (cost=3D666986.48..667010.21 rows=3D187
width=3D142) (actual time=3D272.644..333.847 rows=3D901 loops=3D1)
                     ->  Partial GroupAggregate
(cost=3D665986.46..665988.60 rows=3D78 width=3D142) (actual
time=3D266.379..267.476 rows=3D300 loops=3D3)
                           ->  Sort  (cost=3D665986.46..665986.65
rows=3D78 width=3D116) (actual time=3D266.367..266.583 rows=3D5081 loops=3D=
3)

And in 19_on_explan.out, we got this:

         ->  Finalize GroupAggregate  (cost=3D666987.03..666989.77
rows=3D19 width=3D142) (actual time=3D285.018..357.374 rows=3D900 loops=3D1=
)
               ->  Gather Merge  (cost=3D666987.03..666989.25 rows=3D19
width=3D142) (actual time=3D285.000..352.793 rows=3D15242 loops=3D1)
                     ->  Sort  (cost=3D665987.01..665987.03 rows=3D8
width=3D142) (actual time=3D273.391..273.580 rows=3D5081 loops=3D3)
                           ->  Nested Loop  (cost=3D665918.00..665986.89
rows=3D8 width=3D142) (actual time=3D252.667..269.719 rows=3D5081 loops=3D3=
)
                                 ->  Nested Loop
(cost=3D665917.85..665985.43 rows=3D8 width=3D157) (actual
time=3D252.656..264.755 rows=3D5413 loops=3D3)
                                       ->  Partial GroupAggregate
(cost=3D665917.43..665920.10 rows=3D82 width=3D150) (actual
time=3D252.643..255.627 rows=3D5413 loops=3D3)
                                             ->  Sort
(cost=3D665917.43..665917.64 rows=3D82 width=3D124) (actual
time=3D252.636..252.927 rows=3D5413 loops=3D3)

So, the patch was expected to cause the number of rows passing through
the Gather Merge to decrease from 197 to 19, but actually caused the
number of rows passing through the Gather Merge to increase from 901
to 15242. When the PartialAggregate was positioned at the top of the
join tree, it reduced the number of rows from 5081 to 300; but when it
was pushed down below two joins, it didn't reduce the row count at
all, and the subsequent two joins reduced it by less than 10%.

Now, you could complain about the fact that the Parallel Hash Join
isn't well-estimated here, but my question is: why does the planner
think that the PartialAggregate should go specifically here? In both
plans, the PartialAggregate isn't expected to change the row count.
And if that is true, then it's going to be cheapest to do it at the
point where the joins have reduced the row count to the minimum value.
Here, that would be at the top of the plan tree, where we have only
5081 estimated rows, but instead, the patch chooses to do it as soon
as we have all of the grouping columns, when we. still have 5413 rows.
I don't understand why that path wins on cost, unless it's just that
the paths compare fuzzily the same, in which case it kind of goes to
my earlier point about not really having the statistics to know which
way is actually going to be better.

--=20
Robert Haas
EDB: http://www.enterprisedb.com