Unread counts still hash every read-state row a person has #352
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?
Found measuring #347 after
a11e3b6(2026-10-23). All Subscriptions' unread count for user 2 now reads 213 items, but 10-12 ms of its 19 ms is a hash of all 22,949 of their entry_state rows: Postgres estimates the join on e.first_seen >= coalesce(w.unread_from, 0) at about a third of the feed's items (17,000 rows, where it is 200), so it hashes entry_state instead of probing its primary key per item. A NOT EXISTS form gives the same plan (19 ms); a correlated scalar lookup is worse (51 ms). It still grows with what someone has read. Ways on: an estimate Postgres can use (the watermark as a constant per feed, or a per-feed query), or dropping read rows below the watermark once every reader of read state honours it.Fixed in
e1291c1: unread counts run as a subquery per subscription, where the feed and its unread_from are constants, so Postgres looks up the few items since and their read state by key instead of hashing every read-state row. Used for the feed list's unread counts and count_of(Unread) without a search. EXPLAIN ANALYZE on production (user 2): feed list unread 19.7 -> 14.8 ms, All Subscriptions' unread count 19 -> 13 ms. Deployed; the unread count (44) matches the list, and the feed list's counts over the feeds All Subscriptions shows add up to the same 44. A page of unread items still uses the join, since it needs ordered rows.