Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgaU3-004fPC-Hl for pgsql-hackers@arkaria.postgresql.org; Wed, 21 Aug 2024 01:50:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sgaU1-005Stn-6R for pgsql-hackers@arkaria.postgresql.org; Wed, 21 Aug 2024 01:49:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sgaTz-005Stf-VX for pgsql-hackers@lists.postgresql.org; Wed, 21 Aug 2024 01:49:57 +0000 Received: from smtp217-64.mail.sohu.com ([116.130.217.64] helo=smtp123-59.mail.sohu.com) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sgaTp-000eoX-2r for pgsql-hackers@postgresql.org; Wed, 21 Aug 2024 01:49:53 +0000 Received: from wooking (unknown [110.191.179.145]) by smtp123-59.mail.sohu.com (Postfix) with ESMTPA id 4WpTm26LGtzQjKB; Wed, 21 Aug 2024 09:49:38 +0800 (CST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sohu.com; s=mail; t=1724204979; bh=fuvThgKVDnQFkjgDE72XvnWPWd9jSu2DI7ML4hSyFxQ=; h=From:To:Cc:Subject:Date:From; b=vN48tmEKafMuCXM/E3QAiXl8/YsF6/2nXi/35J87aiVTwaMdcgPxv2lIpYa7/5jQQ 3sTDeEfKXbFJm4V9+qddcm6IphJIoYOHBiQ4BGJRFuCRu9/uYJNYiN8Tm8vfe+n3jV LzlTtwVi1kKYCayFE40/4L507AnN33XEdlfNoZ3A= From: "bucoo" To: "'pgsql-hackers'" Cc: "'Tom Lane'" , "'Robert Haas'" Subject: optimize hashjoin Date: Wed, 21 Aug 2024 09:49:37 +0800 Message-ID: <007601daf36c$62739350$275ab9f0$@sohu.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0077_01DAF3AF.7096D350" X-Mailer: Microsoft Outlook 16.0 Thread-Index: Adrzascgi1NAv90QSDezmbbdZy+PaQ== Content-Language: zh-cn X-Sohu-Gateway: 1 X-Sohu-Antispam-Language: 0 X-Sohu-Antispam-Score: 0.109890184831 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0077_01DAF3AF.7096D350 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0078_01DAF3AF.7096D350" ------=_NextPart_001_0078_01DAF3AF.7096D350 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Avoid unnecessary form and deform tuple. In the TPCH test, HashJoin speed up to ~2x. ------=_NextPart_001_0078_01DAF3AF.7096D350 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Avoid unnecessary form and deform = tuple.

In = the TPCH test, HashJoin speed up to = ~2x.

------=_NextPart_001_0078_01DAF3AF.7096D350-- ------=_NextPart_000_0077_01DAF3AF.7096D350 Content-Type: text/plain; name="optimize-hashjoin.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="optimize-hashjoin.patch" diff --git a/src/backend/executor/nodeHash.c = b/src/backend/executor/nodeHash.c=0A= index 61480733a1..2dad0c8a55 100644=0A= --- a/src/backend/executor/nodeHash.c=0A= +++ b/src/backend/executor/nodeHash.c=0A= @@ -1627,6 +1627,23 @@ ExecHashTableInsert(HashJoinTable hashtable,=0A= {=0A= bool shouldFree;=0A= MinimalTuple tuple =3D ExecFetchSlotMinimalTuple(slot, &shouldFree);=0A= +=0A= + ExecHashTableInsertTuple(hashtable, tuple, hashvalue);=0A= +=0A= + if (shouldFree)=0A= + heap_free_minimal_tuple(tuple);=0A= +}=0A= +=0A= +/*=0A= + * ExecHashTableInsert=0A= + * insert a tuple into the hash table depending on the hash value=0A= + * it may just go to a temp file for later batches=0A= + */=0A= +void=0A= +ExecHashTableInsertTuple(HashJoinTable hashtable,=0A= + MinimalTuple tuple,=0A= + uint32 hashvalue)=0A= +{=0A= int bucketno;=0A= int batchno;=0A= =0A= @@ -1701,9 +1718,6 @@ ExecHashTableInsert(HashJoinTable hashtable,=0A= &hashtable->innerBatchFile[batchno],=0A= hashtable);=0A= }=0A= -=0A= - if (shouldFree)=0A= - heap_free_minimal_tuple(tuple);=0A= }=0A= =0A= /*=0A= @@ -1777,12 +1791,10 @@ retry:=0A= * tuples that belong in the current batch once growth has been = disabled.=0A= */=0A= void=0A= -ExecParallelHashTableInsertCurrentBatch(HashJoinTable hashtable,=0A= - TupleTableSlot *slot,=0A= - uint32 hashvalue)=0A= +ExecParallelHashTableInsertCurrentBatchTuple(HashJoinTable hashtable,=0A= + MinimalTuple tuple,=0A= + uint32 hashvalue)=0A= {=0A= - bool shouldFree;=0A= - MinimalTuple tuple =3D ExecFetchSlotMinimalTuple(slot, &shouldFree);=0A= HashJoinTuple hashTuple;=0A= dsa_pointer shared;=0A= int batchno;=0A= @@ -1798,6 +1810,21 @@ = ExecParallelHashTableInsertCurrentBatch(HashJoinTable hashtable,=0A= HeapTupleHeaderClearMatch(HJTUPLE_MINTUPLE(hashTuple));=0A= ExecParallelHashPushTuple(&hashtable->buckets.shared[bucketno],=0A= hashTuple, shared);=0A= +}=0A= +=0A= +/*=0A= + * like ExecParallelHashTableInsertCurrentBatchTuple,=0A= + * but this function accept a TupleTableSlot=0A= + */=0A= +void=0A= +ExecParallelHashTableInsertCurrentBatch(HashJoinTable hashtable,=0A= + TupleTableSlot *slot,=0A= + uint32 hashvalue)=0A= +{=0A= + bool shouldFree;=0A= + MinimalTuple tuple =3D ExecFetchSlotMinimalTuple(slot, &shouldFree);=0A= +=0A= + ExecParallelHashTableInsertCurrentBatchTuple(hashtable, tuple, = hashvalue);=0A= =0A= if (shouldFree)=0A= heap_free_minimal_tuple(tuple);=0A= diff --git a/src/backend/executor/nodeHashjoin.c = b/src/backend/executor/nodeHashjoin.c=0A= index 5f4073eabd..002098f129 100644=0A= --- a/src/backend/executor/nodeHashjoin.c=0A= +++ b/src/backend/executor/nodeHashjoin.c=0A= @@ -194,10 +194,10 @@ static TupleTableSlot = *ExecHashJoinOuterGetTuple(PlanState *outerNode,=0A= static TupleTableSlot *ExecParallelHashJoinOuterGetTuple(PlanState = *outerNode,=0A= HashJoinState *hjstate,=0A= uint32 *hashvalue);=0A= -static TupleTableSlot *ExecHashJoinGetSavedTuple(HashJoinState *hjstate,=0A= - BufFile *file,=0A= - uint32 *hashvalue,=0A= - TupleTableSlot *tupleSlot);=0A= +static MinimalTuple ExecHashJoinGetSavedTuple(HashJoinState *hjstate,=0A= + BufFile *file,=0A= + uint32 *hashvalue,=0A= + StringInfo buf);=0A= static bool ExecHashJoinNewBatch(HashJoinState *hjstate);=0A= static bool ExecParallelHashJoinNewBatch(HashJoinState *hjstate);=0A= static void ExecParallelHashJoinPartitionOuter(HashJoinState *hjstate);=0A= @@ -831,6 +831,7 @@ ExecInitHashJoin(HashJoin *node, EState *estate, int = eflags)=0A= */=0A= hjstate->hj_HashTable =3D NULL;=0A= hjstate->hj_FirstOuterTupleSlot =3D NULL;=0A= + hjstate->hj_outerTupleBuffer =3D NULL;=0A= =0A= hjstate->hj_CurHashValue =3D 0;=0A= hjstate->hj_CurBucketNo =3D 0;=0A= @@ -936,6 +937,7 @@ ExecHashJoinOuterGetTuple(PlanState *outerNode,=0A= }=0A= else if (curbatch < hashtable->nbatch)=0A= {=0A= + MinimalTuple mtup;=0A= BufFile *file =3D hashtable->outerBatchFile[curbatch];=0A= =0A= /*=0A= @@ -945,12 +947,23 @@ ExecHashJoinOuterGetTuple(PlanState *outerNode,=0A= if (file =3D=3D NULL)=0A= return NULL;=0A= =0A= - slot =3D ExecHashJoinGetSavedTuple(hjstate,=0A= + if (unlikely(hjstate->hj_outerTupleBuffer =3D=3D NULL))=0A= + {=0A= + MemoryContext oldcontext =3D = MemoryContextSwitchTo(GetMemoryChunkContext(hjstate));=0A= + hjstate->hj_outerTupleBuffer =3D makeStringInfo();=0A= + MemoryContextSwitchTo(oldcontext);=0A= + }=0A= +=0A= + mtup =3D ExecHashJoinGetSavedTuple(hjstate,=0A= file,=0A= hashvalue,=0A= - hjstate->hj_OuterTupleSlot);=0A= - if (!TupIsNull(slot))=0A= + hjstate->hj_outerTupleBuffer);=0A= + if (likely(mtup !=3D NULL))=0A= + {=0A= + slot =3D hjstate->hj_OuterTupleSlot;=0A= + ExecForceStoreMinimalTuple(mtup, slot, false);=0A= return slot;=0A= + }=0A= }=0A= =0A= /* End of this batch */=0A= @@ -1034,7 +1047,6 @@ ExecHashJoinNewBatch(HashJoinState *hjstate)=0A= int nbatch;=0A= int curbatch;=0A= BufFile *innerFile;=0A= - TupleTableSlot *slot;=0A= uint32 hashvalue;=0A= =0A= nbatch =3D hashtable->nbatch;=0A= @@ -1125,21 +1137,25 @@ ExecHashJoinNewBatch(HashJoinState *hjstate)=0A= =0A= if (innerFile !=3D NULL)=0A= {=0A= + StringInfoData buf;=0A= + MinimalTuple tuple;=0A= +=0A= if (BufFileSeek(innerFile, 0, 0, SEEK_SET))=0A= ereport(ERROR,=0A= (errcode_for_file_access(),=0A= errmsg("could not rewind hash-join temporary file")));=0A= =0A= - while ((slot =3D ExecHashJoinGetSavedTuple(hjstate,=0A= - innerFile,=0A= - &hashvalue,=0A= - hjstate->hj_HashTupleSlot)))=0A= + initStringInfo(&buf);=0A= + while ((tuple =3D ExecHashJoinGetSavedTuple(hjstate,=0A= + innerFile,=0A= + &hashvalue,=0A= + &buf)))=0A= {=0A= /*=0A= * NOTE: some tuples may be sent to future batches. Also, it is=0A= * possible for hashtable->nbatch to be increased here!=0A= */=0A= - ExecHashTableInsert(hashtable, slot, hashvalue);=0A= + ExecHashTableInsertTuple(hashtable, tuple, hashvalue);=0A= }=0A= =0A= /*=0A= @@ -1148,6 +1164,7 @@ ExecHashJoinNewBatch(HashJoinState *hjstate)=0A= */=0A= BufFileClose(innerFile);=0A= hashtable->innerBatchFile[curbatch] =3D NULL;=0A= + pfree(buf.data);=0A= }=0A= =0A= /*=0A= @@ -1198,7 +1215,6 @@ ExecParallelHashJoinNewBatch(HashJoinState = *hjstate)=0A= {=0A= uint32 hashvalue;=0A= MinimalTuple tuple;=0A= - TupleTableSlot *slot;=0A= =0A= if (!hashtable->batches[batchno].done)=0A= {=0A= @@ -1230,12 +1246,9 @@ ExecParallelHashJoinNewBatch(HashJoinState = *hjstate)=0A= while ((tuple =3D sts_parallel_scan_next(inner_tuples,=0A= &hashvalue)))=0A= {=0A= - ExecForceStoreMinimalTuple(tuple,=0A= - hjstate->hj_HashTupleSlot,=0A= - false);=0A= - slot =3D hjstate->hj_HashTupleSlot;=0A= - ExecParallelHashTableInsertCurrentBatch(hashtable, slot,=0A= - hashvalue);=0A= + ExecParallelHashTableInsertCurrentBatchTuple(hashtable,=0A= + tuple,=0A= + hashvalue);=0A= }=0A= sts_end_parallel_scan(inner_tuples);=0A= BarrierArriveAndWait(batch_barrier,=0A= @@ -1349,14 +1362,14 @@ ExecHashJoinSaveTuple(MinimalTuple tuple, uint32 = hashvalue,=0A= * ExecHashJoinGetSavedTuple=0A= * read the next tuple from a batch file. Return NULL if no more.=0A= *=0A= - * On success, *hashvalue is set to the tuple's hash value, and the = tuple=0A= - * itself is stored in the given slot.=0A= + * On success, *hashvalue is set to the tuple's hash value, and return=0A= + * the tuple(stored in the given buf) itself.=0A= */=0A= -static TupleTableSlot *=0A= +static MinimalTuple=0A= ExecHashJoinGetSavedTuple(HashJoinState *hjstate,=0A= BufFile *file,=0A= uint32 *hashvalue,=0A= - TupleTableSlot *tupleSlot)=0A= + StringInfo buf)=0A= {=0A= uint32 header[2];=0A= size_t nread;=0A= @@ -1375,19 +1388,19 @@ ExecHashJoinGetSavedTuple(HashJoinState *hjstate,=0A= * cheating.=0A= */=0A= nread =3D BufFileReadMaybeEOF(file, header, sizeof(header), true);=0A= - if (nread =3D=3D 0) /* end of file */=0A= - {=0A= - ExecClearTuple(tupleSlot);=0A= + if (unlikely(nread =3D=3D 0)) /* end of file */=0A= return NULL;=0A= - }=0A= +=0A= + enlargeStringInfo(buf, header[1]);=0A= *hashvalue =3D header[0];=0A= - tuple =3D (MinimalTuple) palloc(header[1]);=0A= + buf->len =3D header[1];=0A= + tuple =3D (MinimalTuple) buf->data;=0A= tuple->t_len =3D header[1];=0A= BufFileReadExact(file,=0A= (char *) tuple + sizeof(uint32),=0A= header[1] - sizeof(uint32));=0A= - ExecForceStoreMinimalTuple(tuple, tupleSlot, true);=0A= - return tupleSlot;=0A= +=0A= + return tuple;=0A= }=0A= =0A= =0A= diff --git a/src/include/executor/nodeHash.h = b/src/include/executor/nodeHash.h=0A= index a95911c2fe..543f64cdf7 100644=0A= --- a/src/include/executor/nodeHash.h=0A= +++ b/src/include/executor/nodeHash.h=0A= @@ -37,12 +37,18 @@ extern void = ExecParallelHashTableSetCurrentBatch(HashJoinTable hashtable,=0A= extern void ExecHashTableInsert(HashJoinTable hashtable,=0A= TupleTableSlot *slot,=0A= uint32 hashvalue);=0A= +extern void ExecHashTableInsertTuple(HashJoinTable hashtable,=0A= + MinimalTuple tuple,=0A= + uint32 hashvalue);=0A= extern void ExecParallelHashTableInsert(HashJoinTable hashtable,=0A= TupleTableSlot *slot,=0A= uint32 hashvalue);=0A= extern void ExecParallelHashTableInsertCurrentBatch(HashJoinTable = hashtable,=0A= TupleTableSlot *slot,=0A= uint32 hashvalue);=0A= +extern void ExecParallelHashTableInsertCurrentBatchTuple(HashJoinTable = hashtable,=0A= + MinimalTuple tuple,=0A= + uint32 hashvalue);=0A= extern bool ExecHashGetHashValue(HashJoinTable hashtable,=0A= ExprContext *econtext,=0A= List *hashkeys,=0A= diff --git a/src/include/nodes/execnodes.h = b/src/include/nodes/execnodes.h=0A= index b62c96f206..e5e3a0a155 100644=0A= --- a/src/include/nodes/execnodes.h=0A= +++ b/src/include/nodes/execnodes.h=0A= @@ -2200,6 +2200,7 @@ typedef struct HashJoinState=0A= TupleTableSlot *hj_NullOuterTupleSlot;=0A= TupleTableSlot *hj_NullInnerTupleSlot;=0A= TupleTableSlot *hj_FirstOuterTupleSlot;=0A= + StringInfo hj_outerTupleBuffer;=0A= int hj_JoinState;=0A= bool hj_MatchedOuter;=0A= bool hj_OuterNotEmpty;=0A= ------=_NextPart_000_0077_01DAF3AF.7096D350--