From: "bucoo" <bucoo@sohu.com>
To: "'Tomas Vondra'" <tomas@vondra.me>,
	"'pgsql-hackers'" <pgsql-hackers@postgresql.org>
Cc: "'Tom Lane'" <tgl@sss.pgh.pa.us>,
	"'Robert Haas'" <robertmhaas@gmail.com>
References: <1724328492.62ed368fef9349e68728790d2af02e3f.bucoo@sohu.com> <1a55870e-f5f4-46ae-9676-00a2bbb7462e@vondra.me>
In-Reply-To: <1a55870e-f5f4-46ae-9676-00a2bbb7462e@vondra.me>
Subject: =?utf-8?Q?=E7=AD=94=E5=A4=8D:_optimize_hashjoin?=
Date: Fri, 23 Aug 2024 19:02:22 +0800
Message-ID: <000001daf54b$f134e9a0$d39ebce0$@sohu.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQEzLhZfN5KFngpGSB3DhWMguAAbkwJze/Zfs3CV9FA=
Content-Language: zh-cn
Archived-At: <https://www.postgresql.org/message-id/000001daf54b%24f134e9a0%24d39ebce0%24%40sohu.com>
Precedence: bulk


>  * mtup is hold in hjstate->hj_outerTupleBuffer, so we can using
>  * shouldFree as false to call ExecForceStoreMinimalTuple().
>  *
>  * When slot is TTSOpsMinimalTuple we can avoid realloc memory for
>  * new MinimalTuple(reuse StringInfo to call =
ExecHashJoinGetSavedTuple).
>=20
> But my point was that I don't think the palloc/repalloc should be very =
expensive, once the AllocSet warms up a bit.

Avoiding memory palloc/repalloc is just a side effect of avoiding reform =
tuple.

>  * More importantly, in non-TTSOpsMinimalTuple scenarios, it can avoid
>  * reform(materialize) tuple(see ExecForceStoreMinimalTuple).
>=20
> Yeah, but doesn't that conflate two things - materialization and =
freeing the memory? Only because materialization is expensive, is that a =
good reason to abandon the memory management too?

Currently, I haven't thought of a better way to avoid reform.

> >=20
> >> Can you provide more information about the benchmark you did? What=20
> >> hardware, what scale, PostgreSQL configuration, which of the 22=20
> >> queries are improved, etc.
> >>
> >> I ran TPC-H with 1GB and 10GB scales on two machines, and I see=20
> >> pretty much no difference compared to master. However, it occurred =
to=20
> >> me the patch only ever helps if we increase the number of batches=20
> >> during execution, in which case we need to move tuples to the right =
batch.
> >=20
> > Only parallel HashJoin speed up to ~2x(all data cached in memory),
> >=20
> > not full query, include non-parallel HashJoin.
> >=20
> > non-parallel HashJoin only when batchs large then one will speed up,
> >=20
> > because this patch only optimize for read batchs tuples to memory.
> >=20
>=20
> I'm sorry, but this does not answer *any* of the questions I asked.
>=20
> Please provide enough info to reproduce the benefit - benchmark scale, =
which query, which > parameters, etc. Show explain / explain analyze of =
the query without / with the patch, stuff > like that.
>=20
> I ran a number of TPC-H benchmarks with the patch and I never a =
benefit of this scale.

After further testing, it turns out that the parallel hashjoin did not =
improve performance. I might have compared it with a debug version at =
the time. I apologize for that.

Howerver, the non-parallel hashjoin indeed showed about a 10% =
performance improvement.
Here is the testing information:

CPU: 13th Gen Intel(R) Core(TM) i7-13700
Memory: 32GB
SSD: UMIS REPEYJ512MKN1QWQ
Windows version: win11 23H2 22631.4037
WSL version: 2.2.4.0
Kernel version: 5.15.153.1-2
OS version: rocky linux 9.4
TPCH: SF=3D8

SQL:
set max_parallel_workers_per_gather =3D 0;
set enable_mergejoin =3D off;
explain (verbose,analyze)
select count(*)
from lineitem, orders
where lineitem.l_orderkey =3D orders.o_orderkey;

patch before:
Aggregate  (cost=3D2422401.83..2422401.84 rows=3D1 width=3D8) (actual =
time=3D10591.679..10591.681 rows=3D1 loops=3D1)
   Output: count(*)
   ->  Hash Join  (cost=3D508496.00..2302429.31 rows=3D47989008 =
width=3D0) (actual time=3D1075.213..9503.727 rows=3D47989007 loops=3D1)
         Inner Unique: true
         Hash Cond: (lineitem.l_orderkey =3D orders.o_orderkey)
         ->  Index Only Scan using lineitem_pkey on public.lineitem  =
(cost=3D0.56..1246171.69 rows=3D47989008 width=3D4) (actual =
time=3D0.023..1974.365 rows=3D47989007 loops=3D1)
               Output: lineitem.l_orderkey
               Heap Fetches: 0
         ->  Hash  (cost=3D311620.43..311620.43 rows=3D12000000 =
width=3D4) (actual time=3D1074.155..1074.156 rows=3D12000000 loops=3D1)
               Output: orders.o_orderkey
               Buckets: 262144  Batches: 128  Memory Usage: 5335kB
               ->  Index Only Scan using orders_pkey on public.orders  =
(cost=3D0.43..311620.43 rows=3D12000000 width=3D4) (actual =
time=3D0.014..464.346 rows=3D12000000 loops=3D1)
                     Output: orders.o_orderkey
                     Heap Fetches: 0
 Planning Time: 0.141 ms
 Execution Time: 10591.730 ms
(16 rows)

Patch after:
Aggregate  (cost=3D2422401.83..2422401.84 rows=3D1 width=3D8) (actual =
time=3D9826.105..9826.106 rows=3D1 loops=3D1)
   Output: count(*)
   ->  Hash Join  (cost=3D508496.00..2302429.31 rows=3D47989008 =
width=3D0) (actual time=3D1087.588..8726.441 rows=3D47989007 loops=3D1)
         Inner Unique: true
         Hash Cond: (lineitem.l_orderkey =3D orders.o_orderkey)
         ->  Index Only Scan using lineitem_pkey on public.lineitem  =
(cost=3D0.56..1246171.69 rows=3D47989008 width=3D4) (actual =
time=3D0.015..1989.389 rows=3D47989007 loops=3D1)
               Output: lineitem.l_orderkey
               Heap Fetches: 0
         ->  Hash  (cost=3D311620.43..311620.43 rows=3D12000000 =
width=3D4) (actual time=3D1086.357..1086.358 rows=3D12000000 loops=3D1)
               Output: orders.o_orderkey
               Buckets: 262144  Batches: 128  Memory Usage: 5335kB
               ->  Index Only Scan using orders_pkey on public.orders  =
(cost=3D0.43..311620.43 rows=3D12000000 width=3D4) (actual =
time=3D0.011..470.225 rows=3D12000000 loops=3D1)
                     Output: orders.o_orderkey
                     Heap Fetches: 0
 Planning Time: 0.065 ms
 Execution Time: 9826.135 ms