Hello.
After upgrading to Mastodon 3.0.0 Postgres consumes 100% of CPU while generating Local and Public timelines.
I’ve stopped services, dumped the DB and re-created it from dump, but i still have very slow queries. For example:
2019-10-07 20:15:36.241 PDT [29298] mastodon@mastodon_production LOG: duration: 7458.818 ms execute a12: SELECT "statuses".* FROM "statuses" WHERE "statuses"."deleted_at" IS NULL AND "statuses"."account_id" = $1 AND "statuses"."
reblog_of_id" = $2 ORDER BY "statuses"."id" DESC LIMIT $3
2019-10-07 20:15:36.241 PDT [29298] mastodon@mastodon_production DETAIL: parameters: $1 = '48', $2 = '102924583513975602', $3 = '1'
2019-10-07 20:15:38.937 PDT [30375] mastodon@mastodon_production LOG: duration: 92863.952 ms execute <unnamed>: SELECT "statuses"."id", "statuses"."updated_at" FROM "statuses" LEFT OUTER JOIN "accounts" ON "accounts"."id" = "sta
tuses"."account_id" WHERE "statuses"."visibility" = $1 AND (statuses.reblog_of_id IS NULL) AND (statuses.reply = FALSE OR statuses.in_reply_to_account_id = statuses.account_id) AND "statuses"."account_id" NOT IN ($2, $3, $4, $5, $6
, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $5
3, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63) AND (accounts.domain IS NULL OR accounts.domain NOT IN ('pawoo.net','baraag.net','mstdn.foxfam.club')) AND ("statuses"."language" IS NULL OR "statuses"."language" = $64) AND "sta
tuses"."deleted_at" IS NULL AND "accounts"."silenced_at" IS NULL AND "statuses"."id" < 102924270400802132 ORDER BY "statuses"."id" DESC, "statuses"."id" DESC LIMIT $65
2019-10-07 20:15:38.937 PDT [30375] mastodon@mastodon_production DETAIL: parameters: $1 = '0', $2 = '45785', $3 = '35891', $4 = '114', $5 = '95', $6 = '34', $7 = '57310', $8 = '52161', $9 = '50551', $10 = '11957', $11 = '5687', $1
2 = '19414', $13 = '2044', $14 = '26026', $15 = '33101', $16 = '5393', $17 = '931', $18 = '2294', $19 = '2351', $20 = '821', $21 = '23502', $22 = '41470', $23 = '42569', $24 = '41477', $25 = '42575', $26 = '4191', $27 = '4840', $28
= '87', $29 = '36928', $30 = '41471', $31 = '14790', $32 = '31390', $33 = '41487', $34 = '42610', $35 = '41484', $36 = '41481', $37 = '42559', $38 = '42868', $39 = '7488', $40 = '3314', $41 = '3610', $42 = '3250', $43 = '3113', $4
4 = '2898', $45 = '7522', $46 = '3496', $47 = '7655', $48 = '4538', $49 = '28068', $50 = '5100', $51 = '29443', $52 = '7734', $53 = '23193', $54 = '34642', $55 = '34277', $56 = '13193', $57 = '1384', $58 = '28942', $59 = '11178', $
60 = '2041', $61 = '1401', $62 = '3121', $63 = '1132', $64 = 'ru', $65 = '20'
2019-10-07 20:15:49.053 PDT [29347] mastodon@mastodon_production LOG: duration: 6908.775 ms execute a15: SELECT "statuses".* FROM "statuses" WHERE "statuses"."deleted_at" IS NULL AND "statuses"."account_id" = $1 AND "statuses"."
reblog_of_id" = $2 ORDER BY "statuses"."id" DESC LIMIT $3
Reindexing did not solve the issue. What else can I try to do?