How PostgreSQL Speeds Up Hash Joins

A hash join is the simplest join algorithm in the database: build a hash table from the inner relation, then probe it once per outer row. There is no sort, no merge, no nested loop over the inner side.

And yet it is one of the most performance-sensitive pieces of the executor, because it does the same tiny bit of work an enormous number of times: compute a hash value, look it up, compare the key. For a join between a million-row hash table and a ten-million-row outer relation, that is ten million hash computations and ten million bucket scans, and each one is measured in tens of nanoseconds. Everything else is noise.

So PostgreSQL has spent the last few years shaving that loop down. The strategy has been consistent: move the hashing out of the general-purpose expression interpreter and into specialised C functions, selected by pattern-matching the compiled expression’s step list at startup time.

This is a walk through how it all works, based on reading the PostgreSQL source (version 20devel, commit e75fcc9867f).

Where the time goes

A hash join has two sides and two kinds of work.

Building the hash table. Every inner row’s join key has to be hashed and the row inserted into a bucket chain. This is done once per inner row.

Probing. Every outer row’s join key has to be hashed, the resulting bucket found, and the bucket chain walked, comparing keys until a match is found. This is done once per outer row, and it is the side that dominates: a hash join between a small inner table and a huge outer relation does enormously more probing than building.

Both sides need the same hash function for a given join key, and PostgreSQL has to compute it from an arbitrary expression (the join key can be a.b + 1, or lower(name), or a plain column).

That expression is evaluated by the expression interpreter (src/backend/executor/execExprInterp.c), which compiles an expression tree into a flat list of steps and then runs them through a threaded dispatch loop. The interpreter is fast, but it has a fixed per-step overhead, and for a hash join the step list for “hash column 3” is tiny — four or five steps for what is, in machine code, about six instructions.

The opportunity is obvious: if you know the shape of the step list before you run it, you can write a function that does the whole thing without a dispatch loop at all.

Hashing as an expression

The first move was to make hashing an expression operation at all. Commit adf97c15623 (“Speed up Hash Join by making ExprStates support hashing”, August 2024) introduced a family of new opcodes and the ExecBuildHash32Expr() constructor. Its commit message explains the motivation:

This allows both faster hashing and also JIT compilation of these expressions. This is especially useful when hash joins have multiple join keys as the previous code called ExecEvalExpr on each hash join key individually and that was inefficient as tuple deformation would have only taken into account one key at a time, which could lead to walking the tuple once for each join key. With the new code, we’ll determine the maximum attribute required and deform the tuple to that point only once.

Some performance tests done with this change have shown up to a 20% performance increase of a query containing a Hash Join without JIT compilation and up to a 26% performance increase when JIT is enabled and optimization and inlining were performed by the JIT compiler.

The opcodes themselves, from src/include/executor/execExpr.h:

c
	/* evaluation steps for hashing */
	EEOP_HASHDATUM_SET_INITVAL,
	EEOP_HASHDATUM_FIRST,
	EEOP_HASHDATUM_FIRST_STRICT,
	EEOP_HASHDATUM_NEXT32,
	EEOP_HASHDATUM_NEXT32_STRICT,

Five opcodes, with a clear division of labour:

SET_INITVAL seeds the hash with a caller-supplied 32-bit value.
FIRST hashes the first key and replaces the running value.
NEXT32 hashes the next key and combines it with the running value.
The _STRICT variants abort the whole computation when the key is NULL, instead of treating NULL as a zero hash.

The combination is a rotate-then-XOR, from the interpreter:

c
		EEO_CASE(EEOP_HASHDATUM_NEXT32)
		{
			FunctionCallInfo fcinfo = op->d.hashdatum.fcinfo_data;
			uint32		existinghash;

			existinghash = DatumGetUInt32(op->d.hashdatum.iresult->value);
			/* combine successive hash values by rotating */
			existinghash = pg_rotate_left32(existinghash, 1);

			/* leave the hash value alone on NULL inputs */
			if (!fcinfo->args[0].isnull)
			{
				uint32		hashvalue;

				/* execute hash func and combine with previous hash value */
				hashvalue = DatumGetUInt32(op->d.hashdatum.fn_addr(fcinfo));
				existinghash = existinghash ^ hashvalue;
			}

			*op->resvalue = UInt32GetDatum(existinghash);
			*op->resnull = false;

			EEO_NEXT();
		}

And the FIRST variant, which has to cope with NULL by storing zero so that a subsequent NEXT32 still has something to combine with:

c
		EEO_CASE(EEOP_HASHDATUM_FIRST)
		{
			FunctionCallInfo fcinfo = op->d.hashdatum.fcinfo_data;

			/*
			 * Save the Datum on non-null inputs, otherwise store 0 so that
			 * subsequent NEXT32 operations combine with an initialized value.
			 */
			if (!fcinfo->args[0].isnull)
				*op->resvalue = op->d.hashdatum.fn_addr(fcinfo);
			else
				*op->resvalue = (Datum) 0;

			*op->resnull = false;

			EEO_NEXT();
		}

Building the hash expression

ExecBuildHash32Expr() (src/backend/executor/execExpr.c) is the constructor. It takes the list of key expressions, the hash functions to use, and builds a step list:

