Postgres Planner Crossover

Find the ROW COUNT AT WHICH YOUR QUERY SILENTLY STOPS USING ITS INDEX.

THE SYMPTOM. A query that was instant for a year is now slow. Nothing changed: no deploy, no code change, no schema migration, no index dropped. It got slower gradually, and then suddenly. EXPLAIN now shows a Seq Scan where it used to show an Index Scan or Bitmap Heap Scan. The query is still CORRECT -- it just reads the whole table now.

If an agent wrote the query there is often no human mental model of why this happened at all: the WHERE clause has not changed, so surely the plan should not either.

THE MECHANISM. PostgreSQL does not choose an index scan because an index exists. For every candidate plan it estimates a cost using a published formula, and picks the cheapest. As a table grows, the estimated cost of the index path and the sequential path both rise -- at different rates. There is a row count where the two lines CROSS, and past it the planner correctly prefers the scan. Nothing warns you when you pass it, because nothing went wrong.

THIS COMPUTES THE CROSSOVER from the same published cost parameters the planner uses, so you learn the row count BEFORE you reach it -- and can project it onto a calendar from your own growth rate. A number you can put in an alert is worth more than a plan you can read after the fact.

It also distinguishes a crossover you have genuinely passed from one caused by a bad row estimate, because the remedies are completely different and the EXPLAIN output looks similar.

VERIFIED: 96 tests, measured by running the suite.

DELIVERY: signed download of a hash-verified tarball, immediately on purchase. Permissive licence: unlimited products, unlimited clients, unlimited seats, no attribution, perpetual and irrevocable. One restriction, do not republish the source as source.

Interface

What you call, and what comes back. Types and signatures only — the implementation ships with the source.

  export function findCrossover(input: FindCrossoverInput): CrossoverResult | NotDeterminable;
  export function projectCrossoverToCalendar(crossoverRows: number, growthOverTime: GrowthOverTimePoint[]): CalendarProjection | NotDeterminable;
  export function findPlanNode(explainJson: unknown, nodeTypes: readonly string[]): ExtractedPlanNode | null;
  export function indexPagesFetched(tuplesFetched: number, tablePages: number, totalCompetingPages: number, effectiveCacheSizePages: number): number;
  export function indexScanCostAtRows(calibration: IndexScanCalibration, rows: number, matchedRows: number): number;
  export function seqScanCostAtRows(calibration: SeqScanCalibration, seqPageCost: number, rows: number): number;
  export function isNotDeterminable<T>(r: Checked<T>): r is NotDeterminable;
  export function mergeFailures(results: Checked<unknown>[]): NotDeterminable | null;
  export function validateCostConstants(input: Partial<CostConstants> | undefined | null):;
  export function validateCurrentTableStats( input: Partial<CurrentTableStats> | undefined | null, ):;
  export function validateObservedPlanCosts( input: Partial<ObservedPlanCosts> | undefined | null, ):;
  export function validateSelectivityModel(input: SelectivityGrowthModel | undefined | null):;

  export type Result<T> = (T & { determinable: true }) | NotDeterminable;
  export type Checked<T> = { ok: true;