Hi,
Many of you have run into this issue lately where the second page of your feed loads so slow that it can even timeout and not load at all.

If anyone can test again to confirm my last fix actually works for everyone and not just me.

I'll explain my findings in this post once fixed.

Update

The first red herring

I was initially chasing an issue in the UI, the symptoms looked like something we ran into in the past related to dead community icons timing out sequentially and making the page take forever to load. This one was probably fixed in lemmy-ui a while ago.

The second red herring

I noticed this morning that my "mod/new" view was taking 30s to load page 2.
I spent some time messing around, but that's another issue (that is still unfixed), possibly related with how that specific query is implemented.

Anyway

The actual problem

It turns out this specific issue was completely different, this time it was a database issue.

In the default postgres settings, autovacuum's analyze trigger is 50 + 0.1 × reltuples, so a table needs ~10% of its rows inserted, updated, or deleted before it re-analyzes. On a multi-million-row table that's an enormous absolute number, and because the threshold scales with table size, the wait gets longer as the table grows. Append-heavy tables like post_read and comment_like basically sit at 2–4% of threshold forever. That degrades slowly and maybe eventually self-corrects, maybe.

What made it an actual issue... I'm not 100% sure... probably it was a stats reset (pg_upgrade doesn't carry activity counters).
The long-ass countdown restarted from scratch, while pg_stats still held pre-upgrade values.

Basically, the psql query planner was thrown off by estimates from older table versions.

Why some people's feed and not everyone's?
Good damned question.
I think the planner was suboptimal for everyone and everything, but that some specific sorts and filters multiplied it to a catastrophic level for some users' feeds.
Active/Hot are likely more expensive than New, people with specific language filters, hide read posts, etc.
I'm not exactly sure.

The temporary fix...

In the process of pulling my hair out, I eventually ran these psql queries:

ANALYZE post;
ANALYZE person;
ANALYZE local_user_language;
ANALYZE community_block;
ANALYZE instance_block;
ANALYZE person_block;
ANALYZE post_hide;
ANALYZE post_read;
ANALYZE community_follower;

1-2 seconds each, bingo, symptoms went away immediately.

The longer term fixes

Adjust autovacuum thresholds and/or just periodically analyze such tables.
Go live in the woods.