Very poor query performance after upgrade
Environment
- Installation type (OTP or From Source): Source
- Pleroma version (could be found in the "Version" tab of settings in Pleroma-FE): 90d4b7d6
- Elixir version (
elixir -v
for from source installations, N/A for OTP): Elixir 1.12.2 (compiled with Erlang/OTP 24) - Operating system: Ubuntu 22.04
- PostgreSQL version (
psql -V
): psql (PostgreSQL) 14.5 (Ubuntu 14.5-1.pgdg22.04+1)
Bug description
For the past several days now I've been having really poor database performance too the point that it's affecting federation.
The main culprit seems to be the query UPDATE "activities" AS a0 SET "data" = jsonb_set(data, $5, $1) WHERE ((a0."data")->>$6 = $2) AND (a0."actor" = $3) AND (associated_object_id((a0."data")) = $4) AND (data->>$7 = $8 OR data->>$9 = $10)
where in about 5 minutes of monitoring it had only run 134 times but had used almost 80% of all of the query time.
It really seems like an index issue to me.