Some indices to help your Mastodon PostgreSQL performance

custom_timeline_indices.sql

-- If you find this query is popping up on your Mastodon's slow query list
--
--   SELECT "statuses"."id", "statuses"."updated_at" FROM "statuses" 
--   INNER JOIN "accounts" ON "accounts"."id" = "statuses"."account_id" 
--   WHERE "statuses"."visibility" = $1 AND "accounts"."suspended_at" IS NULL 
--   AND "accounts"."silenced_at" IS NULL AND (statuses.reply = $4 OR 
--   statuses.in_reply_to_account_id = statuses.account_id) AND 
--   (statuses.reblog_of_id IS NULL) AND "statuses"."deleted_at" IS NULL 
--   AND "statuses"."language" = $2 AND "statuses"."id" < $5 
--   ORDER BY "statuses"."id" DESC LIMIT $3
--
-- Try applying the indices below to optimize them.  They are set up to cover
-- exactly this query.
-- 
-- Note: when adding a DB index, there's a tradeoff between read performance
-- and extra work during INSERT/UPDATE/DELETE operations

CREATE INDEX ix_custom__statuses__timeline_replies ON statuses (
    id DESC, 
    visibility,
    language
) 
INCLUDE (updated_at, account_id)
WHERE (
    (reply = true OR (in_reply_to_account_id = account_id)) AND
    (reblog_of_id IS NULL) AND
    (deleted_at IS NULL)
);

CREATE INDEX ix_custom__statuses__timeline_not_replies ON statuses (
    id DESC, 
    visibility,
    language
) 
INCLUDE (updated_at, account_id)
WHERE (
    (reply = false OR (in_reply_to_account_id = account_id)) AND
    (reblog_of_id IS NULL) AND
    (deleted_at IS NULL)
);

CREATE INDEX ix_custom__accounts__timeline ON accounts (id) 
WHERE (suspended_at IS NULL AND silenced_at IS NULL);