[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

At this point, the difference is very small, but indeed it is a bit faster to treat post_aggregates as the main table completely. Will push additional commits soon.

Originally posted by sunaurus in #3653

this post was submitted on 18 Jul 2023

0 points (50.0% liked)

Issue Tracker

143 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