Every artwork request scans the whole entries table: no index on entries.image #272
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
/api/art checks that a feed or item names the image (Db::names_image) before anything else, cache hits included. entries.image has no index, so that is a Parallel Seq Scan of entries on every image: EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM entries WHERE image = '' took 571 ms on production on 2026-10-17. /api/art's p95 since 2026-10-16 22:00 UTC is 1,321 ms, the slowest route by far:
A page showing 50 images makes 50 such scans at once (trace 917fcab7de3b1107d17b4c59708f97ba: 796 ms for a 404).
Still the slowest route after the 15:28 UTC deploy on 2026-10-17: /api/art p95 is 1,495 ms, and its 404s take 900-1,640 ms each. Every other route p95 is under 265 ms.
Seen again on 2026-10-18 at 00:40:43 UTC: five /api/art requests from the iPhone at once, each a 404 after 1027-1095 ms (trace e34a131405c4b026f8df015c5aac77a0, all in ipx's own http span, no child spans). A refusal shouldn't take a second; that fits the whole-table lookup before the answer.
Fixed in
21c786f, deployed: /api/art serves a kept image before asking the database (it was only kept once let through), and entries.image has an index, hash on Postgres since a btree refuses keys over about 2.7 KB. On production the lookup is now an Index Scan using entries_by_image, 0.13 ms, where the sequential scan took 571 ms.