Public/local timeline is slow to load if the user has blocked/muted others

[Smalls1652]

Steps to reproduce the problem

1. Log into your account.
2. Block or mute a few accounts. (I was able to trigger it with more than 6 accounts muted/blocked)
3. Get your local server's timeline feed through either:
   • The /public/local endpoint for the web app.
   • Calling the /api/v1/timelines/public?local=true API endpoint.

Removing all mutes and blocks causes the timeline to load instantaneously.

Expected behaviour

Loading the local server timeline should be fairly quick.

Actual behaviour

Loading the local server timeline takes around 10 seconds to over 1 minute to load.

Detailed description

Whenever a user has a small handful of accounts blocked or muted, the local server timeline can take a while for it to load. I've seen it take anywhere from 10 seconds to upwards of 1 minute from the metrics I was able to gather. I'm not sure how long this has been going on and I haven't delved too deep into it, but it was recently brought to my attention by one of the users on my instance on 2025-01-24. The odd thing is that it doesn't seem to affect other timelines (Even the Other servers or the All timelines).

I was able to pinpoint this down to the SQL query that is ran by selectively removing one filter in the WHERE clause and measuring the response time for it.

The original SQL query:

Taking the "statuses"."account_id" filter out:

This is a trace of one of the longer response times one of my users got:

Mastodon instance

ocw.social

Mastodon version

v4.4.0-alpha.2+glitch

Technical details

I'm running:

• Mastodon v4.4.0-alpha.2+glitch
  • (Built directly off the main branch at 2025-01-27 16:07 +00:00)
  • It's a custom container image build roughly based off the upstream container image build.
• Ruby v3.4.1 (with YJIT enabled)
• Node v22
• PostgreSQL v16.6

For the PostgreSQL database I have:

• It running in the same Kubernetes cluster as the Mastodon server.
• 2 CPU cores allocated
• 1 GiB of memory allocated
• Roughly 49 GiB in size and stored on a SSD volume.

And it has the following in the postgref.conf file (I'm using values generated by PGTune):

max_connections = 150
shared_buffers = 256MB
effective_cache_size = 768MB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 7864kB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 873kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB