just hitting the first page, limit 20, using lemmy-ui
http://localhost:1234/c/zy_photography
default sort of "Active"
taking over 26 full seconds, each refresh.
463K posts in community, 6.18M posts in database
12K communities
block lists should be entirely empty
this is while logged-in

2023-08-16 12:46:44.049 MST [1520415] lemmy@lemmy_alpha LOG: duration: 26064.185 ms execute s22174:

SELECT "post"."id", "post"."name", "post"."url", "post"."body", "post"."creator_id", "post"."community_id", "post"."removed", "post"."locked", "post"."published", "post"."updated", "post"."deleted", "post"."nsfw", "post"."embed_title", "post"."embed_description", "post"."thumbnail_url", "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local", "person"."id", "person"."name", "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated", "person"."actor_id", "person"."bio", "person"."local", "person"."private_key", "person"."public_key", "person"."last_refreshed_at", "person"."banner", "person"."deleted", "person"."inbox_url", "person"."shared_inbox_url", "person"."matrix_user_id", "person"."admin", "person"."bot_account", "person"."ban_expires", "person"."instance_id", "community"."id", "community"."name", "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted", "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner", "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods", "community"."instance_id", "community"."moderators_url", "community"."featured_url", ("community_person_ban"."id" IS NOT NULL), "post_aggregates"."id", "post_aggregates"."post_id", "post_aggregates"."comments", "post_aggregates"."score", "post_aggregates"."upvotes", "post_aggregates"."downvotes", "post_aggregates"."published", "post_aggregates"."newest_comment_time_necro", "post_aggregates"."newest_comment_time", "post_aggregates"."featured_community", "post_aggregates"."featured_local", "post_aggregates"."hot_rank", "post_aggregates"."hot_rank_active", "post_aggregates"."community_id", "post_aggregates"."creator_id", "post_aggregates"."controversy_rank", "community_follower"."pending", ("post_saved"."id" IS NOT NULL), ("post_read"."id" IS NOT NULL), ("person_block"."id" IS NOT NULL), "post_like"."score", coalesce(("post_aggregates"."comments" - "person_post_aggregates"."read_comments"), "post_aggregates"."comments")

