Repository navigation
Typed materialization runs for hours once its statements switch to a generic plan #1298
Description
On our deployment a materialize_typed job has been running its source cleanup (step 1 of materialize_in_tx, the DELETE FROM typed_fact_sources … NOT EXISTS (…)) for over three hours on a base of about 438,000 facts and 200,000 typed-fact sources, holding the database at its CPU limit. A second materialize_typed for the same base waits on the advisory lock behind it.
The statement itself is fast. On a copy of that base's tables (same rows, same indexes, work_mem 16MB):
| Plan | Time |
|---|---|
Planned for the base's id (kb_id = '…', or a custom plan of the prepared statement) |
2.2 s |
Generic plan of the prepared statement (plan_cache_mode = force_generic_plan) |
cancelled at 10 min |
sqlx prepares statements, and after a few runs on a connection Postgres switches to a generic plan. The generic plan can't see how many rows t.kb_id = $1 selects. It estimates about four statements on the inner side and picks a nested-loop anti-join over a materialized inner result. That result actually holds about 356,000 rows and is rescanned for each of the base's 200,000 sources. With the id known, it hashes both sides once.
Proposal: the recompute plans every statement for the base it is given, with SET LOCAL plan_cache_mode = force_custom_plan at the start of materialize_in_tx. One statement, scoped to the transaction, so it covers the worker and the human-review entry points and nothing else. The test would check that the recompute's transaction plans that way; a timing test would be flaky.
A query rewrite would make the cleanup robust to any plan, for example adding s.kb_id = $1 (0.8 s on the same copy, since a source shares its fact's base) or storing the normalized phrase instead of computing a regex per fact per run. That is a bigger change I'd rather propose separately.
Happy to change the shape if you'd prefer a different approach. Thank you!
Activity
WaylandYang commentedon Oct 18, 2026
Thank you for the careful diagnosis and the plan comparison. This matches what we've seen before: 0035 records the same five-executions switch for vector search.
A PR is welcome. Two notes on shape:
SET LOCAL plan_cache_mode = force_custom_planworks and stays inside the transaction, but it also replans every per-row statement in step 3's loop on each execution. Marking just the whole-base statements inmaterialize_in_tx(steps 0–3 and the implication/attestation scans) with.persistent(false)gets them planned with the real values every time and leaves the per-row statements cached. I'd lean that way, but either is fine if you note the tradeoff.s.kb_id = $1in step 1 in the same PR. Migration 0070'styped_fact_sources_same_kbtrigger guarantees a source statement is in its fact's base, so the predicate changes no result, and it keeps the cleanup fast if a generic plan ever comes back. Storing the normalised phrase can be its own proposal, as you suggest.For the test, asserting the planning mode (or that those statements aren't cached) rather than timing is right.
Utopia: run 0.1.0-re.13 (typed recompute plans per base, evidence rea…
gedaiu commentedon Oct 18, 2026
Thank you! #1307 does it your way:
.persistent(false)on the eight whole-base statements, with the per-row ones still cached, ands.kb_id = $1in step 1. The test checkspg_prepared_statementson a one-connection pool rather than timing.WaylandYang commentedon Oct 19, 2026
Fixed by #1307, now in
dev. Thank you.