MIME-Version: 1.0
References: <CA+HiwqF4C0ws3cO+z5cLkPuvwnAwkSp7sfvgGj3yQ=Li6KNMqA@mail.gmail.com>
 <CA+HiwqGM6nvAV5O+=Nr+BXMPWOma0oeCRVzVP0XiLE8zX5TVAg@mail.gmail.com>
 <CA+HiwqGMaovCUgDbGxVGnK0Mrivr+ph3YE2Ws+47-ugyPb4f7g@mail.gmail.com>
 <CAFj8pRDaiBe_GOLk_yyYHTtPiDAAaLOM8u1-=Q3ZgXBTH+1igg@mail.gmail.com>
 <CA+HiwqGA5Ay_MR0eJEEbt4j6WrVh4F+AasTp8yCbs5aJLOJn6Q@mail.gmail.com>
 <CAEG8a3JM=NoqiTK0V6S9FNxZPvy1+C5F7rfafTtPKBVWnunL-g@mail.gmail.com> <CA+HiwqEyiLCY6MTLbOJXDdLNNQLaURYHvdW797MQgbjEK9od4Q@mail.gmail.com>
In-Reply-To: <CA+HiwqEyiLCY6MTLbOJXDdLNNQLaURYHvdW797MQgbjEK9od4Q@mail.gmail.com>
From: Junwang Zhao <zhjwpku@gmail.com>
Date: Mon, 23 Feb 2026 21:45:39 +0800
Message-ID: <CAEG8a3+VBpwPf1Rm-ECD90whM9b3YnGhux5CVXdsL6khiBfzRQ@mail.gmail.com>
Subject: Re: Eliminating SPI / SQL from some RI triggers - take 3
To: Amit Langote <amitlangote09@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>, 
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAEG8a3%2BVBpwPf1Rm-ECD90whM9b3YnGhux5CVXdsL6khiBfzRQ%40mail.gmail.com>
Precedence: bulk

Hi Amit,

On Thu, Feb 19, 2026 at 5:21=E2=80=AFPM Amit Langote <amitlangote09@gmail.c=
om> wrote:
>
> Hi Junwang,
>
> On Mon, Dec 1, 2025 at 3:09=E2=80=AFPM Junwang Zhao <zhjwpku@gmail.com> w=
rote:
> > As Amit has already stated, we are approaching a hybrid "fast-path + fa=
llback"
> > design.
> >
> > 0001 adds a fast path optimization for foreign key constraint checks
> > that bypasses the SPI executor, the fast path applies when the referenc=
ed
> > table is not partitioned, and the constraint does not involve temporal
> > semantics.
> >
> > With the following test:
> >
> > create table pk (a numeric primary key);
> > create table fk (a bigint references pk);
> > insert into pk select generate_series(1, 2000000);
> >
> > head:
> >
> > [local] zhjwpku@postgres:5432-90419=3D# insert into fk select
> > generate_series(1, 2000000, 2);
> > INSERT 0 1000000
> > Time: 13516.177 ms (00:13.516)
> >
> > [local] zhjwpku@postgres:5432-90419=3D# update fk set a =3D a + 1;
> > UPDATE 1000000
> > Time: 15057.638 ms (00:15.058)
> >
> > patched:
> >
> > [local] zhjwpku@postgres:5432-98673=3D# insert into fk select
> > generate_series(1, 2000000, 2);
> > INSERT 0 1000000
> > Time: 8248.777 ms (00:08.249)
> >
> > [local] zhjwpku@postgres:5432-98673=3D# update fk set a =3D a + 1;
> > UPDATE 1000000
> > Time: 10117.002 ms (00:10.117)
> >
> > 0002 cache fast-path metadata used by the index probe, at the current
> > time only comparison operator hash entries, operator function OIDs
> > and strategy numbers and subtypes for index scans. But this cache
> > doesn't buy any performance improvement.
> >
> > Caching additional metadata should improve performance for foreign key =
checks.
> >
> > Amit suggested introducing a mechanism for ri_triggers.c to register a
> > cleanup callback in the EState, which AfterTriggerEndQuery() could then
> > invoke to release per-statement cached metadata (such as the IndexScanD=
esc).
> > However, I haven't been able to implement this mechanism yet.
>
> Thanks for working on this.  I've taken your patches as a starting
> point and reworked the series into two patches (attached): 1st is your
> 0001+0002 as the core patch that adds a gated fast-path alternative to
> SPI and 2nd where I added per-statement resource caching.  Doing the
> latter turned out to be not so hard thanks to the structure you chose
> to build the core fast path.  Good call on adding the RLS and ACL test
> cases, btw.
>
> So, 0001 is a functionally complete fast path: concurrency handling,
> REPEATABLE READ crosscheck, cross-type operators, security context,
> and metadata caching. 0002 implements the per-statement resource
> caching we discussed, though instead of sharing the EState between
> trigger.c and ri_triggers.c it uses a new AfterTriggerBatchCallback
> mechanism that fires at the end of each trigger-firing cycle
> (per-statement for immediate constraints, or until COMMIT for deferred
> ones). It layers resource caching on top so that the PK relation,
> index, scan descriptor, and snapshot stay open across all FK trigger
> invocations within a single trigger-firing cycle rather than being
> opened and closed per row.
>
> Note that phe previous 0002 (metadata caching) is folded into 0001,
> and most of the new fast-path logic added in 0001 now lives in
> ri_FastPathCheck() rather than inline in RI_FKey_check(), so the
> RI_FKey_check diff is just the gating call and SPI fallback.
>
> I re-ran the benchmarks (same test as yours, different machine):
>
> create table pk (a numeric primary key);
> create table fk (a bigint references pk);
> insert into pk select generate_series(1, 2000000);
> insert into fk select generate_series(1, 2000000, 2);
>
> master: 2444 ms  (median of 3 runs)
> 0001: 1382 ms  (43% faster)
> 0001+0002: 1202 ms  (51% faster, 13% over 0001 alone)

