LemmyNet
Separate sort types with time spans/periods (Top day/week/year becomes top) and have separate time span
Requirements
- [X] Is this a feature request? For questions or discussions use https://lemmy.ml/c/lemmy_support
- [X] Did you check to see if this issue already exists?
- [X] Is this only a feature request? Do not put multiple feature requests in one issue.
- [X] Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.
Is your proposal related to a problem?
I want to have time spans apply to other sort types too, like top comments/controversial/scaled/ of the day/week/year/..
Making more separate sort types would increase the amount of sort types by huge margin. And new period/time span would increase by the amount of sorts it would be applied to.
Describe the solution you'd like.
Separate the sortypes and their time span/duration/period (not sure yet on the naming).
The only problem this could have is that some sort types are irrelevant to the timespan, e.g new by day new by month. (All though it could be used to limit the amount of posts that is searched?)
Describe alternatives you've considered.
For each sort type that is applicable add all its time variants. this would increase the sort types from like 13 to like 40 but UI could show it as only top/new/controversial/scaled/hot/active/new comments/top comments and have sub sections for the relevant sort types that show then (day / week / year / ...)
Additional context
No response
This is deceptively simple and would be easy on a tiny instance but on the larger instances computing these is very expensive and potentially can't be done in real time, or may cause too high server load. So it would need close investigation on how to actually build it esp. wrt. db indexes.
I like this idea, esp as an Option<PgInterval> : https://docs.diesel.rs/2.0.x/diesel/pg/data_types/struct.PgInterval.html). Then we could get rid of all the different Top sorts.
It def makes sense for these sorts: Controversial, New, Old, Top, NewComments, MostComments.
There aren't any indexes currently for the time-span limited queries, just the top all one:
"idx_post_aggregates_community_score" btree (community_id, featured_local DESC, score DESC, published DESC)
We could always add indexes later on based on performance.
There aren't any indexes currently for the time-span limited queries, just the top all one:
Yes, and they are impossible to directly index due to the sliding time window. It's somewhat surprising that the top-6-months even still works on larger instances, idk what query plan it uses for those or if it's still fast enough to scan through all posts in 6 months to compute the result. Or alternatively it scans top all time and hopes that most top all time posts are recent.
I think whatever heuristic it uses is likely to fall apart in the future
An easy one is hot/scaled sorting, if you only show posts with a ranking > 0 I think that limits it to 7 days
So any time range 7 days or shorter for hot/scaled should include that ranking filter in the query to use that index.
An easy one is hot/scaled sorting, if you only show posts with a ranking > 0 I think that limits it to 7 days
So any time range 7 days or shorter for hot/scaled should include that ranking filter in the query to use that index.
Also for performance for the other sort methods, I think just limiting the time range will keep the number of results small enough that it'll still perform well. Like the drop-down would only have options for 1 day and shorter. (Maybe depending on which sort method is used, hot could easily go up to 7 days as said above)
As far as creating a DB index for the sliding time window to support larger time ranges, I guess you could add index(year, month, score) for the posts, which makes it kinda like partitioned.