Lemmy Server optimization of PostgreSQL by focusing on community ownership of a post - and visibility of a post in a single control field. Also local flag transition to instance_id field, with value 1

Right now querying posts has logic like this:

WHERE (((((((((("community"."removed" = $9) AND ("community"."deleted" = $10)) AND ("post"."removed" = $11)) AND ("post"."deleted" = $12)) AND (("community"."hidden" = $13)

Note that a community can be hidden or deleted, separate fields. And it also has logic to see if the creator of the post is banned in the community:

LEFT OUTER JOIN "community_person_ban" ON (("post"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post"."creator_id"))

And there is both a deleted boolean (end-user delete) and removed boolean (moderator removed) on a post.

Much of this also applies to comments. Which are also owned by the post, which are also owned by the community.

3 comments

the use of two entire fields for deleted and removed could be consolidated into a flag with multiple meanings. Right now, WHERE clauses have to include conditions for both, when a smallint, optimized enum, or character could hold values such as: a = open, d = deleted, r = removed, e = deleted + removed, f = spam filtered pending analysis, p = pending mod approval for troubled user, q = pending mod approval for all.
Right now, queries to list post in a community do this:

AND ("post"."nsfw" = $15)) AND ("community"."nsfw" = $16))

It should be rare for a community to flip their nsfw flag. And posts are owned by a community. So why not set the post.nsfw flag to true and rewrite it based on a community flipping value. Instead of every refresh to a page having to query these independent variables.
This is a difficult change to make, and I'm likely to focus more on testing code at this point.