[BE] Denormalize community_id into post_aggregates for a 1000x speed-up when loading posts #3653 (github.com)

submitted 1 year ago by issue_tracking_bot@lemm.ee to c/issue_tracker@lemm.ee

3 comments fedilink hide all child comments

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

you are viewing a single comment's thread
view the rest of the comments

[-] issue_tracking_bot@lemm.ee 1 points 1 year ago

New commits contain the following changes

Remove the unused index

Added creator_id to post_aggregates

Made post_aggregates the "main" table for PostQuery

Originally posted by sunaurus in #3653

this post was submitted on 18 Jul 2023

0 points (50.0% liked)

Issue Tracker

169 readers

1 users here now

Welcome to the Lemmy Issue Tracker!

Here you can share your ideas and report issues related to the Lemmy project. We welcome all feedback and suggestions to help us improve the platform.

Guidelines for submitting issues

Please write a single idea or issue per post/comment.
Use the following format for your post title:
- [BE] or [UI] for backend or web-ui issues.
- The type of issue: [Bug], [Feature Request], etc.
If the issue is resolved, please add [Closed] at the start of the comment.

How to contribute

If you want to contribute to the development of Lemmy, please check out our GitHub repository. You can also join our Matrix chat to discuss ideas and issues with the community.

Thank you for your support and contributions to the Lemmy project!

founded 1 year ago

MODERATORS

GodOfThunder@lemm.ee