Poor performance of querying the home timeline #140
Description
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.
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.