Re: Add ExprState hashing for GROUP BY and hashed SubPlans

On 10/31/24 08:16, David Rowley wrote:
> On Tue, 29 Oct 2024 at 22:47, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> I've attached an updated patch with a few other fixes. Whilr checking
>> this tonight, noticed that master does not use
>> SubPlanState.tab_eq_funcs for anything. I resisted removing that in
>> this patch. Perhaps a follow-on patch can remove that. I suspect it's
>> not been used for a long time now, but I didn't do the archaeology
>> work to find out.
>
> 3974bc319 removed the SubPlanState.tab_eq_funcs field, so here's a
> rebased patch.
Thanks for sharing this.
I still need to dive deeply into the code. But I have one annoying user
case where the user complained about a 4x SQL server speedup in
comparison to Postgres, and I guess it is a good benchmark for your code.
This query is remarkable because of high grouping computation load. Of
course, I can't provide the user's data, but I have prepared a synthetic
test to reproduce the case (see attachment).
Comparing the master with and without your patch, the first, I see is
more extensive usage of memory (see complete explains in the attachment):

Current master:
---------------

Partial HashAggregate (cost=54492.60..55588.03 rows=19917 width=889)
(actual time=20621.028..20642.664 rows=10176 loops=9)
Group Key: t1.x1, t1.x2, t1.x3, t1.x4, t1.x5
Batches: 1 Memory Usage: 74513kB

Patched:
--------

Partial HashAggregate (cost=54699.91..55799.69 rows=19996 width=889)
(actual time=57213.280..186216.604 rows=10302738 loops=9)
Group Key: t1.x1, t1.x2, t1.x3, t1.x4, t1.x5
Batches: 261 Memory Usage: 527905kB Disk Usage: 4832656kB

I wonder what causes memory consumption, but it is hard to decide on the
patch's positive outcome for now.

--
regards, Andrei Lepikhov