home timeline query with huge user id list
Environment
- Installation type (OTP or From Source): OTP
- Pleroma version (could be found in the "Version" tab of settings in Pleroma-FE): 2.4.3
- Elixir version (
elixir -v
for from source installations, N/A for OTP): - Operating system:
- PostgreSQL version (
psql -V
): 12
Bug description
In the slow query logs, this >30s query shows up repeatedly with $20
has about 15,000 user ap_ids, all of them are deactivated/deleted users. Can Pleroma delete activity/object when user gets deleted, so that it doesn't have to use this huge deleted user list when generating timeline?
2022-09-16 13:51:04.220 EDT [15765] pleroma@pleroma LOG: duration: 30119.489 ms execute ecto_5634: SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at", b2."id", b2."user_id", b2."activity_id", b2."inserted_at", b2."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at", NOT (t3."id" IS NULL) FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>'id') = COALESCE(a0."data"->'object'->>'id', a0."data"->>'object') LEFT OUTER JOIN "bookmarks" AS b2 ON (b2."user_id" = $1) AND (b2."activity_id" = a0."id") LEFT OUTER JOIN "thread_mutes" AS t3 ON (t3."user_id" = $2) AND (t3."context" = a0."data"->>'context') WHERE ((($3 && a0."recipients")) OR (a0."actor" = $4)) AND (a0."data"->>'type' = ANY($5)) AND (not (a0."actor" = ANY($6))) AND (((not (a0."recipients" && $7)) or a0."actor" = $8)) AND (recipients_contain_blocked_domains(a0."recipients", $9) = false) AND (not (a0."data"->>'type' = 'Announce' and a0."data"->'to' ?| $10)) AND ((not (split_part(a0."actor", '/', 3) = ANY($11))) or a0."actor" = ANY($12)) AND ((not (split_part(o1."data"->>'actor', '/', 3) = ANY($13))) or (o1."data"->>'actor') = ANY($14)) AND (not (a0."actor" = ANY($15))) AND (not (a0."data"->'to' ?| $16) or a0."actor" = $17) AND (t3."user_id" IS NULL) AND (not ( a0."data"->>'type' = 'Announce' and a0."actor" = ANY($18))) AND (a0."data"->>'type' != 'Announce' or o1."data"->>'actor' != $19) AND (NOT (a0."actor" = ANY($20))) AND (not(o1."data"->>'type' = 'Answer')) AND (not(o1."data"->>'type' = 'ChatMessage')) AND (NOT (a0."actor" = ANY($21))) ORDER BY a0."id" desc nulls last LIMIT $22
Edited by shevek