symfonic.memory.backends.postgres_index_probes

postgres_index_probes

Pin claimed-lifecycle statements to index probes inside their own transaction.

An index alone does not bound a statement: given a small or stale-statistics table, a custom plan may still scan it sequentially, or build a hash over a whole tenant or owner scope, which costs the history rather than the rows touched. Inside this block sequential scans, hash joins and merge joins are disabled, so every statement whose input rows can reach an index joins them by a nested loop of index probes, under custom and generic plans alike. The settings are transaction-local and restored on exit, as the native deadline restores statement_timeout.

index_probes async

index_probes(conn: Any, *, ordered: bool = False, presorted: bool = False)

Run the enclosed statements of an open transaction as index probes.

ordered also disables bitmap scans for a range read stopped by LIMIT: a bitmap heap scan reads the whole qualifying range before its sort, so interleaved tenants make the planner trade the bound for heap locality. presorted also disables explicit sorts, for a range read whose ORDER BY its own index provides: otherwise a custom plan may read the whole tenant through another tenant-prefixed index, filter the range and sort it under the LIMIT. A sort no index can avoid still runs, but PostgreSQL 16 prices it with disable_cost, which would push a small read past jit_above_cost and JIT-compile it on every call; presorted therefore disables JIT too.

Source code in src/symfonic/memory/backends/postgres_index_probes.py

@asynccontextmanager
async def index_probes(conn: Any, *, ordered: bool = False, presorted: bool = False):
    """Run the enclosed statements of an open transaction as index probes.

    ``ordered`` also disables bitmap scans for a range read stopped by LIMIT:
    a bitmap heap scan reads the whole qualifying range before its sort, so
    interleaved tenants make the planner trade the bound for heap locality.
    ``presorted`` also disables explicit sorts, for a range read whose ORDER BY
    its own index provides: otherwise a custom plan may read the whole tenant
    through another tenant-prefixed index, filter the range and sort it under
    the LIMIT. A sort no index can avoid still runs, but PostgreSQL 16 prices
    it with disable_cost, which would push a small read past jit_above_cost
    and JIT-compile it on every call; ``presorted`` therefore disables JIT too.
    """
    # pg_settings is materialized before set_config runs, so each row still
    # carries the caller's value.
    previous = await conn.fetchval(
        "SELECT json_object_agg(name, setting) FROM (SELECT name, setting, "
        "set_config(name, 'off', true) FROM pg_settings WHERE name=ANY($1::text[])) s",
        [*_PINNED, *(("enable_bitmapscan",) if ordered else ()),
         *(("enable_sort", "jit") if presorted else ())],
    )
    yield
    await conn.execute(
        "SELECT set_config(key, value, true) FROM json_each_text($1::json)", previous,
    )