Re: Add ExprState hashing for GROUP BY and hashed SubPlans

On Thu, 28 Nov 2024 at 20:05, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> The v6-0002 patch does contain some additional ExecJust*
> functions to improve the performance of Hash Joins. I'll remove those
> and test them independently.

Slightly revised plan. I feel that it's better to add the ExecJust*
functions first as these benefit hash joins. With those added, they
can be used for the GROUP BY and NOT IN code right away.

I benchmarked a slightly revised version of the v6-0002 (attached as
v7-0001) with the following hash join case.

create table t1 as select a/100000 as a from generate_series(0,999999)a;
create table t2 as select a from generate_series(0,9)a;

vacuum freeze analyze t1,t2;

bench.sql:
select * from t1 inner join t2 on t1.a=t2.a offset 1000000;

for i in {1..50}; do pg_ctl stop -D pgdata > /dev/null && pg_ctl start
-D pgdata -l pg.log > /dev/null && psql -c "select
pg_prewarm('t1'),pg_prewarm('t2');" postgres > /dev/null && sleep 10
&& pgbench -n -f bench.sql -T 10 -M prepared postgres | grep tps; done

The patch gave me a 10.62% speed-up for an inner hash join on a single
column using an AMD Zen4 7945HX. The attached graph shows master vs
v7-0001 patch with the percentage of performance improvement on the
right axis and the tps on the left vertical axis. Each of the tps from
the 50 runs were sorted and the tps value is displayed for each.

I'm planning on pushing the v7-0001 patch tomorrow and will look
further at the 0002 after that.

David