[PATCH v1] Stale row estimates for transition tables

Hi,

Static SQL in a PL/pgSQL statement trigger can retain the transition-table
row counts from its first execution. If that execution affects no rows,
a later bulk update can reuse a nested-loop plan with one-row estimates
for both transition tables.

Here is a price-audit example. Run it in a fresh session as superuser,
with auto_explain installed:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_nested_statements = on;
SET auto_explain.log_analyze = on;
SET auto_explain.log_timing = off;
SET auto_explain.log_level = notice;
SET jit = off;

CREATE TEMP TABLE products (
id integer PRIMARY KEY, price_cents integer NOT NULL
);
CREATE TEMP TABLE price_audit (
product_id integer NOT NULL,
old_price_cents integer NOT NULL,
new_price_cents integer NOT NULL,
changed_at timestamptz NOT NULL DEFAULT clock_timestamp()
);
INSERT INTO products SELECT id, 10000 FROM generate_series(1, 2000) AS id;

CREATE FUNCTION pg_temp.audit_price_change()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO price_audit (product_id, old_price_cents, new_price_cents)
SELECT n.id, o.price_cents, n.price_cents
FROM new_prices n JOIN old_prices o USING (id)
WHERE n.price_cents IS DISTINCT FROM o.price_cents;
RETURN NULL;
END;
$$;
CREATE TRIGGER audit_price_change
AFTER UPDATE ON products
REFERENCING OLD TABLE AS old_prices NEW TABLE AS new_prices
FOR EACH STATEMENT EXECUTE FUNCTION pg_temp.audit_price_change();

-- Prepare the trigger's SQL with empty transition tables.
UPDATE products SET price_cents = price_cents + 1 WHERE false;
UPDATE products SET price_cents = price_cents + 1;
SELECT count(*) FROM price_audit; -- 2000 on both builds

On the unpatched build, the audit INSERT took 623.549 ms. Plan excerpts
below omit costs and widths:

Nested Loop (rows=1) (actual rows=2000.00 loops=1)
Join Filter: ((n.price_cents IS DISTINCT FROM o.price_cents) AND (n.id
= o.id))
Rows Removed by Join Filter: 3998000
-> Named Tuplestore Scan (rows=1) (actual rows=2000.00 loops=1)
-> Named Tuplestore Scan (rows=1) (actual rows=2000.00 loops=2000)

With the patch, the audit INSERT took 3.663 ms:

Merge Join (rows=19900) (actual rows=2000.00 loops=1)
Merge Cond: (n.id = o.id)
Join Filter: (n.price_cents IS DISTINCT FROM o.price_cents)
-> Sort (actual rows=2000.00 loops=1)
Sort Key: n.id
-> Named Tuplestore Scan (rows=2000) (actual rows=2000.00 loops=1)
-> Sort (actual rows=2000.00 loops=1)
Sort Key: o.id
-> Named Tuplestore Scan (rows=2000) (actual rows=2000.00 loops=1)

The stale estimates cause 2,000 scans of the inner transition table and
four million row-pair comparisons. With current row counts, the planner
chooses a merge join and scans each transition table once.

In a separate run with auto_explain disabled, six bulk UPDATEs after the
empty UPDATE took 3644.311 ms unpatched and 98.725 ms patched. Their nested
audit INSERTs took 3558.592 ms and 19.184 ms respectively. The patched
queries were planned six times, taking 0.743 ms in total according to
pg_stat_statements; this excludes analysis and rewriting. Both runs
produced the expected 12,000 audit rows. These are single-run results on
master at 1b5dd3a24a4 (PostgreSQL 20devel), using a debug/-O0 build on
x86_64 Linux, with JIT and autovacuum disabled.

SPI_register_trigger_data() supplies the current row counts, but analysis
copies enrtuples into the RTE. Subsequent plans read that cached value.
Replanning alone therefore cannot refresh the estimate. Thomas Munro
noted this possibility in 2017 [1].

The attached patch records whether a cached query references a named
tuplestore. For sources with a raw parse tree, RevalidateCachedQuery()
then uses the existing invalidation path to repeat analysis, rewriting
and planning with the current QueryEnvironment. This also covers ENRs
registered by other SPI clients, and replans even when the row count has
not changed. One-shot and pre-analyzed-only sources keep their existing
behavior.

Does this seem like a reasonable policy for cached ENR queries? I would
also appreciate opinions on applying it to other SPI clients rather than
restricting it to transition tables.

[1]
https://www.postgresql.org/message-id/CAEepm=3mUYhQq4yoiYa=WjFvvUcCVA3H0ZNjR_QVMTkn0Camwg@mail.gmail.com

--
Best Regards,
Teletele