Fetching favourites causes DB timeout
Hey there.
I recently upgraded my instance from v0.9.999 to v1.0.6.
Since the upgrade, fetching favourites takes >= 10 seconds, and more often than not the query is killed after 15s.
Other queries seem to perform normally.
This might be related to (or is a duplicate of) #1224 (closed), but it's hard to say since the ticket didn't contain a lot of information.
Pleroma is running on an otherwise barely loaded server.
iowait is not an issue, and no other programs were doing disk-related work.
Postgres' total CPU usage spikes to around 30-50% while fetching the favourites, and stays there until it gets killed.
A log of said timeout is attached below:
server_1 | 17:40:24.585 [error] Postgrex.Protocol (#PID<0.498.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.2103.0> timed out because it queued and checked out the connection for longer than 15000ms
db_1 | 2019-08-28 17:40:24.586 UTC [8879] ERROR: canceling statement due to user request
db_1 | 2019-08-28 17:40:24.586 UTC [8879] STATEMENT: SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at" FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>'id') = COALESCE(a0."data"->'object'->>'id', a0."data"->>'object') WHERE (a0."data"->>'type' = $1) AND ((o1."data")->'likes' ? ($2)) AND (not (a0."actor" = ANY($3))) AND (not (a0."recipients" && $4)) AND (not (a0."data"->>'type' = 'Announce' and a0."data"->'to' ?| $5)) AND (not (split_part(a0."actor", '/', 3) = ANY($6))) AND (not (split_part(o1."data"->>'actor', '/', 3) = ANY($7))) AND (a0."actor" not in (SELECT ap_id FROM users WHERE info->'deactivated' @> 'true')) AND (not(o1."data"->>'type' = 'Answer')) ORDER BY a0."id" desc nulls last LIMIT $8
db_1 | 2019-08-28 17:40:24.591 UTC [6] LOG: worker process: parallel worker for PID 8879 (PID 8880) exited with exit code 1
db_1 | 2019-08-28 17:40:24.591 UTC [6] LOG: worker process: parallel worker for PID 8879 (PID 8881) exited with exit code 1
db_1 | 2019-08-28 17:40:24.592 UTC [8879] LOG: could not send data to client: Broken pipe
db_1 | 2019-08-28 17:40:24.592 UTC [8879] FATAL: connection to client lost
server_1 | 17:40:24.601 [error] #PID<0.2103.0> running Pleroma.Web.Endpoint (connection #PID<0.2101.0>, stream id 1) terminated
server_1 | Server: cofe.rocks:80 (http)
server_1 | Request: GET /api/v1/favourites?count=20&with_muted=true
server_1 | ** (exit) an exception was raised:
server_1 | ** (DBConnection.ConnectionError) tcp recv: closed (the connection was closed by the pool, possibly due to a timeout or because the pool has been terminated)
server_1 | (ecto_sql) lib/ecto/adapters/sql.ex:618: Ecto.Adapters.SQL.raise_sql_call_error/1
server_1 | (ecto_sql) lib/ecto/adapters/sql.ex:551: Ecto.Adapters.SQL.execute/5
server_1 | (ecto) lib/ecto/repo/queryable.ex:153: Ecto.Repo.Queryable.execute/4
server_1 | (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
server_1 | (pleroma) lib/pleroma/pagination.ex:24: Pleroma.Pagination.fetch_paginated/3
server_1 | (pleroma) lib/pleroma/web/activity_pub/activity_pub.ex:955: Pleroma.Web.ActivityPub.ActivityPub.fetch_activities/2
server_1 | (pleroma) lib/pleroma/web/mastodon_api/controllers/mastodon_api_controller.ex:1151: Pleroma.Web.MastodonAPI.MastodonAPIController.favourites/2
server_1 | (pleroma) lib/pleroma/web/mastodon_api/controllers/mastodon_api_controller.ex:5: Pleroma.Web.MastodonAPI.MastodonAPIController.action/2
Thanks in advance for any help