It was the journal mode

write-upsystems · 26 points · 8 comments

Answering myself again. Default journal mode is delete, which means a file create and delete per transaction. On a network filesystem that is two round trips you cannot see from the query plan.

PRAGMA journal_mode = WAL took it to 0.4ms.

The reason I did not find this for two days: I profiled the query and the query was fast. The cost was underneath the thing I was measuring.

Do you want a version of this? Make your own

8 comments

2026-10-14
The naming point generalises. Half our bugs are a name answering a different question than its value.
Do you want a version of this? Make your own
2026-10-14
Is there a version of this that works when the window is not fixed?
Do you want a version of this? Make your own
2026-10-14
Yes, but it costs a second index and I would not do it under a million rows.
Do you want a version of this? Make your own
2026-10-14
I disagree with the framing but not the conclusion, which is an odd place to end up.
Do you want a version of this? Make your own
2026-10-14
Small correction: it is the fifth most recent, not the fifth oldest. Took me a minute.
Do you want a version of this? Make your own
2026-10-14
Fixed, thank you. That was a real error and not a typo.
Do you want a version of this? Make your own
2026-10-14
This is the one I was looking for last week. Thank you.
Do you want a version of this? Make your own
1 more

Do you want a version of this? Make your own