Activity table not being indexed

[HorseJump]

Found a bug? Please fill out the sections below. 👍

Issue Summary

A summary of the bug.
SELECT statements are taking a long time (> 5000ms) to complete at times. Appears that Activity table isn't being indexed.

Technical details

Logs:
postgres_1 | 2022-12-16 17:27:52.887 UTC [11973] LOG: duration: 5010.355 ms execute __diesel_stmt_31: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:27:52.887 UTC [11973] DETAIL: parameters: $1 = 'https://midwest.social/activities/create/c922b6e1-1e00-4d85-8a8f-374bdfcf76c9', $2 = '1'
postgres_1 | 2022-12-16 17:29:00.533 UTC [11973] LOG: duration: 5360.124 ms execute __diesel_stmt_31: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:29:00.533 UTC [11973] DETAIL: parameters: $1 = 'https://midwest.social/activities/create/c922b6e1-1e00-4d85-8a8f-374bdfcf76c9', $2 = '1'
postgres_1 | 2022-12-16 17:29:26.750 UTC [13649] LOG: duration: 5268.068 ms execute __diesel_stmt_17: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:29:26.750 UTC [13649] DETAIL: parameters: $1 = 'https://midwest.social/activities/create/c922b6e1-1e00-4d85-8a8f-374bdfcf76c9', $2 = '1'
postgres_1 | 2022-12-16 17:29:26.911 UTC [11973] LOG: duration: 5080.917 ms execute __diesel_stmt_31: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:29:26.911 UTC [11973] DETAIL: parameters: $1 = 'https://midwest.social/activities/create/c922b6e1-1e00-4d85-8a8f-374bdfcf76c9', $2 = '1'
postgres_1 | 2022-12-16 17:30:06.950 UTC [11973] LOG: duration: 5306.031 ms execute __diesel_stmt_31: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:30:06.950 UTC [11973] DETAIL: parameters: $1 = 'https://midwest.social/activities/like/e169df97-63a0-4eee-ab8c-045b70c6195d', $2 = '1'
postgres_1 | 2022-12-16 17:30:08.674 UTC [13649] LOG: duration: 5065.262 ms execute __diesel_stmt_17: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:30:08.674 UTC [13649] DETAIL: parameters: $1 = 'https://midwest.social/activities/like/e169df97-63a0-4eee-ab8c-045b70c6195d', $2 = '1'
postgres_1 | 2022-12-16 17:30:41.894 UTC [13649] LOG: duration: 5249.674 ms execute __diesel_stmt_17: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:30:41.894 UTC [13649] DETAIL: parameters: $1 = 'https://midwest.social/activities/like/e169df97-63a0-4eee-ab8c-045b70c6195d', $2 = '1'
postgres_1 | 2022-12-16 17:30:44.768 UTC [11973] LOG: duration: 5025.025 ms execute __diesel_stmt_31: SELECT "activity"."id", "activity"."data", "activity"."local", "activity"."published", "activity"."updated", "activity"."ap_id", "activity"."sensitive" FROM "activity" WHERE "activity"."ap_id" = $1 LIMIT $2
postgres_1 | 2022-12-16 17:30:44.768 UTC [11973] DETAIL: parameters: $1 = 'https://midwest.social/activities/like/e169df97-63a0-4eee-ab8c-045b70c6195d', $2 = '1'

[Nutomic]

You can fix this by adding a new sql migration: https://github.com/LemmyNet/lemmy/tree/main/migrations

[HorseJump]

Any idea why it would be running the same query 4 times in a row?

[Nutomic]

Maybe that url was federated to a few different instances, and they all made requests to resolve the data. Or the request timed out and was retried.

[HorseJump]

Oh OK. I thought there might be some bug that is causing it to do that and taking up resources.

I unfortunately don't know how to write sql migrations.

[dessalines]

This index should already exist. Run the following command in postgres: \d idx_activity_ap_id

If it doesn't give you any result, then your database got into a weird state with potentially lots of missing indexes.

[HorseJump]

Yeah, that gives no results. Now that I think about it I don't think this started happening until after I migrated to postgres 14 from 12.