I’m studying: LEFT OUTER JOIN “community_person_ban” ON ((“post_aggregates”.“community_id” = “community_person_ban”.“community_id”) AND (“community_person_ban”.“person_id” = “post_aggregates”.“creator_id”))) … and the SELECT field has (“community_person_ban”.“id” IS NOT NULL)

I’m concerned a ban-happy community ends up having thousands of rows of people they have banned, and every SELECT to list posts has to process that.

Revised Rust code…

HTTP request{http.method=GET http.scheme=“http” http.host=lemmy-alpha:8541 http.target=/api/v3/post/listanon otel.kind=“server” request_id=65f254ad-0569-4126-823a-620477fa73bc}:list_posts_anonymous{data=Query(GetPosts { type_: None, sort: None, page: None, limit: None, community_id: None, community_name: None, saved_only: None, liked_only: None, disliked_only: None, moderator_view: None, auth: None })}:

lemmy_db_views::post_view_anonymous: Post View Anon Query:

Query { sql: “SELECT "post"."id", "post"."name", "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", "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local", "person"."id", "person"."name", "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated", "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", "person"."bot_account", "person"."ban_expires", "person"."instance_id", "community"."id", "community"."name", "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted", "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner", "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods", "community"."instance_id", "community"."moderators_url", "community"."featured_url", "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes", "post_aggregates"."downvotes", "post_aggregates"."published", "post_aggregates"."newest_comment_time_necro", "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local", "post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id", "post_aggregates"."controversy_rank" FROM ((("post_aggregates" INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id")) INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id")) INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")) WHERE (((((("community"."deleted" = $1) AND ("post"."deleted" = $2)) AND ("community"."removed" = $3)) AND ("post"."removed" = $4)) AND ("post"."nsfw" = $5)) AND ("community"."nsfw" = $6)) ORDER BY "post_aggregates"."featured_local" DESC , "post_aggregates"."hot_rank" DESC , "post_aggregates"."published" DESC LIMIT $7 OFFSET $8”

, binds: [false, false, false, false, false, false, 10, 0] }

I think 8 is a concern for Lemmy!

FROM (((((((((((((“post_aggregates”
INNER JOIN “person” ON (“post_aggregates”.“creator_id” = “person”.“id”))
INNER JOIN “community” ON (“post_aggregates”.“community_id” = “community”.“id”))
LEFT OUTER JOIN “community_person_ban” ON ((“post_aggregates”.“community_id” = “community_person_ban”.“community_id”) AND (“community_person_ban”.“person_id” = “post_aggregates”.“creator_id”))) INNER JOIN “post” ON (“post_aggregates”.“post_id” = “post”.“id”))
LEFT OUTER JOIN “community_follower” ON ((“post_aggregates”.“community_id” = “community_follower”.“community_id”) AND (“community_follower”.“person_id” = $1)))
LEFT OUTER JOIN “community_moderator” ON ((“post”.“community_id” = “community_moderator”.“community_id”) AND (“community_moderator”.“person_id” = $2)))
LEFT OUTER JOIN “post_saved” ON ((“post_aggregates”.“post_id” = “post_saved”.“post_id”) AND (“post_saved”.“person_id” = $3)))
LEFT OUTER JOIN “post_read” ON ((“post_aggregates”.“post_id” = “post_read”.“post_id”) AND (“post_read”.“person_id” = $4)))
LEFT OUTER JOIN “person_block” ON ((“post_aggregates”.“creator_id” = “person_block”.“target_id”) AND (“person_block”.“person_id” = $5)))
LEFT OUTER JOIN “post_like” ON ((“post_aggregates”.“post_id” = “post_like”.“post_id”) AND (“post_like”.“person_id” = $6)))
LEFT OUTER JOIN “person_post_aggregates” ON ((“post_aggregates”.“post_id” = “person_post_aggregates”.“post_id”) AND (“person_post_aggregates”.“person_id” = $7)))
LEFT OUTER JOIN “community_block” ON ((“post_aggregates”.“community_id” = “community_block”.“community_id”) AND (“community_block”.“person_id” = $8)))
LEFT OUTER JOIN “local_user_language” ON ((“post”.“language_id” = “local_user_language”.“language_id”) AND (“local_user_language”.“local_user_id” = $9)))
WHERE

I’m thinking for Lemmy…

SET geqo = on;
SET geqo_threshold = 12;

SET from_collapse_limit = 15;
SET join_collapse_limit = 15;