Add index hotspots

Here are three indexes that speed up Pleroma considerably; the second two have resulted in a 37.9-49.6% reduction in backend response times for affected endpoints (roughly 600k requests/day) and—because these are busy endpoints—a 25.8-37.9% reduction in response times across all backend-involved requests (roughly 2m requests/day), per the nginx logs, using $upstream_header_time to measure how long the backend took to prepare a response.

All figures given are for FSE. At time of writing, the database is 283GB on-disk, with 269,310 rows in the users table (415MB), 2,203,919 rows in the notifications table (218MB), and 108,127 rows in the following_relationships table (10MB). The index sizes noted will probably be significantly smaller for smaller databases. All of these migrations have been running in production without problems. They were created by periodically polling Postgres to see which queries had taken longer than 250ms and then examining the query plan for for those queries.

users USING btree (ap_id, COALESCE(follower_address, ''::character varying))

The first one indexes ap_id and follower_address on users. Since adding the visibility index, every row inserted into activities triggers a full-table scan of users; this remedies that. The overhead for individual activities is not so bad but when loading a dump of a database, this reduced the time it took to load a dump from 180 hours(!) to about two hours. It's present in a lot of deployments; I've been suggesting people add this index when they complained that it was taking a long time to load from backup. FSE and all of the CofeSpace instances have been using it without incident for a couple of years. The cost of this index is 39MB.

following_relationships USING btree (follower_id, state)

This index speeds up the home timeline: the previous query plan scanned two indexes and had a filter and a recheck, this changes it to a single index scan. In addition to the measured speedup, home timeline loading is subjectively much faster. The cost of this index is 4MB.

notifications USING btree (user_id, seen)

This index significantly speeds up /api/v1/notifications by again turning a somewhat expensive query into a single index scan. The cost of this index is 86MB. (This is 10MB on Poast, which regularly trims the notifications table.)