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

  • issue_tracking_bot@lemm.eeOPB
    link
    fedilink
    arrow-up
    1
    ·
    1 year ago

    New commits contain the following changes

    1. Remove the unused index
    2. Added creator_id to post_aggregates
    3. Made post_aggregates the “main” table for PostQuery

    Originally posted by sunaurus in #3653