Home timeline query is slow (scans the entire `posts` table)

[ntsklab]

Summary

With ~1.5 million rows in posts, GET /api/v1/timelines/home becomes
noticeably slow. The query appears to read the whole posts table, so
latency grows with the number of stored posts.

Environment

• Hollo 0.9.20 (ghcr.io/fedify-dev/hollo:0.9.20)
• PostgreSQL 18.6 (CloudNativePG)
• ~1.5 million rows in posts
• TIMELINE_INBOXES not set

Symptoms

The timings below are from our environment (a VM, not a fast SSD), so
absolute numbers will differ elsewhere.

• A home timeline request using min_id routinely takes 4–8 s.
• Some requests hit a 10 s client timeout and fail.
• A normal page load (max_id) is also slow (~0.4 s).

Detailed analysis and proposed fix by AI

The three "hide shared posts from muted/blocked accounts" filters in
getTimelinePostFilterConditions() (src/api/v1/timelines.ts) are written
as uncorrelated subqueries, e.g.:

posts.sharing_id IS NULL
OR posts.sharing_id NOT IN (
SELECT posts.id FROM posts
JOIN mutes ON mutes.muted_account_id = posts.actor_id
WHERE mutes.account_id = AND …
)

PostgreSQL plans this as a merge join that scans the entire posts table
(~1.5 million rows), which is what takes seconds. Removing just these share
filters brings the same query down to ~0.5 ms.

Rewriting them as correlated NOT EXISTS avoids the full scan:

posts.sharing_id IS NULL
OR NOT EXISTS (
SELECT 1 FROM posts AS sp
JOIN mutes ON mutes.muted_account_id = sp.actor_id
WHERE sp.id = posts.sharing_id
AND mutes.account_id = AND …
)

This is semantically equivalent (the original subquery selects the non-null
primary key posts.id, and sharing_id is guarded by IS NULL OR), and
reduces the query to ~4 ms.

The same sharing_id NOT IN (posts JOIN mutes/blocks) pattern also appears
in the public (/api/v1/timelines/public) and hashtag
(/api/v1/timelines/tag/:hashtag) timelines.

[dahlia]

Fixed in Hollo 0.9.21.