joins are better than in queries with potentially thousands of inserted IDs.
Given that more than 8 JOIN statements is something PostgreSQL specifically concerns itself with (join_collapse_limit). I hand-edit the query with a single IN clause and the performance problem disappears. 8 full seconds becomes less than 200ms against 5,431,043 posts. And that 200ms is still high, as I was extremely over-reaching with “LIMIT 1000” in case the end-user went wild with blocking lists or some other filtering before reaching the final “LIMIT 10”. When I change it to “LIMIT 20” in the subquery, it drops almost in half to 115ms… still meeting the needs of the outer “LIMIT 10” by double. More of the core query filtering can be put into the IN subquery, as we aren’t dealing with more than 500 length pages (currently limited to 50).
<span style="color:#323232;">SELECT
</span><span style="color:#323232;"> "post"."id" AS post_id, "post"."name" AS post_title,
</span><span style="color:#323232;"> -- "post"."url", "post"."body", "post"."creator_id", "post"."community_id", "post"."removed", "post"."locked", "post"."published", "post"."updated", "post"."deleted", "post"."nsfw", "post"."embed_title", "post"."embed_description", "post"."thumbnail_url",
</span><span style="color:#323232;"> -- "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local",
</span><span style="color:#323232;"> "person"."id" AS p_id, "person"."name",
</span><span style="color:#323232;"> -- "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated",
</span><span style="color:#323232;"> -- "person"."actor_id", "person"."bio", "person"."local", "person"."private_key", "person"."public_key", "person"."last_refreshed_at", "person"."banner", "person"."deleted", "person"."inbox_url", "person"."shared_inbox_url", "person"."matrix_user_id", "person"."admin",
</span><span style="color:#323232;"> -- "person"."bot_account", "person"."ban_expires",
</span><span style="color:#323232;"> "person"."instance_id" AS p_inst,
</span><span style="color:#323232;"> "community"."id" AS c_id, "community"."name" AS community_name,
</span><span style="color:#323232;"> -- "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted",
</span><span style="color:#323232;"> -- "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner",
</span><span style="color:#323232;"> -- "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods",
</span><span style="color:#323232;"> "community"."instance_id" AS c_inst,
</span><span style="color:#323232;"> -- "community"."moderators_url", "community"."featured_url",
</span><span style="color:#323232;"> ("community_person_ban"."id" IS NOT NULL) AS ban,
</span><span style="color:#323232;"> -- "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes", "post_aggregates"."downvotes", "post_aggregates"."published",
</span><span style="color:#323232;"> -- "post_aggregates"."newest_comment_time_necro", "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local",
</span><span style="color:#323232;"> --"post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id", "post_aggregates"."controversy_rank",
</span><span style="color:#323232;"> -- "community_follower"."pending",
</span><span style="color:#323232;"> ("post_saved"."id" IS NOT NULL) AS save,
</span><span style="color:#323232;"> ("post_read"."id" IS NOT NULL) AS read,
</span><span style="color:#323232;"> ("person_block"."id" IS NOT NULL) as block,
</span><span style="color:#323232;"> "post_like"."score",
</span><span style="color:#323232;"> coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"), "post_aggregates"."comments") AS unread
</span><span style="color:#323232;">
</span><span style="color:#323232;">FROM (
</span><span style="color:#323232;"> ((((((((((
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> "post_aggregates"
</span><span style="color:#323232;"> INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_person_ban"
</span><span style="color:#323232;"> ON (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id"))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_follower" ON (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_saved" ON (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_read" ON (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = 3)))
</span><span style="color:#323232;"> LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = 3))
</span><span style="color:#323232;"> )
</span><span style="color:#323232;">WHERE
</span><span style="color:#323232;"> post_aggregates.id IN (
</span><span style="color:#323232;"> SELECT id FROM post_aggregates
</span><span style="color:#323232;"> WHERE "post_aggregates"."creator_id" = 3
</span><span style="color:#323232;"> ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
</span><span style="color:#323232;"> LIMIT 1000
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND
</span><span style="color:#323232;"> (((((((
</span><span style="color:#323232;"> (
</span><span style="color:#323232;"> (("community"."deleted" = false) AND ("post"."deleted" = false))
</span><span style="color:#323232;"> AND ("community"."removed" = false))
</span><span style="color:#323232;"> AND ("post"."removed" = false)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("post_aggregates"."creator_id" = 3)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("post"."nsfw" = false))
</span><span style="color:#323232;"> AND ("community"."nsfw" = false)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("local_user_language"."language_id" IS NOT NULL)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("community_block"."person_id" IS NULL)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;"> AND ("person_block"."person_id" IS NULL)
</span><span style="color:#323232;"> )
</span><span style="color:#323232;">ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."published" DESC
</span><span style="color:#323232;">LIMIT 10
</span><span style="color:#323232;">OFFSET 0
</span><span style="color:#323232;">;
</span>
If it isn’t social hazing, then what is going on here? Why has this issue gone on since May and servers are crashing every day?