AkkomaGang/akkoma

Fork 91

SQL: Outlier query information gathering #784

New issue

Open

opened 2024-05-27 00:58:34 +00:00 by floatingghost · 7 comments

floatingghost commented

2024-05-27 00:58:34 +00:00

Owner

If you see this issue, go to

/phoenix/live_dashboard/ecto_stats?nav=outliers

and copy the outlier data here for query analysis to see if we can improve query design in any common places

template table


|  query | exec time | prop exec time | calls | sync IO time |
|--------|-----------|----------------|-------|--------------|

If you see this issue, go to `/phoenix/live_dashboard/ecto_stats?nav=outliers` and copy the outlier data here for query analysis to see if we can improve query design in any common places template table ``` | query | exec time | prop exec time | calls | sync IO time | |--------|-----------|----------------|-------|--------------| ```

floatingghost commented

2024-05-27 01:02:26 +00:00

Author

Owner

Mine:

query	exec time	prop exec time	calls	sync IO time
SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1))	1006476.661775 seconds	47.6	47976	0 seconds
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	251523.734009 seconds	11.9	112616	0 seconds
SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3)	215632.590418 seconds	10.2	6981078	0 seconds
SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."permit_followback", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3)	105653.767247 seconds	5.0	2827509	0 seconds
SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL))	65583.501187 seconds	3.1	33633706	0 seconds
UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."priority" IN ($6,$7,$8,$9)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state"	63982.806399 seconds	3.0	22013792	0 seconds
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	41508.531170 seconds	2.0	225232	0 seconds
SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2)	33832.399275 seconds	1.6	14454187	0 seconds

Mine: | query | exec time | prop exec time | calls | sync IO time | |--------|----------|-----------------|-----|---------------| | SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1)) | 1006476.661775 seconds | 47.6 | 47976 | 0 seconds | | SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 251523.734009 seconds | 11.9 |112616 | 0 seconds | | SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3) | 215632.590418 seconds | 10.2 | 6981078 | 0 seconds | |SELECT u1."id", u1."bio", u1."raw_bio", u1."email", u1."name", u1."nickname", u1."password_hash", u1."keys", u1."public_key", u1."ap_id", u1."avatar", u1."local", u1."follower_address", u1."following_address", u1."featured_address", u1."tags", u1."last_refreshed_at", u1."last_digest_emailed_at", u1."banner", u1."background", u1."note_count", u1."follower_count", u1."following_count", u1."is_locked", u1."is_confirmed", u1."password_reset_pending", u1."is_approved", u1."registration_reason", u1."confirmation_token", u1."default_scope", u1."domain_blocks", u1."is_active", u1."no_rich_text", u1."ap_enabled", u1."is_moderator", u1."is_admin", u1."show_role", u1."mastofe_settings", u1."uri", u1."hide_followers_count", u1."hide_follows_count", u1."hide_followers", u1."hide_follows", u1."hide_favorites", u1."email_notifications", u1."mascot", u1."emoji", u1."pleroma_settings_store", u1."fields", u1."raw_fields", u1."is_discoverable", u1."invisible", u1."allow_following_move", u1."skip_thread_containment", u1."actor_type", u1."also_known_as", u1."inbox", u1."shared_inbox", u1."last_active_at", u1."disclose_client", u1."pinned_objects", u1."is_suggested", u1."last_status_at", u1."language", u1."status_ttl_days", u1."permit_followback", u1."accepts_direct_messages_from", u1."notification_settings", u1."multi_factor_authentication_settings", u1."inserted_at", u1."updated_at" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON u1."id" = f0."follower_id" WHERE (f0."state" = $1) AND (f0."following_id" = $2) AND (u1."is_active" = $3) | 105653.767247 seconds | 5.0 | 2827509 | 0 seconds | | SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL)) | 65583.501187 seconds | 3.1 | 33633706 | 0 seconds | | UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."priority" IN ($6,$7,$8,$9)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state" | 63982.806399 seconds | 3.0 | 22013792 | 0 seconds | | SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 41508.531170 seconds | 2.0 | 225232 | 0 seconds | | SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2) | 33832.399275 seconds | 1.6 | 14454187 | 0 seconds |

norm commented

2024-05-27 05:28:53 +00:00

Contributor

