Denormalize community_id into post_aggregates for a 1000x speed-up when loading posts #3653
Denormalize community_id into post_aggregates for a 1000x speed-up when loading posts #3653

github.com
Denormalize community_id into post_aggregates for a 1000x speed-up when loading posts by sunaurus · Pull Request #3653 · LemmyNet/lemmy

Credit to @phiresky for this idea, originally posted in comments of #2994
This PR adds community_id
to post_aggregates
(& a new index on post_aggregates
) to enable joining community
directly to post_aggregates
when querying posts.
On lemm.ee, this optimization speeds up the query for front page of subscribed posts ~1000x, from several seconds to to just milliseconds. You can check a before/after of query plans here: https://gist.github.com/sunaurus/856e03165bb0c0010505afeebde45230