The query behind the bell's unread count reads every moderation case instead of looking them up, and it runs on every page load for every member. Since page owners started hearing about takedowns on their pages, the condition is "the case is mine, or it belongs to a page I own", and Postgres turns the second half into a hashed subplan inside the OR, which costs it the index: measured on a 200,000-row copy, a parallel sequential scan at 18–26 ms against 0.04 ms for the old lookup. Today the table holds zero rows in production, so the scan is free and nobody notices.
Nobody notices yet; whoever operates the installation notices on the day moderation cases become common, as a site that got slower everywhere at once for no visible reason. Resolving the page ids to a plain list first restores an index plan (0.5–2 ms measured), and that form was built and rejected during #2120 for a reason worth keeping: it breaks two invariants. notification_filter_coverage_test.exs walks the notification registry with no database checked out, and the unread count is pinned to two queries, which the shipped list form turns into four — two extra round trips on every page load, which is a worse trade than a scan over an empty table.
So the fix is not the list form as written. It is to hand the page ids in from the caller that already executes a query, leaving the registry pure. The trigger for doing it is the row count, not a calendar.
Where: Vutuv.Activity's count_moderation/2, Vutuv.Moderation.told_about/2
An AI agent wrote this text in my name. I know that is problematic.
The query behind the bell's unread count reads every moderation case instead of looking them up, and it runs on every page load for every member. Since page owners started hearing about takedowns on their pages, the condition is "the case is mine, or it belongs to a page I own", and Postgres turns the second half into a hashed subplan inside the
OR, which costs it the index: measured on a 200,000-row copy, a parallel sequential scan at 18–26 ms against 0.04 ms for the old lookup. Today the table holds zero rows in production, so the scan is free and nobody notices.Nobody notices yet; whoever operates the installation notices on the day moderation cases become common, as a site that got slower everywhere at once for no visible reason. Resolving the page ids to a plain list first restores an index plan (0.5–2 ms measured), and that form was built and rejected during #2120 for a reason worth keeping: it breaks two invariants.
notification_filter_coverage_test.exswalks the notification registry with no database checked out, and the unread count is pinned to two queries, which the shipped list form turns into four — two extra round trips on every page load, which is a worse trade than a scan over an empty table.So the fix is not the list form as written. It is to hand the page ids in from the caller that already executes a query, leaving the registry pure. The trigger for doing it is the row count, not a calendar.
Where:
Vutuv.Activity'scount_moderation/2,Vutuv.Moderation.told_about/2An AI agent wrote this text in my name. I know that is problematic.