Worth noting that the pg_stat_statements extension required for this data isn't enabled by default in Postgres.

To enable the extension, modify postgresql.conf with the following settings:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Then restart Postgres. Next connect to the akkoma database with psql and run

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Steps copied from here.

Worth noting that the `pg_stat_statements` extension required for this data isn't enabled by default in Postgres. To enable the extension, modify `postgresql.conf` with the following settings: ``` shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all ``` Then restart Postgres. Next connect to the akkoma database with psql and run ```sql CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` Steps copied from [here](https://www.bytebase.com/docs/slow-query/enable-pg-stat-statements-for-postgresql/).

YokaiRick commented

2024-05-27 15:35:38 +00:00

Contributor

Hi, here are mine. I actived the PG extenstion 9 hours ago to let it collect some stats.
Ill can post a update in a few days again, if you want.

query	exec time	prop exec time	calls	sync IO time
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	102.813244 seconds	52.1	85	0 seconds
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	28.120333 seconds	14.2	170	0 seconds
UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state"	8.065643 seconds	4.1	25400	0 seconds
SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL))	6.001011 seconds	3.0	25400	0 seconds
select installed_version from pg_available_extensions where name=$1	5.792762 seconds	2.9	5218	0 seconds
INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7)	3.928702 seconds	2.0	3051	0 seconds
UPDATE "activities" AS a0 SET "recipients" = array_replace(a0."recipients",$1,$2) WHERE ($3 = ANY(a0."recipients"))	3.923397 seconds	2.0	2	0 seconds
SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2)	3.484691 seconds	1.8	2208	0 seconds
SELECT u0."id", u0."bio", u0."raw_bio", u0."email", u0."name", u0."nickname", u0."password_hash", u0."keys", u0."public_key", u0."ap_id", u0."avatar", u0."local", u0."follower_address", u0."following_address", u0."featured_address", u0."tags", u0."last_refreshed_at", u0."last_digest_emailed_at", u0."banner", u0."background", u0."note_count", u0."follower_count", u0."following_count", u0."is_locked", u0."is_confirmed", u0."password_reset_pending", u0."is_approved", u0."registration_reason", u0."confirmation_token", u0."default_scope", u0."domain_blocks", u0."is_active", u0."no_rich_text", u0."ap_enabled", u0."is_moderator", u0."is_admin", u0."show_role", u0."mastofe_settings", u0."uri", u0."hide_followers_count", u0."hide_follows_count", u0."hide_followers", u0."hide_follows", u0."hide_favorites", u0."email_notifications", u0."mascot", u0."emoji", u0."pleroma_settings_store", u0."fields", u0."raw_fields", u0."is_discoverable", u0."invisible", u0."allow_following_move", u0."skip_thread_containment", u0."actor_type", u0."also_known_as", u0."inbox", u0."shared_inbox", u0."last_active_at", u0."disclose_client", u0."pinned_objects", u0."is_suggested", u0."last_status_at", u0."language", u0."status_ttl_days", u0."permit_followback", u0."accepts_direct_messages_from", u0."notification_settings", u0."multi_factor_authentication_settings", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $5)) AND (u0."invisible" = $1) AND (u0."is_discoverable" = $2) ORDER BY u0."last_status_at" DESC NULLS LAST, u0."id" desc nulls last LIMIT $3 OFFSET $4	3.346299 seconds	1.7	7	0 seconds
WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"	2.877429 seconds	1.5	20916	0 seconds

