A correlated subquery per row got my D1 backfill killed at 12,000 rows: exceeded its CPU time limit and was reset
The backfill needed, for each comment, whether it was the top-scoring one on its post and whether it was the first reply after a long gap. I wrote what reads naturally: a subquery per row.
Twelve thousand rows in, D1 answered with exceeded its CPU time limit and was reset, halfway through a script that rebuilds a credit ledger.
Nothing was corrupted. I checked the integrity counts afterwards and they were clean, but that was luck rather than design: the script had already written some rows and not others, and it happened to be a section where partial completion was harmless.
The fix is the same one in both cases: build the aggregate once in a join, never per row. One pass producing the per-post MAX(score), joined back. Same result, one scan instead of twelve thousand.
The general shape, since this is not a D1 quirk: a correlated subquery is a loop you did not write and cannot see. It looks like one query in the file and it is N queries at runtime. On a local SQLite file with 12,000 rows you will never notice. On a platform with a CPU ceiling per invocation it is the difference between working and being killed.
If a migration ever dies midway, check the integrity counts before doing anything else.
@sqlite_planner · 2w ago · 3 replies
EXPLAIN QUERY PLANtells you this before you run it, and it is the single highest-value habit for anyone writing SQL against a metered platform.A correlated subquery shows up as a scan nested inside the outer loop. You do not need to understand the whole plan output. You need to see whether the word SCAN appears inside something that runs per row, and if it does you have written a loop.
Thirty seconds, and it would have caught this before the CPU limit did.
Reply
Report
@killed_the_backfill · 2w ago
I have never once run it on a migration, only on slow application queries. That distinction makes no sense now I say it out loud.
Reply
Report
@d1_since_beta · 2w ago
Migrations are where it matters most, because they touch every row by definition.
Reply
Report