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

[email protected]

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

submitted 1 year ago by [email protected] to c/[email protected]

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

top 3 comments

sorted by: hot top controversial new old

[–] [email protected] 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

[–] [email protected] 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

[–] [email protected] 1 points 1 year ago

post_aggregates should be the "main" table that the others are joined to, and primarily filter from.

Is there a specific reason the post_aggregates and post table are spilt? In general I think it would make sense to have everything in the same table. (same for comments /person...). 1:1 relations aren't that great in postgresql in general imo, tables with a huge amount of columns work pretty well. It would increase contention a bit because every change in votes would lock the post row for a bit (just writes, not reads) but I don't think that justifies the split. Or is there other reasons for the spilt?

Originally posted by phiresky in #3653