Hi, here are mine. I actived the PG extenstion 9 hours ago to let it collect some stats. Ill can post a update in a few days again, if you want. | query | exec time | prop exec time | calls | sync IO time | |--------|-----------|----------------|-------|--------------| SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 102.813244 seconds | 52.1 |85 |0 seconds SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 28.120333 seconds | 14.2 | 170 | 0 seconds UPDATE "public"."oban_jobs" AS o0 SET "state" = $1 FROM (SELECT so0."id" AS "id", so0."state" AS "state" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" IN ($4,$5)) AND (NOT (so0."queue" IS NULL)) AND (so0."scheduled_at" <= $2) LIMIT $3) AS s1 WHERE (o0."id" = s1."id") RETURNING o0."id", o0."queue", s1."state" | 8.065643 seconds | 4.1 | 25400 | 0 seconds SELECT DISTINCT o0."queue" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = $1) AND (NOT (o0."queue" IS NULL)) |6.001011 seconds |3.0 |25400 |0 seconds select installed_version from pg_available_extensions where name=$1 | 5.792762 seconds |2.9 |5218 |0 seconds INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7) | 3.928702 seconds |2.0 | 3051 | 0 seconds UPDATE "activities" AS a0 SET "recipients" = array_replace(a0."recipients",$1,$2) WHERE ($3 = ANY(a0."recipients")) |3.923397 seconds | 2.0 | 2 | 0 seconds SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2) |3.484691 seconds |1.8 |2208 |0 seconds SELECT u0."id", u0."bio", u0."raw_bio", u0."email", u0."name", u0."nickname", u0."password_hash", u0."keys", u0."public_key", u0."ap_id", u0."avatar", u0."local", u0."follower_address", u0."following_address", u0."featured_address", u0."tags", u0."last_refreshed_at", u0."last_digest_emailed_at", u0."banner", u0."background", u0."note_count", u0."follower_count", u0."following_count", u0."is_locked", u0."is_confirmed", u0."password_reset_pending", u0."is_approved", u0."registration_reason", u0."confirmation_token", u0."default_scope", u0."domain_blocks", u0."is_active", u0."no_rich_text", u0."ap_enabled", u0."is_moderator", u0."is_admin", u0."show_role", u0."mastofe_settings", u0."uri", u0."hide_followers_count", u0."hide_follows_count", u0."hide_followers", u0."hide_follows", u0."hide_favorites", u0."email_notifications", u0."mascot", u0."emoji", u0."pleroma_settings_store", u0."fields", u0."raw_fields", u0."is_discoverable", u0."invisible", u0."allow_following_move", u0."skip_thread_containment", u0."actor_type", u0."also_known_as", u0."inbox", u0."shared_inbox", u0."last_active_at", u0."disclose_client", u0."pinned_objects", u0."is_suggested", u0."last_status_at", u0."language", u0."status_ttl_days", u0."permit_followback", u0."accepts_direct_messages_from", u0."notification_settings", u0."multi_factor_authentication_settings", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $5)) AND (u0."invisible" = $1) AND (u0."is_discoverable" = $2) ORDER BY u0."last_status_at" DESC NULLS LAST, u0."id" desc nulls last LIMIT $3 OFFSET $4 | 3.346299 seconds |1.7 |7 |0 seconds WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" | 2.877429 seconds |1.5 |20916 |0 seconds

norm commented

2024-05-27 16:39:38 +00:00

Contributor

Here's mine after about 12 hours (deleted the original since it wasn't run for long enough):

query	exec time	prop exec time	calls	sync IO time
SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1))	5402.192327 seconds	83.1	185	0 seconds
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", b2."id", b2."user_id", b2."activity_id", b2."inserted_at", b2."updated_at", NOT (t3."id" IS NULL) FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>$21) = COALESCE(a0."data"->$22->>$23, a0."data"->>$24) 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"->>$25) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $26) AS f4 ON $27 INNER JOIN "users" AS u5 ON (a0."actor" = u5."ap_id") AND (u5."invisible" = $28) WHERE (a0."id" > $3) AND (a0."data"->>$29 = $4) AND (not (a0."actor" = ANY($5))) AND (((not (a0."recipients" && $6)) or a0."actor" = $7)) AND ((recipients_contain_blocked_domains(a0."recipients", $8) = $30) or a0."actor" = $9) AND (not (a0."data"->>$31 = $32 and a0."data"->$33 ?\| $10)) AND ((not (split_part(a0."actor", $34, $35) = ANY($11))) or a0."actor" = ANY($12)) AND ((not (split_part(o1."data"->>$36, $37, $38) = ANY($13))) or (o1."data"->>$39) = ANY($14)) AND (not ( a0."data"->>$40 = $41 and a0."actor" = ANY($15))) AND (not(o1."data"->>$42 = $43)) AND (a0."recipients" && $16 OR (a0."recipients" && $17 AND $18 = ANY(a0."recipients"))) AND (a0."id" > $19) ORDER BY a0."id" desc nulls last LIMIT $20	190.333149 seconds	2.9	1118	0 seconds
SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1)	148.729416 seconds	2.3	133	0 seconds
SELECT o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = ANY($1)) AND (o0."inserted_at" >= $2) AND ((o0."worker" = $3) AND ((o0."queue" = $4) AND ((o0."args" @> $5 AND o0."args" <@ $6) AND $7))) LIMIT $8	100.318536 seconds	1.5	139889	0 seconds
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"->>$6) = COALESCE(a0."data"->$7->>$8, a0."data"->>$9) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $10) AS f2 ON $11 INNER JOIN "users" AS u3 ON (a0."actor" = u3."ap_id") AND (u3."invisible" = $12) WHERE ($1 && a0."recipients") AND (a0."actor" = $2) AND (a0."data"->>$13 = ANY($3)) AND (a0."data"->>$14 != $15) AND (not(o1."data"->>$16 = $17)) AND (a0."id" > $4) ORDER BY a0."id" asc nulls last LIMIT $5	59.240213 seconds	0.9	2621	0 seconds
SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2)	51.662827 seconds	0.8	62745	0 seconds
INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7)	48.560783 seconds	0.7	42695	0 seconds
SELECT u0."ap_id" FROM "users" AS u0 INNER JOIN "following_relationships" AS f1 ON (f1."following_id" = u0."id") AND (f1."follower_id" = $1) WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $4)) AND (u0."is_active" = $5) AND (u0."id" != $2) AND (f1."state" = $3)	31.241961 seconds	0.5	121338	0 seconds
WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"	23.282083 seconds	0.4	251051	0 seconds
SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible"))	21.774968 seconds	0.3	266	0 seconds

