3 hours later... I put it into code and am experimenting with it. Some proof of concept results: https://github.com/LemmyNet/lemmy/files/12373819/auto_explain_list_post_community_0_18_4_dullbananas_with_inclusion_run0a.txt

Good results with this approach. I hadn't considered the RANK OVER PARTITION BY criteria_a values and it works like a champ. It moves the ORDER BY into the realm of focus (criteria_a) and performance seems decent enough... and it isn't difficult to read the short statement.

SELECT COUNT(ranked_recency.*) AS post_row_count
FROM
  (
     SELECT id, post_id, community_id, published,
        rank() OVER (
           PARTITION BY community_id
           ORDER BY published DESC, id DESC
           )
     FROM post_aggregates) ranked_recency
WHERE rank <= 1000
;

Gives me the expected results over the 5+ million test rows I ran it against.

If you could elaborate on your idea of TOP, please do. I'm hoping there might be a way to wall the LIMIT 1000 into the inner query and not have the outer query need to WHERE filter rank on so many results?

What problem are you trying to solve?

Reproducible regular server crashes from queries taking tens of seconds long because the whole logic is based on no WHERE clause that has any meat to it. The server overloads in the field have been going on every single day that I've been here testing the big servers since May 2023.

If I want to look through 1000 posts in a community, I probably want to look at more than 1000.

I'm well aware of the push back. Everyone chimes in saying they want counting to be real time, the developers seem to avoid caching at all cost, and out of desperation - I'm trying to build some kind of basic sanity logic into the system so it doesn't plow through 5 million rows to do a LIMIT 10 query.

Right now Lemmy works perfectly fine with no personalization. Anonymous users - it works great. If you want to read a million posts, it works great. Start blocking specific users, start adding in NSFW filters, cherry-picking a blend of communities, etc. and the problems show up. The ORM logic is difficult to follow, based on massive JOIN of every field there is in many tables, and at certain data thresholds with per-account preferences engaged - it goes off the rails into the pile of over 1 million posts (taking 40 seconds to list page = 1 of LIMIT 20 posts for even a single community).

The programmers who built the code for over 4 years don't seem to think it is an urgent problem. So I'm chipping in. I personally have never worked with this ORM and I find it painful compared to the hand-crafted SQL I've done on major projects. I'm doing this because I feel like nobody else has for months.

FYI: !lemmydev@lemm.ee

ok, experimenting on a massive test data set of over 5 million posts... this PostgreSQL works pretty well

SELECT COUNT(ranked_recency.*) AS post_row_count
FROM
  (
     SELECT id, community_id, published,
        rank() OVER (
           PARTITION BY community_id
           ORDER BY published DESC, id DESC
           )
     FROM post_aggregates) ranked_recency
WHERE rank <= 1000
;

This limits any one community to 1000 posts, picking the most recent created posts. This gives a way to age out older data in very active communities without removing any posts at all for small communities.

Ok, I'm doing some reading: https://medium.com/@amulya349/how-to-select-top-n-rows-from-each-category-in-postgresql-39e3cfebb020

That is, give a “next page” token

There's already a pull request on changing paging.

My focus is a very hard wall on performance, scale. There is way too much potential for data to run into the full post table as things are now.

I'll say this: a lot of discussion seems to take place on Matrix chat that doesn't make it into GitHub code comments as to why specific changes are made.

It used to be you could see the actual content of deleted comments and it was at the discretion of the client to show or not show them. The newcomers from Reddit (June) seemed to not like that people could read content of deleted comments, so I think changes were made for that reason.

With federation, it really isn't reasonable to expect content copies to all be deleted. So it's a complex issue.

It can't be a simple as a date range, because we want to be inclusive for smaller communities.

1. paging is a consideration. 1000 posts per community would allow 10 pages of 20 posts.
2. small communities are defined to be 1000 or less posts, regardless of age
3. large communities would focus on recency, the 1000 post would be recently created or edited
4. Edited can be more tricky, either skip for now or focus on how to limit some kind of mass edit from taking over newly published

Also a good time to be reminded that the published date isn't reliable for a couple reasons:

1. problems in the field have been shown with incoming federation data having future published dates on content. kbin in an easy example, but it isn't limited to kbin.
2. federation can lag due to server overload and problems paths between specific servers, ISP issues, etc. It is rather common to have received a post hours after the published date. Lemmy currently does not track the 'received' date of content.

With 0.18.2, 0.18.3 there were changes in the behavior of comment sorting and delete / remove behavior. It is entirely possible that behavior changed, intentional or otherwise.

There is a !test@lemmy.ml community where you could create comments, do some screen shots before and after delete.

An Instance is just another word for 'server' in lemmy terminology. HDTV is a classic form of media that doesn't involve TCP/IP to watch films and other video content.

i'm curious about alternate front-end / API clients....