Poor performance of querying the home timeline

[RangHo]

I've had this issue for quite a while.

With my brand-new Intel N100 + 16 GiB RAM + 500 GB NVMe SSD running Fedora CoreOS with containerized Hollo and PostgreSQL running as a systemd service, querying the home timeline (i.e. /api/v1/timelines/home?limit=40) takes at least 8400 ms.

I bit the bullet and did some digging, and it seems that not only my server lacks some juice, but also there might be some room for optimization.

Attached is a sample query that was made against my own personal instance, the resulting EXPLAIN artifact.

hollo.zip

Using https://pev2.pages.dev/, I noticed that the most of the processing happens during the final LIMIT query. Do you think there is a better way to handle this?

Note

The local timeline experiences the similar slowdown, but the federated timeline seems to be significantly faster.

[ThisIsMissEm]

Do you have that available as a text file?

[RangHo]

Do you have that available as a text file?

Do you mean the hollo.zip file? Inside the archive there should be a JSON explanation file that is ~2.2 MiB.

I didn't even bother exporting the plaintext explanation since it was way too large... I can produce one tomorrow if that is easier to read.

[ThisIsMissEm]

Perhaps use a gist for it?

[RangHo]

Ah, I guess that's also an option. I'll come up with some gists tomorrow.

[RangHo]

Sorry for the delay---I haven't had access to my computer for a while. For now, I have the JSON version of the EXPLAIN result uploaded here. I'll have the pure text version uploaded in the same gist once I get full access to my server back.

[dahlia]

Have you been using block or mute a lot?

[RangHo]

Have you been using block or mute a lot?

Not really—I have a couple of accounts blocked, all of which are spam accounts.

On a side note, I am willing to migrate the database to a different version of PostgreSQL to see if this is a regression due to the database version difference. I am currently running the latest container version of PostgreSQL v17. Could you share which version of PostgreSQL you are running?

[dahlia]

I'm running PostgreSQL v17.2 on hollo.social.

[RangHo]

A bit unrelated to the PR linked above: I'd love to survey how many rows are registered in your posts table.

I've recently upgraded the hosting machine to a Ryzen 7 4700U machine from an Intel N100 one, and based on its sheer specs, 0.8M rows should not be a problem...

[dahlia]

There are ~2,500,000 rows in my posts table, and I have no problem.

[RangHo]

I've decided to nuke all posts and see "rebuilding" a timeline magically fixed my issue. I'll report back in a couple weeks if there is a performance degradation.

[RangHo]

Update: The database query time started showing signs of degradation again at around 420,000 posts. It takes around 7.8 seconds on average to load the home timeline.