FROM (((((((((((((
"post_aggregates" INNER JOIN "person" ON ("post_aggregates"."creator_id" = "person"."id")) INNER JOIN "community" ON ("post_aggregates"."community_id" = "community"."id")) LEFT OUTER JOIN "community_person_ban" ON (("post_aggregates"."community_id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "post_aggregates"."creator_id")))
INNER JOIN "post" ON ("post_aggregates"."post_id" = "post"."id")) LEFT OUTER JOIN "community_follower" ON (("post_aggregates"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $1)))
LEFT OUTER JOIN "community_moderator" ON (("post"."community_id" = "community_moderator"."community_id") AND ("community_moderator"."person_id" = $2)))
LEFT OUTER JOIN "post_saved" ON (("post_aggregates"."post_id" = "post_saved"."post_id") AND ("post_saved"."person_id" = $3)))
LEFT OUTER JOIN "post_read" ON (("post_aggregates"."post_id" = "post_read"."post_id") AND ("post_read"."person_id" = $4)))
LEFT OUTER JOIN "person_block" ON (("post_aggregates"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $5)))
LEFT OUTER JOIN "post_like" ON (("post_aggregates"."post_id" = "post_like"."post_id") AND ("post_like"."person_id" = $6)))
LEFT OUTER JOIN "person_post_aggregates" ON (("post_aggregates"."post_id" = "person_post_aggregates"."post_id") AND ("person_post_aggregates"."person_id" = $7)))
LEFT OUTER JOIN "community_block" ON (("post_aggregates"."community_id" = "community_block"."community_id") AND ("community_block"."person_id" = $8)))
LEFT OUTER JOIN "local_user_language" ON (("post"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = $9)))

WHERE ((((((((("community"."removed" = $10) AND ("post"."removed" = $11)) AND ("post_aggregates"."community_id" = $12)) AND (("community"."hidden" = $13) OR ("community_follower"."person_id" = $14))) AND ("post"."nsfw" = $15)) AND ("community"."nsfw" = $16)) AND ("local_user_language"."language_id" IS NOT NULL)) AND ("community_block"."person_id" IS NULL)) AND ("person_block"."person_id" IS NULL))

ORDER BY "post_aggregates"."featured_community" DESC , "post_aggregates"."hot_rank_active" DESC , "post_aggregates"."published" DESC LIMIT $17 OFFSET $18

In April and May, Lemmy had a great brand... but server crashes and then lemmy.ml closed sign-up.... sort of confusing people and then Lemmy.world became the brand, then it too has had to close home page many times and constant crashes....

The SQL performance issues that were in lemmy for years really - became trouble once data was introduced.

I don't even know how many times people have come to sing-up and heard about Lemmy to find it slow, crashing, errroring.

Social hazing, rock star glamour attitude among the audience who keeps using Lemmy... but these odd trends in 2023 with social media aren't unique to lemmy, are they?

Threads, Twitter to X, Reddit API change, Lemmy crashing and not really caring about the crashes - and Redis or Memcache to mitigate it.... it's all such an odd year. It's as if everyone stirred the pot with social media but no place to really land upon that isn't crashing or in some form of Elon Musk kind of chaos.

I remember the rise of Reddit, the rise of Gmail - and it just felt like the growth was being dealt with. In 2023, Twitter seems to be leading the entire audience into accepting a kind of crash bad experience and 'stick around, no matter how bad it is' that Reddit users seem to have accepted.

I don't want to be a Lemmy developer... I want the people who know Rust Diesel and such to actually make it work - or choose something else that does work. I want Lemmy to actually not crash all the time and at least be where Reddit was in 2008 when there were just a few programmers doing it.

The SQL statements and server crashes in Lemmy speak for them self, just like the chaos of Reddit and Elon Musk Twitter speaks for itself... but a lot of people accept it. The audience is not asking for stability.

Strange times in 2023!

So this is currently a thought experiment or brainstorming, whatever...

When a community is local, the "home server" for community, that means that the federation is sent out to subscribed servers.

Now it also means that the total subscribe list is only know to that one server... As least I think it works that way, that the subscribe of "home" server is the complete list and everyone else has partial lists.

There is a lot of the structure of a community that offers some data opportunities to Lemmy. For one, a community object can be edited by any of the moderators. and any moderator can Lock a post, Remove a post, etc.

Reddit has a not-often-used Wiki feature, that even basically means more than one person could edit content too. Other than the sidebar of a community, I don't think Lemmy has any concept of multiple people being able to edit a post or a comment. But again, multiple mods can feature or lock a post... so there is some concept of multiple-actors on data.

I think when you get into creating flair / tags and even playlists (multi-community lists), you want multiple people to be able to edit data. Which in Reddit was the Wiki structure.

i know some of it is kind of a waste of time.. because people tend to avoid Wiki and highly favor posting the same repeat questions and content over and over, reposts. But a man can dream, can't he :)

cross-posted from: https://campfyre.nickwebster.dev/post/89316

I was getting close to hitting the end of my free object storage so there was time pressure involved haha.

Seems to work but I haven't tested it too much. Currently running on my instance.

I did get permission to share this private message. For the record, I loath having public Lemmy topics being discussed in private messages. I created this community to get all this OUT in the OPEN.

I already made my decision on NOT changing privacy policy. But now I see that the very idea of having more than one "Subscribed" list for an end-user is raising threats of defederation!

Help! I want to divorce ReadFn from ListFN - bypassing the Queries mutual closure behavior so I can better isolate some logic. My need is to get an independent ListFn...

fn queries_<'a>() -> Queries<
  impl ReadFn<'a, PostView, (PostId, Option, bool)>,
  impl ListFn<'a, PostView, PostQuery<'a>>,
> {

Context: https://github.com/LemmyNet/lemmy/blob/main/crates/db_views/src/post_view.rs

For sake of clarity... I'm not wanting to break the whole Queries joined closure marriage project-wide, just this one source file I want to be able to copy/paste this code twice and have just a single closure for ListFn.

Thank you.

Donald Trump faces four indictments, 91 criminal charges and hundreds of years of maximum prison time combined.

This is a former president who — according to the latest grand jury indictment in Fulton County, Georgia — participated in a “criminal enterprise.” Trump and 18 co-defendants are accused of trying “to unlawfully change the outcome of the election” in 2020. Among the 13 felony charges he faces is one count of violating the Georgia RICO (Racketeer Influenced and Corrupt Organizations) Act and two counts of conspiracy to commit forgery.

Most of those charges are related to a fake elector scheme by the Trump campaign in which a slate of “alternate” electors in Georgia would cast electoral votes for Trump instead of Joe Biden. The president of the most powerful democracy in the world allegedly tried to steal an election.

We can’t say it often enough: This is serious. Americans cannot shrug this off or normalize it, no matter how many times Trump gets indicted. Yet it feels like business as usual. Not only is Trump favored to win the GOP presidential nomination, he’s also neck and neck with President Biden in the 2024 general election, according to a July poll by the New York Times/Siena Poll.

MORE THAN A CULT

Trump’s support cannot only be explained as the product of the cult-like power he has over his MAGA base, which accounts for roughly 40% of Republican voters who believe those indictments are nothing but a conspiracy against him.

more: https://www.miamiherald.com/opinion/editorials/article278265068.html

I'm trying to wrangle in and get 'back to basics' with Lemmy's Diesel code and at every turn I run into not understanding the complexity of the Rust code.

You may want to do a GitHub checkout of this branch if you want to see what I'm attempting: https://github.com/LemmyNet/lemmy/pull/3865

I'm basing my experiments off the code in that pull request, which links to this branch on this repository: https://github.com/dullbananas/lemmy/tree/post-view-same-joins

Right now Lemmy's Diesel code spins up many SQL table joins and for an anonymous user it just passes a -1 user id to all the joins - and it really makes for difficult to read SQL statements. So I decided to experiment with removing as much logic as I could to get the bare-bones behavior on generating the desired SQL statement....

I copied/pasted the queries function/method and gave it a new name, kept removing as much as I could see that referenced the user being logged-in vs. anonymous, and got to this point:

fn queries_anonymous<'a>() -> Queries<
  impl ReadFn<'a, PostView, (PostId, Option, bool)>,
  impl ListFn<'a, PostView, PostQuery<'a>>,
> {
  let is_creator_banned_from_community = exists(
    community_person_ban::table.filter(
      post_aggregates::community_id
        .eq(community_person_ban::community_id)
        .and(community_person_ban::person_id.eq(post_aggregates::creator_id)),
    ),
  );

// how do we eliminate these next 3 assignments, this is anonymous user, not needed

  let is_saved = |person_id_join| {
    exists(
      post_saved::table.filter(
        post_aggregates::post_id
          .eq(post_saved::post_id)
          .and(post_saved::person_id.eq(person_id_join)),
      ),
    )
  };

  let is_read = |person_id_join| {
    exists(
      post_read::table.filter(
        post_aggregates::post_id
          .eq(post_read::post_id)
          .and(post_read::person_id.eq(person_id_join)),
      ),
    )
  };

  let is_creator_blocked = |person_id_join| {
    exists(
      person_block::table.filter(
        post_aggregates::creator_id
          .eq(person_block::target_id)
          .and(person_block::person_id.eq(person_id_join)),
      ),
    )
  };

  let all_joins = move |query: post_aggregates::BoxedQuery<'a, Pg>, my_person_id: Option| {
    // The left join below will return None in this case
    let person_id_join = my_person_id.unwrap_or(PersonId(-1));

    query
      .inner_join(person::table)
      .inner_join(community::table)
      .inner_join(post::table)
// how do we eliminate these next 3 joins that are user/person references?
      .left_join(
        community_follower::table.on(
          post_aggregates::community_id
            .eq(community_follower::community_id)
        ),
      )
      .left_join(
        community_moderator::table.on(
          post::community_id
            .eq(community_moderator::community_id)
        ),
      )
      .left_join(
        post_like::table.on(
          post_aggregates::post_id
            .eq(post_like::post_id)
        ),
      )
      .left_join(
        person_post_aggregates::table.on(
          post_aggregates::post_id
            .eq(person_post_aggregates::post_id)
        ),
      )
      .select((
        post::all_columns,
        person::all_columns,
        community::all_columns,
        is_creator_banned_from_community,
        post_aggregates::all_columns,
        CommunityFollower::select_subscribed_type(),
// how do we eliminate these next 3 for anonymous?
        is_saved(person_id_join),
        is_read(person_id_join),
        is_creator_blocked(person_id_join),
        post_like::score.nullable(),
        coalesce(
          post_aggregates::comments.nullable() - person_post_aggregates::read_comments.nullable(),
          post_aggregates::comments,
        ),
      ))
  };

  let read =
    move |mut conn: DbConn<'a>,
          (post_id, my_person_id, is_mod_or_admin): (PostId, Option, bool)| async move {

      let mut query = all_joins(
        post_aggregates::table
          .filter(post_aggregates::post_id.eq(post_id))
          .into_boxed(),
        my_person_id,
      );

        query = query
          .filter(community::removed.eq(false))
          .filter(post::removed.eq(false))
          ;

      query.first::(&mut conn).await
    };

  let list = move |mut conn: DbConn<'a>, options: PostQuery<'a>| async move {
    let person_id = options.local_user.map(|l| l.person.id);

    let mut query = all_joins(post_aggregates::table.into_boxed(), person_id);


      query = query
        .filter(community::deleted.eq(false))
        .filter(post::deleted.eq(false));


    // every SELECT has to labor away on removed filtering
      query = query
        .filter(community::removed.eq(false))
        .filter(post::removed.eq(false));

    if options.community_id.is_none() {
      query = query.then_order_by(post_aggregates::featured_local.desc());
    } else if let Some(community_id) = options.community_id {
      query = query
        .filter(post_aggregates::community_id.eq(community_id))
        .then_order_by(post_aggregates::featured_community.desc());
    }

    if let Some(creator_id) = options.creator_id {
      query = query.filter(post_aggregates::creator_id.eq(creator_id));
    }


    if let Some(url_search) = options.url_search {
      query = query.filter(post::url.eq(url_search));
    }

    if let Some(search_term) = options.search_term {
      let searcher = fuzzy_search(&search_term);
      query = query.filter(
        post::name
          .ilike(searcher.clone())
          .or(post::body.ilike(searcher)),
      );
    }

      query = query
        .filter(post::nsfw.eq(false))
        .filter(community::nsfw.eq(false));


    query = match options.sort.unwrap_or(SortType::Hot) {
      SortType::Active => query
        .then_order_by(post_aggregates::hot_rank_active.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::Hot => query
        .then_order_by(post_aggregates::hot_rank.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::Controversial => query.then_order_by(post_aggregates::controversy_rank.desc()),
      SortType::New => query.then_order_by(post_aggregates::published.desc()),
      SortType::Old => query.then_order_by(post_aggregates::published.asc()),
      SortType::NewComments => query.then_order_by(post_aggregates::newest_comment_time.desc()),
      SortType::MostComments => query
        .then_order_by(post_aggregates::comments.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopAll => query
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopYear => query
        .filter(post_aggregates::published.gt(now - 1.years()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopMonth => query
        .filter(post_aggregates::published.gt(now - 1.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopWeek => query
        .filter(post_aggregates::published.gt(now - 1.weeks()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopDay => query
        .filter(post_aggregates::published.gt(now - 1.days()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopHour => query
        .filter(post_aggregates::published.gt(now - 1.hours()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopSixHour => query
        .filter(post_aggregates::published.gt(now - 6.hours()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopTwelveHour => query
        .filter(post_aggregates::published.gt(now - 12.hours()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopThreeMonths => query
        .filter(post_aggregates::published.gt(now - 3.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopSixMonths => query
        .filter(post_aggregates::published.gt(now - 6.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopNineMonths => query
        .filter(post_aggregates::published.gt(now - 9.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
    };

    let (limit, offset) = limit_and_offset(options.page, options.limit)?;

    query = query.limit(limit).offset(offset);

    debug!("Post View Query: {:?}", debug_query::(&query));

    query.load::(&mut conn).await
  };

  Queries::new(read, list)
}

This compiles, but I can not progress further. There are 3 joins more that aren't really needed for an anonymous user... but the interdependent Rust object structures I can't unravel enough to remove them from the code.

For example, Lemmy allows you to "save" a post, but an anonymous user doesn't have that ability - so how can I remove the JOIN + select related to that while still satisfying the object requirements? I even tried creating a variation of PostViewTuple object without one of the bool fields, but it all cascades into 50 lines of compiler errors. Thank you.