c
ExprState *
ExecBuildHash32Expr(TupleDesc desc, const TupleTableSlotOps *ops,
					const Oid *hashfunc_oids, const List *collations,
					const List *hash_exprs, const bool *opstrict,
					PlanState *parent, uint32 init_value)
{
	ExprState  *state = makeNode(ExprState);
	ExprEvalStep scratch = {0};
	NullableDatum *iresult = NULL;
	List	   *adjust_jumps = NIL;
	ListCell   *lc;
	ListCell   *lc2;
	intptr_t	strict_opcode;
	intptr_t	opcode;
	int			num_exprs = list_length(hash_exprs);

	Assert(num_exprs == list_length(collations));

	state->parent = parent;

	/* Insert setup steps as needed. */
	ExecCreateExprSetupSteps(state, unconstify(Node *, (const Node *) hash_exprs));

	/*
	 * Make a place to store intermediate hash values between subsequent
	 * hashing of individual expressions.  We only need this if there is more
	 * than one expression to hash or an initial value plus one expression.
	 */
	if ((int64) num_exprs + (init_value != 0) > 1)
		iresult = palloc_object(NullableDatum);

The iresult slot is where the running hash value lives between keys. Notice the comment in the header of the function:

If any hash_expr yields NULL and the corresponding hash operator is strict, the created ExprState will return NULL. (If the operator is not strict, we treat NULL values as having a hash value of zero. The hash functions themselves are always treated as strict.)

The two modes matter, and the difference is the subject of a bug we will come back to.

The hash join node wires it up in ExecInitHashJoin() (src/backend/executor/nodeHashjoin.c):

c
		/*
		 * Build ExprStates to obtain hash values for either side of the join.
		 * Note: must build the ExprStates before ExecHashTableCreate() so we
		 * properly attribute any SubPlans that exist in the hash expressions
		 * to the correct PlanState.
		 */
		nkeys = list_length(node->hashoperators);

		outer_hashfuncid = palloc_array(Oid, nkeys);
		inner_hashfuncid = palloc_array(Oid, nkeys);
		hash_strict = palloc_array(bool, nkeys);
		...
		/*
		 * Build an ExprState to generate the hash value for the expressions
		 * on the outer side of the join.
		 */
		hjstate->hj_OuterHash =
			ExecBuildHash32Expr(hjstate->js.ps.ps_ResultTupleDesc,
								hjstate->js.ps.resultops,
								outer_hashfuncid,
								node->hashcollations,
								node->hashkeys,
								hash_strict,
								&hjstate->js.ps,
								0);

		/* As above, but for the inner side of the join */
		hashstate->hash_expr =
			ExecBuildHash32Expr(hashstate->ps.ps_ResultTupleDesc,
								hashstate->ps.resultops,
								inner_hashfuncid,
								node->hashcollations,
								hash->hashkeys,
								hash_strict,
								&hashstate->ps,
								0);

Two ExprStates, one per side, each evaluating a list of key expressions into a single 32-bit hash.

The inner side is evaluated when the hash table is built (src/backend/executor/nodeHash.c):

c
		slot = ExecProcNode(outerNode);
		if (TupIsNull(slot))
			break;
		/* We have to compute the hash value */
		econtext->ecxt_outertuple = slot;

		ResetExprContext(econtext);

		hashdatum = ExecEvalExprSwitchContext(node->hash_expr, econtext,
											  &isnull);

		if (!isnull)
		{
			/* normal case with a non-null join key */
			uint32		hashvalue = DatumGetUInt32(hashdatum);
			int			bucketNumber;

			bucketNumber = ExecHashGetSkewBucket(hashtable, hashvalue);
			if (bucketNumber != INVALID_SKEW_BUCKET_NO)
			{
				/* It's a skew tuple, so put it into that hash table */
				ExecHashSkewTableInsert(hashtable, slot, hashvalue,
										bucketNumber);
			}
			else
			{
				/* Not subject to skew optimization, so insert normally */
				ExecHashTableInsert(hashtable, slot, hashvalue);
			}
			hashtable->totalTuples += 1;
		}

And the outer side, once per probe, in nodeHashjoin.c:

c
			*hashvalue = DatumGetUInt32(ExecEvalExprSwitchContext(hjstate->hj_OuterHash,
																  econtext,
																  &isnull));

ExecEvalExprSwitchContext() is a thin wrapper that switches to the per-tuple memory context and then calls state->evalfunc():

c
static inline Datum
ExecEvalExprSwitchContext(ExprState *state,
						  ExprContext *econtext,
						  bool *isNull)
{
	Datum		retDatum;
	MemoryContext oldContext;

	oldContext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
	retDatum = state->evalfunc(state, econtext, isNull);
	MemoryContextSwitchTo(oldContext);
	return retDatum;
}

So state->evalfunc is the hook. Whatever it points at is what runs ten million times.

The interpreter

Before the optimisation, evalfunc always pointed at ExecInterpExpr(), the threaded interpreter. Its header comment explains the dispatch strategy:

This file provides either a “direct threaded” (for gcc, clang and compatible) or a “switch threaded” (for all compilers) implementation of expression evaluation. The former is amongst the fastest known methods of interpreting programs without resorting to assembly level work, or just-in-time compilation, but it requires support for computed gotos.

In direct threading, we use gcc’s label-as-values extension - also adopted by some other compilers - to replace ExprEvalStep->opcode with the address of the block implementing the instruction. Dispatch to the next instruction is done by a “computed goto”. This allows for better branch prediction (as the jumps are happening from different locations) and fewer jumps (as no preparatory jump to a common dispatch location is needed).

Direct threading is the classic “threaded code” technique: each opcode’s implementation ends with a jump to the next opcode’s implementation, so there is no central dispatch point at all. The interpreter is about as fast as an interpreter gets.

But it is still an interpreter. Every step pays for the dispatch.

And here is the key observation from ExecReadyInterpretedExpr():

For very simple instructions the overhead of the full interpreter “startup”, as minimal as it is, is noticeable. Therefore ExecReadyInterpretedExpr will choose to implement certain simple opcode patterns using special fast-path routines (ExecJust*).

Pattern-matching the step list

ExecReadyInterpretedExpr() (src/backend/executor/execExprInterp.c) runs once, when the expression is prepared. Its job is to look at the compiled step list and decide: “is this one of the shapes I know how to do without an interpreter?”

c
	/*
	 * Select fast-path evalfuncs for very simple expressions.  "Starting up"
	 * the full interpreter is a measurable overhead for these, and these
	 * patterns occur often enough to be worth optimizing.
	 */
	if (state->steps_len == 5)
	{
		ExprEvalOp	step0 = state->steps[0].opcode;
		ExprEvalOp	step1 = state->steps[1].opcode;
		ExprEvalOp	step2 = state->steps[2].opcode;
		ExprEvalOp	step3 = state->steps[3].opcode;

		if (step0 == EEOP_INNER_FETCHSOME &&
			step1 == EEOP_HASHDATUM_SET_INITVAL &&
			step2 == EEOP_INNER_VAR &&
			step3 == EEOP_HASHDATUM_NEXT32)
		{
			state->evalfunc_private = (void *) ExecJustHashInnerVarWithIV;
			return;
		}
	}
	else if (state->steps_len == 4)
	{
		ExprEvalOp	step0 = state->steps[0].opcode;
		ExprEvalOp	step1 = state->steps[1].opcode;
		ExprEvalOp	step2 = state->steps[2].opcode;

		if (step0 == EEOP_OUTER_FETCHSOME &&
			step1 == EEOP_OUTER_VAR &&
			step2 == EEOP_HASHDATUM_FIRST)
		{
			state->evalfunc_private = (void *) ExecJustHashOuterVar;
			return;
		}
		else if (step0 == EEOP_INNER_FETCHSOME &&
				 step1 == EEOP_INNER_VAR &&
				 step2 == EEOP_HASHDATUM_FIRST)
		{
			state->evalfunc_private = (void *) ExecJustHashInnerVar;
			return;
		}
		else if (step0 == EEOP_OUTER_FETCHSOME &&
				 step1 == EEOP_OUTER_VAR &&
				 step2 == EEOP_HASHDATUM_FIRST_STRICT)
		{
			state->evalfunc_private = (void *) ExecJustHashOuterVarStrict;
			return;
		}
	}
	else if (state->steps_len == 3)
	{
		...
		else if (step0 == EEOP_INNER_VAR &&
				 step1 == EEOP_HASHDATUM_FIRST)
		{
			state->evalfunc_private = (void *) ExecJustHashInnerVarVirt;
			return;
		}
		else if (step0 == EEOP_OUTER_VAR &&
				 step1 == EEOP_HASHDATUM_FIRST)
		{
			state->evalfunc_private = (void *) ExecJustHashOuterVarVirt;
			return;
		}
	}

This is the whole trick, and it is worth reading slowly.

It matches on steps_len first. The step count is a cheap necessary condition, and it buckets the cases: five steps, four steps, three steps, two steps.

It then matches on the exact opcode sequence. Not “contains a HASHDATUM somewhere” — the exact sequence, in order. EEOP_INNER_FETCHSOME, EEOP_INNER_VAR, EEOP_HASHDATUM_FIRST and nothing else.

Note what is not matched. There is no pattern for EEOP_INNER_FETCHSOME, EEOP_INNER_VAR, EEOP_HASHDATUM_NEXT32 — the non-strict “next key” case. There is no pattern with two key expressions. There is no pattern for a hash key that is an expression rather than a plain column. Every one of those falls through to the general interpreter.

The last step is always a terminator — EEOP_DONE_RETURN or EEOP_DONE_NO_RETURN, which is asserted at the top of ExecReadyInterpretedExpr(). That is why a four-opcode expression has steps_len == 5 (four real opcodes plus the terminator). The count includes the terminator.

The result is stored in evalfunc_private, not evalfunc. Look at the surrounding code:

c
	/*
	 * First time through, check whether attribute matches Var.  Might not be
	 * ok anymore, due to schema changes. We do that by setting up a callback
	 * that does checking on the first call, which then sets the evalfunc
	 * callback to the actual method of execution.
	 */
	state->evalfunc = ExecInterpExprStillValid;

The first call goes through a validity check (the schema might have changed since the plan was built), and only then does the real function get installed:

c
Datum
ExecInterpExprStillValid(ExprState *state, ExprContext *econtext, bool *isNull)
{
	/*
	 * First time through, check whether attribute matches Var.  Might not be
	 * ok anymore, due to schema changes.
	 */
	CheckExprStillValid(state, econtext);

	/* skip the check during further executions */
	state->evalfunc = (ExprStateEvalFunc) state->evalfunc_private;

	/* and actually execute */
	return state->evalfunc(state, econtext, isNull);
}

So evalfunc_private holds the chosen implementation, and evalfunc holds the trampoline that installs it. Elegant, and it means the one-time schema check is not paid on every call.

The specialised functions

Now the payoff. The four-step inner case:

c
/* implementation of ExecJustHash(Inner|Outer)Var */
static pg_always_inline Datum
ExecJustHashVarImpl(ExprState *state, TupleTableSlot *slot, bool *isnull)
{
	ExprEvalStep *fetchop = &state->steps[0];
	ExprEvalStep *var = &state->steps[1];
	ExprEvalStep *hashop = &state->steps[2];
	FunctionCallInfo fcinfo = hashop->d.hashdatum.fcinfo_data;
	int			attnum = var->d.var.attnum;

	CheckOpSlotCompatibility(fetchop, slot);
	slot_getsomeattrs(slot, fetchop->d.fetch.last_var);

	fcinfo->args[0].value = slot->tts_values[attnum];
	fcinfo->args[0].isnull = slot->tts_isnull[attnum];

	*isnull = false;

	if (!fcinfo->args[0].isnull)
		return hashop->d.hashdatum.fn_addr(fcinfo);
	else
		return (Datum) 0;
}

That is the entire hash computation, for the common case of “hash one plain column of the inner relation”:

Deform the tuple as far as needed (slot_getsomeattrs).
Copy the attribute value and its NULL flag into the function call frame.
Call the hash function.
If the value was NULL, return 0 (matching EEOP_HASHDATUM_FIRST’s “store 0 on NULL” behaviour).

No dispatch loop. No EEO_NEXT(). No step array traversal. The compiler can see the whole thing.

The outer and inner wrappers are one-liners:

c
/* implementation for hashing an outer Var */
static Datum
ExecJustHashOuterVar(ExprState *state, ExprContext *econtext, bool *isnull)
{
	return ExecJustHashVarImpl(state, econtext->ecxt_outertuple, isnull);
}

/* implementation for hashing an inner Var */
static Datum
ExecJustHashInnerVar(ExprState *state, ExprContext *econtext, bool *isnull)
{
	return ExecJustHashVarImpl(state, econtext->ecxt_innertuple, isnull);
}

The strict variant

The strict case is different, and the difference is exactly the bug discussed below. In strict mode a NULL key means “the whole hash is NULL” — the join key does not match anything:

c
/*
 * implementation for hashing an outer Var.  Returns NULL on NULL input.
 */
static Datum
ExecJustHashOuterVarStrict(ExprState *state, ExprContext *econtext,
						   bool *isnull)
{
	ExprEvalStep *fetchop = &state->steps[0];
	ExprEvalStep *var = &state->steps[1];
	ExprEvalStep *hashop = &state->steps[2];
	FunctionCallInfo fcinfo = hashop->d.hashdatum.fcinfo_data;
	int			attnum = var->d.var.attnum;

	CheckOpSlotCompatibility(fetchop, econtext->ecxt_outertuple);
	slot_getsomeattrs(econtext->ecxt_outertuple, fetchop->d.fetch.last_var);

	fcinfo->args[0].value = econtext->ecxt_outertuple->tts_values[attnum];
	fcinfo->args[0].isnull = econtext->ecxt_outertuple->tts_isnull[attnum];

	if (!fcinfo->args[0].isnull)
	{
		*isnull = false;
		return hashop->d.hashdatum.fn_addr(fcinfo);
	}
	else
	{
		/* return NULL on NULL input */
		*isnull = true;
		return (Datum) 0;
	}
}

The seeded variant

The five-step pattern is for a hash seeded with an initial value — used by parallel hash aggregation, which seeds the hash with a worker-specific IV:

c
/*
 * implementation for hashing an inner Var, seeding with an initial value.
 */
static Datum
ExecJustHashInnerVarWithIV(ExprState *state, ExprContext *econtext,
						   bool *isnull)
{
	ExprEvalStep *fetchop = &state->steps[0];
	ExprEvalStep *setivop = &state->steps[1];
	ExprEvalStep *innervar = &state->steps[2];
	ExprEvalStep *hashop = &state->steps[3];
	FunctionCallInfo fcinfo = hashop->d.hashdatum.fcinfo_data;
	int			attnum = innervar->d.var.attnum;
	uint32		hashkey;

	CheckOpSlotCompatibility(fetchop, econtext->ecxt_innertuple);
	slot_getsomeattrs(econtext->ecxt_innertuple, fetchop->d.fetch.last_var);

	fcinfo->args[0].value = econtext->ecxt_innertuple->tts_values[attnum];
	fcinfo->args[0].isnull = econtext->ecxt_innertuple->tts_isnull[attnum];

	hashkey = DatumGetUInt32(setivop->d.hashdatum_initvalue.init_value);
	hashkey = pg_rotate_left32(hashkey, 1);

	if (!fcinfo->args[0].isnull)
	{
		uint32		hashvalue;

		hashvalue = DatumGetUInt32(hashop->d.hashdatum.fn_addr(fcinfo));
		hashkey = hashkey ^ hashvalue;
	}

	*isnull = false;
	return UInt32GetDatum(hashkey);
}

Note the step indices: steps[1] is the SET_INITVAL step and steps[2] is the INNER_VAR step. The initial value is stored in the step’s own payload and is fetched directly, not through the generic *op->resvalue indirection.

The virtual-slot variant

The three-step pattern has no FETCHSOME step at all. That absence is meaningful, and it is the reason the virtual-slot variant can skip slot_getsomeattrs() entirely:

c
/* implementation for ExecJustHash(Inner|Outer)VarVirt */
static pg_always_inline Datum
ExecJustHashVarVirtImpl(ExprState *state, TupleTableSlot *slot, bool *isnull)
{
	ExprEvalStep *var = &state->steps[0];
	ExprEvalStep *hashop = &state->steps[1];
	FunctionCallInfo fcinfo = hashop->d.hashdatum.fcinfo_data;
	int			attnum = var->d.var.attnum;

	fcinfo->args[0].value = slot->tts_values[attnum];
	fcinfo->args[0].isnull = slot->tts_isnull[attnum];

	*isnull = false;

	if (!fcinfo->args[0].isnull)
		return hashop->d.hashdatum.fn_addr(fcinfo);
	else
		return (Datum) 0;
}

No slot_getsomeattrs. No CheckOpSlotCompatibility. It reads slot->tts_values[attnum] directly.

Why is that safe? Because a virtual slot has no on-disk tuple to deform. Its values are already materialised in the tts_values[] array. The interpreter knows this, and the code that emits the setup steps knows it too:

c
	/* if the slot is known to always virtual we never need to deform */
	if (op->d.fetch.fixed && op->d.fetch.kind == &TTSOpsVirtual)
		return false;

ExecComputeSlotInfo() returns false — meaning “do not push a FETCHSOME step” — when the slot is known to be virtual. That is why the virtual case produces a three-step expression rather than a four-step one, and why the pattern matcher can tell them apart by step count alone.

The ExecJustVarVirtImpl() counterpart says it explicitly:

c
	/*
	 * As it is guaranteed that a virtual slot is used, there never is a need
	 * to perform tuple deforming (nor would it be possible). Therefore
	 * execExpr.c has not emitted an EEOP_*_FETCHSOME step. Verify, as much as
	 * possible, that that determination was accurate.
	 */
	Assert(TTS_IS_VIRTUAL(slot));
	Assert(TTS_FIXED(slot));
	Assert(attnum >= 0 && attnum < slot->tts_nvalid);

For a heap tuple slot, slot_getsomeattrs() is not free — it is the tuple deformation machinery, which unpacks varlena headers, follows TOAST pointers, and so on. Skipping it when it is not needed is a real saving.

The payoff

Commit 50416cc4843 (“Speedup Hash Joins with dedicated functions for ExprState hashing”, December 2024) is the one that introduced the ExecJust*Hash* family. Its commit message:

Hashing of a single Var is a very common operation for ExprState to perform. Here we add dedicated ExecJust* functions which helps speed up Hash Joins by removing the interpretation overhead in ExecInterpExpr().
This change currently only affects Hash Joins on a single column. Hash Joins with multiple join keys or an expression still run through ExecInterpExpr().
Some testing has shown up to 10% query performance increases on recent AMD hardware and nearly 7% increase on an Apple M2 for a query performing a hash join with a large number of lookups on a small hash table.

Two things to note. First, the scope: single column only. Second, the shape of the workload that benefits: “a large number of lookups on a small hash table” — i.e. probe-dominated, which is exactly the case where per-probe overhead matters.

Hardcoding the NULL check

The same “specialise the common case” philosophy was applied to strict function calls. Commit d35d32d7112 (“Add special case fast-paths for strict functions”, March 2025):

Many STRICT function calls will have one or two arguments, in which case we can speed up checking for NULL input by avoiding setting up a loop over the arguments. This adds EEOP_FUNCEXPR_STRICT_1 and the corresponding EEOP_FUNCEXPR_STRICT_2 for functions with one and two arguments respectively.

The generic strict function step loops:

c
		/* strict function call with more than two arguments */
		EEO_CASE(EEOP_FUNCEXPR_STRICT)
		{
			FunctionCallInfo fcinfo = op->d.func.fcinfo_data;
			NullableDatum *args = fcinfo->args;
			int			nargs = op->d.func.nargs;
			Datum		d;

			Assert(nargs > 2);

			/* strict function, so check for NULL args */
			for (int argno = 0; argno < nargs; argno++)
			{
				if (args[argno].isnull)
				{
					*op->resnull = true;
					goto strictfail;
				}
			}
			fcinfo->isnull = false;
			d = op->d.func.fn_addr(fcinfo);
			*op->resvalue = d;
			*op->resnull = fcinfo->isnull;

	strictfail:
			EEO_NEXT();
		}

A loop, with a goto, and a nargs that is not known at compile time. The one-argument version:

c
		/* strict function call with one argument */
		EEO_CASE(EEOP_FUNCEXPR_STRICT_1)
		{
			FunctionCallInfo fcinfo = op->d.func.fcinfo_data;
			NullableDatum *args = fcinfo->args;

			Assert(op->d.func.nargs == 1);

			/* strict function, so check for NULL args */
			if (args[0].isnull)
				*op->resnull = true;
			else
			{
				Datum		d;

				fcinfo->isnull = false;
				d = op->d.func.fn_addr(fcinfo);
				*op->resvalue = d;
				*op->resnull = fcinfo->isnull;
			}

			EEO_NEXT();
		}

and the two-argument version:

c
		/* strict function call with two arguments */
		EEO_CASE(EEOP_FUNCEXPR_STRICT_2)
		{
			FunctionCallInfo fcinfo = op->d.func.fcinfo_data;
			NullableDatum *args = fcinfo->args;

			Assert(op->d.func.nargs == 2);

			/* strict function, so check for NULL args */
			if (args[0].isnull || args[1].isnull)
				*op->resnull = true;
			else
			{
				Datum		d;

				fcinfo->isnull = false;
				d = op->d.func.fn_addr(fcinfo);
				*op->resvalue = d;
				*op->resnull = fcinfo->isnull;
			}

			EEO_NEXT();
		}

The same trick appears for aggregate transition functions’ strict input checks:

c
		/* special case for just one argument */
		EEO_CASE(EEOP_AGG_STRICT_INPUT_CHECK_ARGS_1)
		{
			NullableDatum *args = op->d.agg_strict_input_check.args;
			PG_USED_FOR_ASSERTS_ONLY int nargs = op->d.agg_strict_input_check.nargs;

			Assert(nargs == 1);

			if (args[0].isnull)
				EEO_JUMP(op->d.agg_strict_input_check.jumpnull);
			EEO_NEXT();
		}

versus the general case, which loops over the arguments.

And the choice of opcode happens once, at expression-build time:

c
	/* Insert appropriate opcode depending on strictness and stats level */
	if (pgstat_track_functions <= flinfo->fn_stats)
	{
		if (flinfo->fn_strict && nargs > 0)
		{
			/* Choose nargs optimized implementation if available. */
			if (nargs == 1)
				scratch->opcode = EEOP_FUNCEXPR_STRICT_1;
			else if (nargs == 2)
				scratch->opcode = EEOP_FUNCEXPR_STRICT_2;
			else
				scratch->opcode = EEOP_FUNCEXPR_STRICT;
		}
		else
			scratch->opcode = EEOP_FUNCEXPR;
	}

Note the pgstat_track_functions condition: if per-function statistics collection is enabled, the specialised opcodes are not used, because they bypass the FUSAGE variants that do the counting.

The NULL-key bug

Specialisation is where bugs live. Commit 60826a352d4 (“Fix Hash Join performance issue when hashing NULL values”, July 2026) fixed a real one. Its message is a model of a good bug report:

adf97c156 allowed expression evaluation to perform hashing, and subsequently 9ca67658d fixed a memory stomping bug in that commit that caused unrelated-to-hashing expression op steps to stomp on the intermediate hash value. The intermediate hash value needs to be maintained when hashing multiple hash keys. 9ca67658d didn’t quite get things right when in “strict” mode when it aborted hashing early after encountering a NULL hash key. What was meant to happen was that the expression returns NULL directly to indicate to the caller the value hashed to NULL. The problem was that any EEOP_HASHDATUM_FIRST_STRICT or EEOP_HASHDATUM_NEXT32_STRICT op step that didn’t belong to the final key to be hashed would have its op->resnull and op->resvalue pointing to the location to store the intermediate hash value. That’s correct for non-NULLs since we bit-rotate the intermediate value and continue hashing, but with the strict case, when we get a NULL key, we immediately jump to the “jumpdone” step. The problem is the jumpdone step expects the ExprState resnull and resvalue fields to be set (as they would be if we didn’t abort hashing early due to the NULL), but when we aborted early, the ExprState fields never got set. This would result in inserting records into the hash table that would never match to any join partner, which is a waste of CPU and memory.

Let me unpack that, because it is subtle and it is a nice illustration of why specialisation is dangerous.

There are two “output slots” in play:

op->resvalue / op->resnull — where this step writes its result. For a key that is not the last one, this points at the intermediate hash slot (iresult), because the running hash must be preserved for the next key.
state->resvalue / state->resnull — the final result of the whole expression.

For a multi-key hash, the first key’s HASHDATUM_FIRST_STRICT step writes to the intermediate slot, and only the last key’s step writes to state->resvalue.

Now, in strict mode, when a NULL key is found, the interpreter aborts immediately:

c
		EEO_CASE(EEOP_HASHDATUM_FIRST_STRICT)
		{
			FunctionCallInfo fcinfo = op->d.hashdatum.fcinfo_data;

			if (fcinfo->args[0].isnull)
			{
				/*
				 * With strict we have the expression return NULL instead of
				 * ignoring NULL input values.  We've nothing more to do after
				 * finding a NULL.
				 */
				state->resnull = true;
				state->resvalue = (Datum) 0;
				EEO_JUMP(op->d.hashdatum.jumpdone);
			}

(note: this is the fixed version; the bug was *op->resnull = true; *op->resvalue = (Datum) 0;).

The bug: when the NULL was found in the first key of a multi-key hash, the step wrote to *op->resnull / *op->resvalue — the intermediate slot — and then jumped to the “done” step. The “done” step reads state->resnull / state->resvalue. Those were never written, so they held whatever was left over from the previous evaluation.

Consequences: the caller saw a non-NULL hash value for a tuple whose join key was NULL. That tuple got inserted into the hash table, where it could never match anything — pure waste of memory and CPU, in the hash-join build phase.

The fix, in both _STRICT opcodes, is to write to the expression’s result fields directly:

c
				state->resnull = true;
				state->resvalue = (Datum) 0;

instead of *op->resnull / *op->resvalue. One character of difference — op-> versus state-> — with a real performance consequence.

The commit also notes the blast radius:

Although Hash Agg and Hashed Subplans do use hashing from ExprStates, those were unaffected by this bug, as neither of those uses the STRICT op steps.

Which is precisely why it survived: hash aggregate and hashed subplans build their hash expressions with init_value == 0 and non-strict operators, so they never take the _STRICT path.

The reporter was a user: “Reported-by: Dan Stefura”.

The hash table side

Specialising the hash computation is only half of it. The probe side walks a bucket chain, and for each candidate it has to re-check the join qual. From ExecScanHashBucket() in nodeHash.c:

c
	while (hashTuple != NULL)
	{
		if (hashTuple->hashvalue == hashvalue)
		{
			TupleTableSlot *inntuple;

			/* insert hashtable's tuple into exec slot so ExecQual sees it */
			inntuple = ExecStoreMinimalTuple(HJTUPLE_MINTUPLE(hashTuple),
											 hjstate->hj_HashTupleSlot,
											 false);	/* do not pfree */
			econtext->ecxt_innertuple = inntuple;

			if (ExecQualAndReset(hjclauses, econtext))
			{
				hjstate->hj_CurTuple = hashTuple;
				return true;
			}
		}

		hashTuple = hashTuple->next.unshared;
	}

Two things happen per candidate: a cheap 32-bit hash comparison, and only on a match, the full join qual (hjclauses) is evaluated. The 32-bit comparison is what makes the bucket scan cheap — a hash collision is expected roughly once in 2^32 rows, so the qual almost never runs.

ExecQualAndReset() resets the expression context after each evaluation, so the qual’s temporary memory does not accumulate across candidates.

Memoize: a hash join that remembers

The Memoize node (src/backend/executor/nodeMemoize.c) is a materialised cache in front of a subplan, and it is the other place where hash-join-style machinery shows up: a hash table keyed by the subplan’s parameters, with an LRU eviction policy.

The probe slot

The design is unusual: the hash and equality functions do not use the stored key at all. They read the probe slot instead, which is populated before every lookup:

c
/*
 * MemoizeHash_hash
 *		Hash function for simplehash hashtable.  'key' is unused here as we
 *		require that all table lookups first populate the MemoizeState's
 *		probeslot with the key values to be looked up.
 */
static uint32
MemoizeHash_hash(struct memoize_hash *tb, const MemoizeKey *key)

The population function is prepare_probe_slot():

c
static inline void
prepare_probe_slot(MemoizeState *mstate, MemoizeKey *key)
{
	TupleTableSlot *pslot = mstate->probeslot;
	TupleTableSlot *tslot = mstate->tableslot;
	int			numKeys = mstate->nkeys;

	ExecClearTuple(pslot);

	if (key == NULL)
	{
		ExprContext *econtext = mstate->ss.ps.ps_ExprContext;
		MemoryContext oldcontext;

		oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);

		/* Set the probeslot's values based on the current parameter values */
		for (int i = 0; i < numKeys; i++)
			pslot->tts_values[i] = ExecEvalExpr(mstate->param_exprs[i],
												econtext,
												&pslot->tts_isnull[i]);

		MemoryContextSwitchTo(oldcontext);
	}
	else
	{
		/* Process the key's MinimalTuple and store the values in probeslot */
		ExecStoreMinimalTuple(key->params, tslot, false);
		slot_getallattrs(tslot);
		memcpy(pslot->tts_values, tslot->tts_values, sizeof(Datum) * numKeys);
		memcpy(pslot->tts_isnull, tslot->tts_isnull, sizeof(bool) * numKeys);
	}

	ExecStoreVirtualTuple(pslot);
}

Note the two paths. When looking up the current parameter values (key == NULL), the parameter expressions are evaluated directly into the probe slot’s arrays — no tuple copying at all. When re-hashing an entry that is being evicted, the stored key tuple is re-materialised into the probe slot so that the same hash function can be used.

And the probe slot is turned into a virtual tuple at the end:

c
	ExecStoreVirtualTuple(pslot);

which is the same trick the hash join’s virtual-slot fast path relies on: no deformation needed, values already materialised.

The LRU eviction

The cache is bounded by mem_limit, and when it is exceeded, entries are evicted from the least-recently-used end of a doubly-linked list:

c
/*
 * cache_reduce_memory
 *		Evict older and less recently used items from the cache in order to
 *		reduce the memory consumption back to something below the
 *		MemoizeState's mem_limit.
 */
static bool
cache_reduce_memory(MemoizeState *mstate, MemoizeKey *specialkey)
{
	...
	/* Start the eviction process starting at the head of the LRU list. */
	dlist_foreach_modify(iter, &mstate->lru_list)
	{
		MemoizeKey *key = dlist_container(MemoizeKey, lru_node, iter.cur);
		MemoizeEntry *entry;

		/*
		 * Populate the hash probe slot in preparation for looking up this LRU
		 * entry.
		 */
		prepare_probe_slot(mstate, key);

		/*
		 * Ideally the LRU list pointers would be stored in the entry itself
		 * rather than in the key.  Unfortunately, we can't do that as the
		 * simplehash.h code may resize the table and allocate new memory for
		 * entries which would result in those pointers pointing to the old
		 * buckets.  However, it's fine to use the key to store this as that's
		 * only referenced by a pointer in the entry, which of course follows
		 * the entry whenever the hash table is resized.  Since we only have a
		 * pointer to the key here, we must perform a hash table lookup to
		 * find the entry that the key belongs to.
		 */
		entry = memoize_lookup(mstate->hashtable, NULL);
		...
		remove_cache_entry(mstate, entry);

		evictions++;

		/* Exit if we've freed enough memory */
		if (mstate->mem_used <= mstate->mem_limit)
			break;
	}

Note the comment about why the LRU list pointer lives in the key rather than the entry: simplehash.h may resize the table and reallocate its buckets, invalidating pointers stored inside the entries. The key is referenced by pointer from the entry, so it moves with the entry.

The eviction loop has to do a full hash-table lookup per evicted entry (just to find the entry that belongs to the key it is evicting) — the price of that design decision.

16 bytes per tuple

Commit 609f969f61b (“Reduce memory overheads for storing a Memoize tuple”, August 2026) is a nice piece of allocator-level thinking. Each cached tuple used to be a separately allocated structure plus a MinimalTuple. Now the “next tuple” pointer is stored in 8 extra bytes allocated with the tuple:

c
/*
 * The number of extra bytes we request from ExecCopySlotMinimalTupleExtra to
 * allow storage of the pointer to the next cached tuple for a MemoizeEntry.
 */
#define MEMOIZE_NEXT_TUPLE_EXTRA_BYTES MAXALIGN(sizeof(MinimalTuple))

MAXALIGN(sizeof(MinimalTuple)) is 8 bytes on a 64-bit machine. And the pointer lives before the tuple:

c
/*
 * Calculate the address of the "next" pointer from the MinimalTuple
 */
#define MemoizeNextTupleAddress(t) \
	((MinimalTuple *) ((char *) (t) - MEMOIZE_NEXT_TUPLE_EXTRA_BYTES))

ExecCopySlotMinimalTupleExtra() allocates the tuple with that much extra space, already zeroed:

c
/*
 * ExecCopySlotMinimalTupleExtra - return MinimalTuple allocated in caller's
 * context, with extra bytes (maxaligned and zeroed) before the tuple for data
 * the caller wishes to store along with the tuple (without requiring the
 * caller to make an additional allocation).
 */
static inline MinimalTuple
ExecCopySlotMinimalTupleExtra(TupleTableSlot *slot, Size extra)
{
	return slot->tts_ops->copy_minimal_tuple(slot, extra);
}

And the cache store path:

c
	/*
	 * Form a MinimalTuple with extra space to store a "next" pointer so that
	 * we can form a singly linked list of tuples belonging to this
	 * MemoizeEntry.
	 */
	mintuple = ExecCopySlotMinimalTupleExtra(slot,
											 MEMOIZE_NEXT_TUPLE_EXTRA_BYTES);

	/*
	 * No need to use MemoizeSetNextTuple to point the next tuple to NULL as
	 * ExecCopySlotMinimalTupleExtra zeros the extra bytes.
	 */

The commit message quantifies the win:

This uses 16 fewer bytes per tuple stored in each MemoizeEntry. 24 bytes can be saved by not doing palloc_object(MemoizeTuple), and instead we can store the pointer to the next tuple by requesting that ExecCopySlotMinimalTupleExtra() allocate an extra 8 bytes per MinimalTuple, a net saving of 16 bytes per cached tuple, which equates to 33% less memory in the most extreme case (assuming very few entries with many tuples and the narrowest possible MinimalTuple).

Saving memory in Memoize allows more entries to exist concurrently and reduces the chances that an entry will be evicted from the cache due to memory pressure. That increases the chances of getting cache hits.

The accounting macro follows:

c
/* Helper macros for memory accounting */
#define EMPTY_ENTRY_MEMORY_BYTES(e)		(sizeof(MemoizeEntry) + \
										 sizeof(MemoizeKey) + \
										 (e)->key->params->t_len)
#define CACHE_TUPLE_BYTES(t)			((t)->t_len + \
										 MEMOIZE_NEXT_TUPLE_EXTRA_BYTES)

