Add ExprState hashing for GROUP BY and hashed SubPlans

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Add ExprState hashing for GROUP BY and hashed SubPlans
Date: 2024-09-01 11:49:19
Message-ID: CAApHDvpYSO3kc9UryMevWqthTBrxgfd9djiAjKHMPUSQeX9vdQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

adf97c156 added support to allow ExprStates to support hashing and
adjusted Hash Join to make use of that. That allowed a speedup in hash
value generation as it allowed JIT compilation of hash values. It also
allowed more efficient tuple deforming as all required attributes are
deformed in one go rather than on demand when hashing each join key.

The attached does the same for GROUP BY and hashed SubPlans. The win
for the tuple deformation does not exist here, but there does seem to
be some gains still to be had from JIT compilation.

Using a scale=1 TPC-H lineitem table, I ran the attached script.

The increase is far from impressive, but likely worth migrating these
over to use ExprState too.

master:

alter system set jit = 0;
latency average = 1509.116 ms
latency average = 1502.496 ms
latency average = 1507.560 ms
alter system set jit = 1;
latency average = 1396.015 ms
latency average = 1392.138 ms
latency average = 1396.476 ms
alter system set jit_optimize_above_cost = 0;
latency average = 1290.463 ms
latency average = 1293.364 ms
latency average = 1290.366 ms
alter system set jit_inline_above_cost = 0;
latency average = 1294.540 ms
latency average = 1300.970 ms
latency average = 1302.181 ms

patched:

alter system set jit = 0;
latency average = 1500.183 ms
latency average = 1500.911 ms
latency average = 1504.150 ms (+0.31%)
alter system set jit = 1;
latency average = 1367.427 ms
latency average = 1367.329 ms
latency average = 1366.473 ms (+2.03%)
alter system set jit_optimize_above_cost = 0;
latency average = 1273.453 ms
latency average = 1265.348 ms
latency average = 1272.598 ms (+1.65%)
alter system set jit_inline_above_cost = 0;
latency average = 1264.657 ms
latency average = 1272.661 ms
latency average = 1273.179 ms (+2.29%)

David

Attachment Content-Type Size
hashjoin_bench.sh.txt text/plain 2.0 KB
v1-0001-Use-ExprStates-for-hashing-in-GROUP-BY-and-SubPla.patch application/octet-stream 15.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Lakhin 2024-09-01 12:00:00 Re: Improving tracking/processing of buildfarm test failures
Previous Message David Rowley 2024-09-01 09:31:18 Re: ANALYZE ONLY