appview/db: speed up following timeline queries
benchmarked on a synthetic db (10k users, 500k follows, 500k stars,
50k repos), the per-source queries behind the following timeline were
slow because of missing indexes and a poor query shape:
- stars filtered by did had no usable index; sqlite picked
idx_stars_subject_type which matches nearly every row (~312ms)
- follower/following counts group by subject_did, which had no
index, forcing a full scan (~229ms)
- follows ordered by followed_at with no index sorted every matched
row in a temp b-tree (~157ms)
fixes:
- new indexes: stars(did, subject_type, created),
follows(subject_did), follows(followed_at), repos(created); the
latter two also let the global timeline read newest-first straight
off an index instead of sorting
- push the following-set membership check into sql with a subquery
(orm.FilterInSubquery) instead of materializing all followed dids
in go and binding them as hundreds of placeholders in three
separate queries
all benchmarked queries now run in ~1ms.
Signed-off-by: Anirudh Oppiliappan <anirudh@tangled.org>