This is a micro-optimisation with a macro-level payoff: 16 bytes out of a typical tuple’s ~48 bytes of overhead is a third of the per-tuple memory, and memory per cached tuple is the cache’s capacity.

The JIT has its own copy

One more wrinkle. PostgreSQL’s JIT compiler (src/backend/jit/llvm/) compiles the same step list, but it is a completely separate implementation with its own switch over the same opcodes. So every change to the opcode set has to be made twice.

The strict-function specialisation had to be mirrored:

c
			case EEOP_FUNCEXPR:
			case EEOP_FUNCEXPR_STRICT:
			case EEOP_FUNCEXPR_STRICT_1:
			case EEOP_FUNCEXPR_STRICT_2:
				{
					FunctionCallInfo fcinfo = op->d.func.fcinfo_data;
					LLVMValueRef v_fcinfo_isnull;
					LLVMValueRef v_retval;

					if (opcode == EEOP_FUNCEXPR_STRICT ||
						opcode == EEOP_FUNCEXPR_STRICT_1 ||
						opcode == EEOP_FUNCEXPR_STRICT_2)
					{

and the hashing opcodes likewise:

c
			case EEOP_HASHDATUM_SET_INITVAL:
				{
					LLVMValueRef v_initvalue;

					v_initvalue = l_datum_const(op->d.hashdatum_initvalue.init_value);

					LLVMBuildStore(b, v_initvalue, v_resvaluep);
					LLVMBuildStore(b, l_sbool_const(0), v_resnullp);
					LLVMBuildBr(b, opblocks[opno + 1]);
					break;
				}

			case EEOP_HASHDATUM_FIRST:
			case EEOP_HASHDATUM_FIRST_STRICT:
			case EEOP_HASHDATUM_NEXT32:
			case EEOP_HASHDATUM_NEXT32_STRICT:

Note that the JIT has to be careful about when it rotates the running hash, and it differs deliberately between strict and non-strict mode:

c
					/*
					 * When performing the next hash and not in strict mode we
					 * perform a rotation of the previously stored hash value
					 * before doing the NULL check.  We want to do this even
					 * when we receive a NULL Datum to hash.  In strict mode,
					 * we do this after the NULL check so as not to waste the
					 * effort of rotating the bits when we're going to throw
					 * away the hash value and return NULL.
					 */

This is the same subtlety that produced the NULL-key bug, and the JIT had to be written with the same care.

The JIT also changes which fast path applies. ExecReadyExpr() is the single entry point:

c
static void
ExecReadyExpr(ExprState *state)
{
	if (jit_compile_expr(state))
		return;

	ExecReadyInterpretedExpr(state);
}

If the JIT compiled the expression, ExecReadyInterpretedExpr() is never called, so the ExecJust* fast paths are not installed at all when JIT is active. The JIT’s own code is the fast path in that case — which is why the adf97c15623 commit measured a 26% improvement with JIT enabled versus 20% without.

Caveats

Only very specific opcode shapes are specialised. A hash join on two columns, or on an expression like a + 1, or on a column with a collation-sensitive hash function, falls through to the general interpreter. The commit message is explicit: “This change currently only affects Hash Joins on a single column. Hash Joins with multiple join keys or an expression still run through ExecInterpExpr().”

The strict fast paths only cover 1 and 2 arguments. EEOP_FUNCEXPR_STRICT still loops. And the specialised opcodes are skipped entirely when pgstat_track_functions is enabled, because they bypass the statistics-collecting variants.

The JIT and the interpreter are separate implementations of the same specification. Every opcode change must be made twice, and the two can disagree — as they nearly did around the NULL-key bug.

Specialisation is where bugs live. The op->resnull versus state->resnull distinction is invisible at the level of “return NULL if the key is NULL”, but the difference between writing the intermediate slot and the final slot is the difference between a correct result and a hash table full of rows that can never match.

pfree is not the only thing that got faster. The hash join’s build side also got faster, because ExecBuildHash32Expr() deforms the tuple once to the highest needed attribute rather than once per key.

What it all means in practice

The pattern is “specialise the shape you actually have”. PostgreSQL’s expression evaluation is a general interpreter with a general dispatcher, and it is used for everything from qual evaluation to aggregate transitions to hash keys. Rather than making the interpreter faster in general, the strategy is to notice the handful of shapes that dominate real workloads and hand-write those.

Matching on step count first is a cheap filter that pays for itself. steps_len == 5 narrows the space before any opcode is examined, and the check happens exactly once per expression.

The specialised functions are not “clever”. They are straight-line code that a compiler can see through — no dispatch, no goto, no loop over nargs. The win comes from removing the interpreter’s per-step overhead, not from doing anything clever.

The virtual-slot trick is about knowing what you don’t have to do. A virtual slot has no tuple to deform, so the deformation step is not emitted, so the fast path does not perform it. Absence of a step is the signal.

Memory per cached entry is cache capacity. Saving 16 bytes per Memoize tuple is not a micro-optimisation; in a cache bounded by work_mem, it directly translates into more entries surviving, fewer evictions, and more cache hits.

A 10% win on a probe-dominated hash join is worth a lot. The commit measured “up to 10% query performance increases on recent AMD hardware” for a workload with many probes against a small hash table. That is the shape of workload where per-probe overhead is the whole cost.