I can get similar improvement on my old mac intel chip:

master: 12963.993 ms
0001: 6641.692 ms, 48.8% faster
0001+0002: 5771.703 ms, 55.5% faster

>
> Also, with int PK / int FK (1M rows):
>
> create table pk (a int primary key);
> create table fk (a int references pk);
> insert into pk select generate_series(1, 1000000);
> insert into fk select generate_series(1, 1000000);
>
> master: 1000 ms
> 0001: 520 ms  (48% faster)
> 0001+0002: 432 ms  (57% faster, 17% over 0001 alone)

master: 11134.583 ms
0001: 5240.298 ms, 52.9% faster
0001+0002: 4554.215 ms, 59.1% faster

>
> The incremental gain from 0002 comes from eliminating per-row relation
> open/close, scan begin/end, slot alloc/free, and replacing per-row
> GetSnapshotData() with only curcid adjustment on the registered
> snapshot copy in the cache.
>
> The two current limitations are partitioned referenced tables and
> temporal foreign keys. Partitioned PKs are relatively uncommon in
> practice, so the non-partitioned case should cover most FK workloads,
> so I'm not sure it's worth the added complexity to support them.
> Temporal FKs are inherently multi-row, so they're a poor fit for a
> single-probe fast path.
>
> David Rowley mentioned off-list that it might be worth batching
> multiple FK values into a single index probe, leveraging the
> ScalarArrayOp btree improvements from PostgreSQL 17. The idea would be
> to buffer FK values across trigger invocations in the per-constraint
> cache (0002 already has the right structure for this), build a
> SK_SEARCHARRAY scan key, and let the btree AM walk the matching leaf
> pages in one sorted traversal instead of one tree descent per row. The
> locking and recheck would still be per-tuple, but the index traversal
> cost drops significantly. Single-column FKs are the obvious starting
> point. That seems worth exploring but can be done as a separate patch
> on top of this.

I will take a look at this in the following weeks.

>
> I think the series is in reasonable shape but would appreciate extra
> eyeballs, especially on the concurrency handling in ri_LockPKTuple()
> in 0001 and the snapshot lifecycle in 0002. Or anything else that
> catches one's eye.
>
> --
> Thanks, Amit Langote

I don't have any additional comments on the patch except one minor nit,
maybe merge the following two if conditions into one, not a strong opinion
though.

if (use_cache)
{
/*
* The snapshot was registered once when the cache entry was created.
* We just patch curcid to reflect the new command counter.
* SnapshotSetCommandId() only patches process-global statics, not
* registered copies, so we do it directly.
*
* The xmin/xmax/xip fields don't need refreshing: within a single
* statement batch, only curcid changes between rows.
*/
Assert(fpentry && fpentry->snapshot !=3D NULL);
snapshot =3D fpentry->snapshot;
snapshot->curcid =3D GetCurrentCommandId(false);
}
else
snapshot =3D RegisterSnapshot(GetLatestSnapshot());

if (use_cache)
{
pk_rel =3D fpentry->pk_rel;
idx_rel =3D fpentry->idx_rel;
scandesc =3D fpentry->scandesc;
slot =3D fpentry->slot;
}
else
{
pk_rel =3D table_open(riinfo->pk_relid, RowShareLock);
idx_rel =3D index_open(riinfo->conindid, AccessShareLock);
scandesc =3D index_beginscan(pk_rel, idx_rel,
snapshot, NULL,
riinfo->nkeys, 0);
slot =3D table_slot_create(pk_rel, NULL);
}

--=20
Regards
Junwang Zhao