postgres-github.git / summary / log / commit / refs

commit    b7b27eb41a5cc0b45a1a9ce5c1cde5883d7bc358
Author:   Amit Langote <amitlan@postgresql.org>
Date:     Fri Apr 03 05:33:53 2026 +0000

    Optimize fast-path FK checks with batched index probes
    
    Instead of probing the PK index on each trigger invocation, buffer
    FK rows in a new per-constraint cache entry (RI_FastPathEntry) and
    flush them as a batch.
    
    On each trigger invocation, the new ri_FastPathBatchAdd() buffers
    the FK row in RI_FastPathEntry.  When the buffer fills (64 rows)
    or the trigger-firing cycle ends, the new ri_FastPathBatchFlush()
    probes the index for all buffered rows, sharing a single
    CommandCounterIncrement, snapshot, permission check, and security
    context switch across the batch, rather than repeating each per row
    as the SPI path does.  Per-flush CCI is safe because all AFTER
    triggers for the buffered rows have already fired by flush time.
    
    For single-column foreign keys, the new ri_FastPathFlushArray()
    builds an ArrayType from the buffered FK values (casting to the
    PK-side type if needed) and constructs a scan key with the
    SK_SEARCHARRAY flag.  The index AM sorts and deduplicates the array
    internally, then walks matching leaf pages in one ordered traversal
    instead of descending from the root once per row.  A matched[] bitmap
    tracks which batch items were satisfied; the first unmatched item is
    reported as a violation.  Multi-column foreign keys fall back to
    per-row probing via the new ri_FastPathFlushLoop().
    
    The fast path introduced in the previous commit (2da86c1ef9) yields
    ~1.8x speedup.  This commit adds ~1.6x on top of that, for a combined
    ~2.9x speedup over the unpatched code (int PK / int FK, 1M rows, PK
    table and index cached in memory).
    
    FK tuples are materialized via ExecCopySlotHeapTuple() into a new
    purpose-specific memory context (flush_cxt), child of
    TopTransactionContext, which is also used for per-flush transient
    work: cast results, the search array, and index scan allocations.
    It is reset after each flush and deleted in teardown.
    
    The PK relation, index, tuple slots, and fast-path metadata are
    cached in RI_FastPathEntry across trigger invocations within a
    trigger-firing batch, avoiding repeated open/close overhead.  The
    snapshot and IndexScanDesc are taken fresh per flush.  The entry is
    not subject to cache invalidation: cached relations are held with
    locks for the transaction duration, and the entry's lifetime is
    bounded by the trigger-firing cycle.
    
    Lifecycle management for RI_FastPathEntry relies on three new
    mechanisms:
    
      - AfterTriggerBatchCallback: A new general-purpose callback
        mechanism in trigger.c.  Callbacks registered via
        RegisterAfterTriggerBatchCallback() fire at the end of each
        trigger-firing batch (AfterTriggerEndQuery for immediate
        constraints, AfterTriggerFireDeferred at COMMIT, and
        AfterTriggerSetState for SET CONSTRAINTS IMMEDIATE).  The RI
        code registers ri_FastPathEndBatch as a batch callback.
    
      - Batch callbacks only fire at the outermost query level
        (checked inside FireAfterTriggerBatchCallbacks), so nested
        queries from SPI inside other AFTER triggers do not tear down
        the cache mid-batch.
    
      - XactCallback: ri_FastPathXactCallback NULLs the static cache
        pointer at transaction end, handling the abort path where the
        batch callback never fired.
    
      - SubXactCallback: ri_FastPathSubXactCallback NULLs the static
        cache pointer on subtransaction abort, preventing the batch
        callback from accessing already-released resources.
    
      - AfterTriggerBatchIsActive(): A new exported accessor that
        returns true when afterTriggers.query_depth >= 0.  During
        ALTER TABLE ... ADD FOREIGN KEY validation, RI triggers are
        called directly outside the after-trigger framework, so batch
        callbacks would never fire.  The fast-path code uses this to
        fall back to the non-cached per-invocation path in that
        context.
    
    ri_FastPathEndBatch() flushes any partial batch before tearing
    down cached resources.  Since the FK relation may already be
    closed by flush time (e.g. for deferred constraints at COMMIT),
    it reopens the relation using entry->fk_relid if needed.
    
    The existing ALTER TABLE validation path bypasses batching and
    continues to call ri_FastPathCheck() directly per row, because
    RI triggers are called outside the after-trigger framework there
    and batch callbacks would never fire to flush the buffer.
    
    Suggested-by: David Rowley <dgrowleyml@gmail.com>
    Author: Amit Langote <amitlangote09@gmail.com>
    Co-authored-by: Junwang Zhao <zhjwpku@gmail.com>
    Reviewed-by: Haibo Yan <tristan.yim@gmail.com>
    Reviewed-by: Chao Li <li.evan.chao@gmail.com>
    Tested-by: Tomas Vondra <tomas@vondra.me>
    Discussion: https://postgr.es/m/CA+HiwqF4C0ws3cO+z5cLkPuvwnAwkSp7sfvgGj3yQ=Li6KNMqA@mail.gmail.com

[parent: be21341e13d9]