| From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
|---|---|
| To: | Andres Freund <andres(at)anarazel(dot)de> |
| Cc: | Ants Aasma <ants(dot)aasma(at)cybertec(dot)at>, Tomas Vondra <tomas(at)vondra(dot)me>, Richard Guo <guofenglinux(at)gmail(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Hash aggregate collisions cause excessive spilling |
| Date: | 2026-11-20 03:33:28 |
| Message-ID: | CAApHDvr9Qrp8u+QuV16KumgfunYPcqRVkrVJG_Hd1ozw5doUSA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
On Fri, 20 Nov 2026 at 06:44, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2026-03-01 19:19:38 +0200, Ants Aasma wrote:
> > On Sat, 28 Feb 2026 at 22:03, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > Hm, won't that mean we'll now always use the slower path? I think there are
> > > non-partial cases that could be problematic, but if we do this we probaly
> > > should do the optimization of the hash expression stuff I mentioned above.
> >
> > If you mean EEOP_HASHDATUM_FIRST, then I think that would just be
> > removing an optimization. I think getting rid of the quicker growth
> > and correlated table filling in the 1:1 case might be more significant
> > than the slightly slower hash calculation. However the downside of the
> > bad case is pretty nasty. Do you think it's worth the effort of trying
> > to figure out if we are in a dangerous looking structure?
>
> David, any opinion?
Looks like with the proposed patch, most of the extra work will be
done by ExecJustHashInnerVarWithIV() instead of
ExecJustHashInnerVar(), at least for the single GROUP BY column case.
So probably not too much more expensive. Might be worth benchmarking
to check. Checking 2 GROUP BY columns would be wise too as introducing
an IV there means a few more steps in the ExprState as there's
EEOP_HASHDATUM_SET_INITVAL. IIRC, there wasn't enough space in the
hashdatum union for init_value (due to the 64-byte size limit for
ExprEvalStep), so had to put that in hashdatum_initvalue. I think
otherwise there could be a EEOP_HASHDATUM_FIRST_WITH_IV step that
hashes in the IV along with the first column.
I think we do need a fix in hash_agg_check_limits() too. Ants
mentioned in the final paragraph of [1] about just using 25% more for
tuples. That should stop the 1-tuple-per-batch issue.
Maybe something like:
if (aggstate->hash_ngroups_current > 0 &&
- (total_mem > aggstate->hash_mem_limit ||
+ ((total_mem > aggstate->hash_mem_limit &&
+ (entry_mem + tval_mem) > aggstate->hash_mem_limit / 4) ||
ngroups > aggstate->hash_ngroups_limit))
or we could try to partition the memory so that we split the available
memory into maximum thresholds and spill if any portion goes over its
threshold. That way we don't have to code it to go 25% over hash_mem
in some cases.
Maybe (entry_mem + tval_mem) > aggstate->hash_mem_limit / 2 ||
meta_mem > aggstate->hash_mem_limit / 2 ?
It's quite hard to know the repercussions there, though as surely that
would end up slowing down some existing workload as it'd cause more
batches.
We might also need to rethink 34d52e6d3, as the planner might opt to
give us some silly number of buckets from the get-go, leaving no space
for anything else. We could probably modify the code changed in that
commit to divide the size by 4.
David
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Neil Chen | 2026-11-20 03:36:29 | [PATCH] Handle SQL NULL results from casts in JSON generation |
| Previous Message | shveta malik | 2026-11-20 03:32:22 | Re: Initial table sync copies the whole table when it is no longer published |