CONCEPT Cited by 1 source

DELETE-LIMIT asymptote¶

The DELETE-LIMIT asymptote is a latency-degradation shape seen on recurring DELETE … WHERE <predicate> LIMIT <N> jobs on an unindexed predicate. As successive batches delete matching rows, the pool of matches shrinks; finding the next N matches approaches a full table scan, and per-execution latency degrades from milliseconds to minutes within a single batch run.

(Source: sources/2026-04-21-planetscale-query-performance-analysis-with-insights.)

Mechanism¶

The LIMIT N clause on a DELETE is commonly used to cap the work a single transaction does — "the limit is added to avoid long-running deletes that could slow down other queries" (Hazen 2023). The database scans the table in whatever order its plan chooses, evaluates the predicate on each row, and stops after the first N matches.

Early in the run: many rows match the predicate; the scan finds N matches after scanning N / match-rate rows. On a 100M-row table where 1% of rows match, that's ~500/0.01 = 50k rows scanned per 500-row DELETE — fast.
Late in the run: most matches are already deleted; the scan must examine a large fraction of the remaining table before finding the next N matches. At the asymptote, every DELETE approximates a full table scan to find the last handful of matches.

Shape on a time-series graph¶

PlanetScale's worked example: hourly DELETE job kicked off at 10 past the hour, 100M-row table, LIMIT 500.

Window	Mean / p95 latency
10–20 past hour (early matches)	"a few hundred milliseconds"
20–30 past hour (depleting matches)	"almost 15 minutes"

Reported headline (averaged across the hour): "approximately 8 seconds to run on average."

The per-pattern time-series graph shows a sawtooth — each hourly period starts fast, asymptotes to full-table- scan latency, then resets when the job finishes and new matches accumulate over the next hour.

Fix: index the predicate column¶

Hazen's fix: add an index on the minute column used in the WHERE clause. "Adding an index to the minute column lets the database quickly identify and delete the rows that match the where clause."

Post-fix latency: "consistently under a few hundred milliseconds" — "so dramatically that they're not even visibly discernible from zero on the graph."

The index turns the per-batch work from O(table-size) to O(N) — the scan is replaced by an index seek that finds N matches in O(N) time regardless of how many matches remain.

Why LIMIT doesn't help unindexed DELETEs¶

The LIMIT is a cap on matches returned, not a cap on rows scanned. Without an index, the database has no way to locate matches without scanning — LIMIT 500 bounds the output, not the work. The sweet spot intended by operators ("delete in small chunks to avoid long transactions") is only reached when there's an index directing the scan to matching rows directly.

Seen in¶

sources/2026-04-21-planetscale-query-performance-analysis-with-insights — canonical wiki disclosure. Hazen's worked example is the pedagogical anchor: 100M-row table, hourly DELETE ... LIMIT 500, sawtooth latency shape on the per-pattern graph, fix via CREATE INDEX. Shipped via PlanetScale's 505th deploy request. This is the canonical production instance of the antipattern on the wiki.