Thank you for sharing, I had not come across the auto_explain technique and was manually pulling out SQL from pg_stat_statements and trying to match up to runtime parameters for $1 $2 etc with EXPLAIN
There are always underlying assumptions made by people who support industrial-age work for every human being. Things like... 9 to 5 work schedule (or longer) being compatible with their genetics, year round - winter or summer. Paperwork appreciation, having to fill out job applications often with high amounts of redundancy in the questions and formats. Red tape for getting paychecks and accounting. Dress codes and even uniform requirements. Businessmen preferences for rectangle and high-rise buildings that few other animals from Earth seem to construct or use to build loyal followers.
With generic randomness alone, I suspect that 20%+ of a population on Pale Blue Dot never fit in with what their local society considers perfectly "normal" conformity and biological needs in industrialized world.
I did perform some tests yesterday. DeleteAccount does NOT Delete the communities created by the account. If the account deleted was the moderator, the community will be left with zero moderators. That person's post and comments are overwritten, but a post created in the community by an account other than the deleted one should still function (not sure how deep the testing went on that, still enhancing testing).
featured_community and featured_local could be combine into a unified enum/value field and allowing expansion of features for other uses. Maybe only on post_aggregates to start.
Right now, queries to list post in a community do this:
AND ("post"."nsfw" = $15)) AND ("community"."nsfw" = $16))
It should be rare for a community to flip their nsfw flag. And posts are owned by a community. So why not set the post.nsfw flag to true and rewrite it based on a community flipping value. Instead of every refresh to a page having to query these independent variables.
the use of two entire fields for deleted and removed could be consolidated into a flag with multiple meanings. Right now, WHERE clauses have to include conditions for both, when a smallint, optimized enum, or character could hold values such as: a = open, d = deleted, r = removed, e = deleted + removed, f = spam filtered pending analysis, p = pending mod approval for troubled user, q = pending mod approval for all.
right now, I can’t delete the above comment on either the default or alexandrite UIs!
deleting of a single post or comment is one of the worst-performing SQL problems in lemmy's code right now. an open pull request to fix a bug. On my instance, it is already taking over 5 seconds for PostgreSQL to delete a single post or comment, and I only have 2 months worth of data in the database.
You are speaking as someone who is home, transportation, and water secure. Someone with a marginally less stable life
Exactly. I'm in Arizona right now and it's incredibly hot with no break from the heat. Equipment overheating is a real issue, my development computers and phones have all had problems.
I'm using Linux Ubuntu 22.04.2 desktop Chrome (direct .deb download from Google) on lemmy.ml and I haven't seen it? lemmy.ml has been regularly crashing for me, errors, etc.
I'm inclined to encourage we bite the bullet while data is still relatively small and change delete/removed field into a unified status field, enum or integer or is it a character? I think I've seen code that says= true and and ='t'
EDIT: I created a new posting regarding consolidating some of these fields that yield the same results.
And have some timestamps of deleted, even if that's off on another table. Need to thigh this through some more.
--> crates/db_schema/src/aggregates/site_aggregates.rs:12:35
|
12 | site_aggregates::dsl::site_id.eq(1).first::<Self>(conn).await
| ^^ `schema::site_aggregates::columns::site_id` is not an iterator
|
::: crates/db_schema/src/schema.rs:817:9
|
817 | site_id -> Int4,
| -------
| |
| method `eq` not found for this struct
| doesn't satisfy `_: Iterator`
|
= note: the following trait bounds were not satisfied:
`schema::site_aggregates::columns::site_id: Iterator`
which is required by `&mut schema::site_aggregates::columns::site_id: Iterator`
site_aggregates::table.filter(site_aggregates::site_id.eq(1)).first::<Self>(conn).await
^^ `schema::site_aggregates::columns::site_id` is not an iterator
checking lemmy.ml, https://lemmy.ml/instances it isn't showing as blocked:
Don't see lemmy.ml blocked on the other either:
Pages are hard to read with so many listed...