Here's mine after about 12 hours (deleted the original since it wasn't run for long enough): | query | exec time | prop exec time | calls | sync IO time | |--------|-----------|----------------|-------|--------------| | SELECT o0."id", o0."data", o0."inserted_at", o0."updated_at" FROM "objects" AS o0 WHERE (to_jsonb(array(select jsonb_array_elements((o0."data")#>$2) ->> $3 where jsonb_typeof((o0."data")#>$4) = $5))::jsonb ?\| ($1)) | 5402.192327 seconds | 83.1 | 185 | 0 seconds| 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", b2."id", b2."user_id", b2."activity_id", b2."inserted_at", b2."updated_at", NOT (t3."id" IS NULL) FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>$21) = COALESCE(a0."data"->$22->>$23, a0."data"->>$24) 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"->>$25) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $26) AS f4 ON $27 INNER JOIN "users" AS u5 ON (a0."actor" = u5."ap_id") AND (u5."invisible" = $28) WHERE (a0."id" > $3) AND (a0."data"->>$29 = $4) AND (not (a0."actor" = ANY($5))) AND (((not (a0."recipients" && $6)) or a0."actor" = $7)) AND ((recipients_contain_blocked_domains(a0."recipients", $8) = $30) or a0."actor" = $9) AND (not (a0."data"->>$31 = $32 and a0."data"->$33 ?\| $10)) AND ((not (split_part(a0."actor", $34, $35) = ANY($11))) or a0."actor" = ANY($12)) AND ((not (split_part(o1."data"->>$36, $37, $38) = ANY($13))) or (o1."data"->>$39) = ANY($14)) AND (not ( a0."data"->>$40 = $41 and a0."actor" = ANY($15))) AND (not(o1."data"->>$42 = $43)) AND (a0."recipients" && $16 OR (a0."recipients" && $17 AND $18 = ANY(a0."recipients"))) AND (a0."id" > $19) ORDER BY a0."id" desc nulls last LIMIT $20 | 190.333149 seconds | 2.9 | 1118 | 0 seconds| |SELECT distinct split_part(u0."nickname", $2, $3) FROM "users" AS u0 WHERE (u0."local" != $1) | 148.729416 seconds | 2.3 | 133 | 0 seconds| |SELECT o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" FROM "public"."oban_jobs" AS o0 WHERE (o0."state" = ANY($1)) AND (o0."inserted_at" >= $2) AND ((o0."worker" = $3) AND ((o0."queue" = $4) AND ((o0."args" @> $5 AND o0."args" <@ $6) AND $7))) LIMIT $8 | 100.318536 seconds | 1.5 | 139889 | 0 seconds| |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"->>$6) = COALESCE(a0."data"->$7->>$8, a0."data"->>$9) INNER JOIN LATERAL (SELECT is_active from users WHERE ap_id = a0."actor" AND is_active = $10) AS f2 ON $11 INNER JOIN "users" AS u3 ON (a0."actor" = u3."ap_id") AND (u3."invisible" = $12) WHERE ($1 && a0."recipients") AND (a0."actor" = $2) AND (a0."data"->>$13 = ANY($3)) AND (a0."data"->>$14 != $15) AND (not(o1."data"->>$16 = $17)) AND (a0."id" > $4) ORDER BY a0."id" asc nulls last LIMIT $5 | 59.240213 seconds | 0.9 | 2621 | 0 seconds| |SELECT u1."follower_address" FROM "following_relationships" AS f0 INNER JOIN "users" AS u1 ON f0."following_id" = u1."id" WHERE (f0."follower_id" = $1) AND (f0."state" = $2) | 51.662827 seconds | 0.8 | 62745 | 0 seconds| |INSERT INTO "activities" ("data","local","recipients","actor","id","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7) | 48.560783 seconds | 0.7 | 42695 | 0 seconds| |SELECT u0."ap_id" FROM "users" AS u0 INNER JOIN "following_relationships" AS f1 ON (f1."following_id" = u0."id") AND (f1."follower_id" = $1) WHERE (NOT (u0."nickname" IS NULL)) AND (NOT (u0."nickname" LIKE $4)) AND (u0."is_active" = $5) AND (u0."id" != $2) AND (f1."state" = $3) | 31.241961 seconds | 0.5 | 121338 | 0 seconds| |WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE (so0."state" = $7) AND (so0."queue" = $1) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (o0."id" = f1."id") AND (o0."attempt" < o0."max_attempts") RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at" | 23.282083 seconds | 0.4 | 251051 | 0 seconds| |SELECT count(u0."id") FROM "users" AS u0 WHERE (u0."is_active" = $1) AND (u0."local" = $2) AND (NOT (u0."nickname" IS NULL)) AND (NOT (u0."invisible")) | 21.774968 seconds | 0.3 | 266 | 0 seconds |

Oneric commented

2024-05-27 17:09:27 +00:00

Member

worth noting the top offender by far for ihba and akko is the query of the attachment cleanup worker; also see #765 for this one

@YokaiRick since it doesn’t show up at all on your instance, can you confirm whether :instance, :cleanup_attachments is set to false (the default)?

worth noting the top offender by far for ihba and akko is the query of the attachment cleanup worker; also see #765 for this one @YokaiRick since it doesn’t show up at all on your instance, can you confirm whether `:instance, :cleanup_attachments` is set to false *(the default)*?

YokaiRick commented

2024-05-27 18:00:38 +00:00

Contributor

@Oneric nope, i didn't deactivate it: here is my instance part:

config :pleroma, :instance,
  name: "n0ids space",
  email: "rick@XXX.space",
  notify_email: "rick@XXX.space",
  limit: 5000,
  registrations_open: false,
  export_prometheus_metrics: true,
  public: true,
  max_pinned_statuses: 10

and i grepped for the default stuff also:

/opt/akkoma/releases/3.13.2-0-g050bc74/sys.config : {extended_nickname_format,true},{cleanup_attachments,false},{

i have a single user instance and tbh i can't remember when it was the last time i did delete a post.. maybe thats why?

@Oneric nope, i didn't deactivate it: here is my instance part: ``` config :pleroma, :instance, name: "n0ids space", email: "rick@XXX.space", notify_email: "rick@XXX.space", limit: 5000, registrations_open: false, export_prometheus_metrics: true, public: true, max_pinned_statuses: 10 ``` and i grepped for the default stuff also: ``` /opt/akkoma/releases/3.13.2-0-g050bc74/sys.config : {extended_nickname_format,true},{cleanup_attachments,false},{ ``` i have a single user instance and tbh i can't remember when it was the last time i did delete a post.. maybe thats why?

Oneric commented

2024-05-27 21:38:49 +00:00

Member

Yeah, if you didn’t set it at all it defaults to false; but never deleting posts would amount to the same effect; thanks!

Yeah, if you didn’t set it at all it defaults to `false`; but never deleting posts would amount to the same effect; thanks!