Re: Add ExprState hashing for GROUP BY and hashed SubPlans

On Sat, 2 Nov 2024 at 22:13, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> Okay, I passed through the code. It looks good. Hashing expressions are
> too simple to give notably impressive results, but it is a step in the
> right direction.
> I found only one minor issue: an outdated comment (see attachment).

Thanks for looking. I ended up doing a bit more work on that comment.
See attached.

I was performing some final benchmarks on this and found that there's
a small performance regression for hashed subplans.

The test case is:
create table t1 (a int);
insert into t1 select a from generate_series(1,100000)a;

select * from t1 where a not in(select a from t1);

With my Zen4 laptop I get:

gcc:
master tps = 58.0375255
v5-0001 tps = 56.11840762

clang:
master tps = 58.72993378
v5-0001 tps = 53.39965968

Zen2 3990x
gcc:
master tps = 34.30392818
v5-0001 tps = 33.22067202

clang:
master tps = 34.0497471
v5-0001 tps = 33.34801254

That's the average over 50 runs starting and stopping the server on
each 10 second run.

I'm still thinking about what to do about this. In the meantime, I'm
not going to commit it.

> Also, to make a hash calculation as fast as possible, should we add the
> call of murmurhash32 as an optional step of ExprState in the
> ExecBuildHash32FromAttrs?

I wrote enough of this patch to test the performance. It does not
help. See attached 0002.

David