Addressing slow profile queries. #2777 #2830

dessalines · 2023-04-24T02:37:14Z

This is an attempt to work through a few of the issues dealing with slow profile queries. It doesn't solve all the issues, because the post_view and comment_view queries are unavoidably terrible... and I can't get them any better than ~60ms on average. They really need the eyes of a SQL expert to speed up past that.

I'll address the main changes in the comments.

crates/db_schema/src/impls/community.rs

dessalines · 2023-04-24T02:39:42Z

src/scheduled_tasks.rs

@@ -122,6 +122,11 @@ fn update_banned_when_expired(conn: &mut PgConnection) {
  sql_query(update_ban_expires_stmt)
    .execute(conn)
    .expect("update banned when expires");
+
+  let delete_community_ban_expires_stmt = "delete from community_person_ban where expires < now()";


Rather than checking the community_person_ban.expires column every time, I correctly delete the rows which are expired anyway in this scheduled job.

Makes sense, you can do the same for site bans. Btw why are queries in this file all written as plain strings and not using dsl?

Good call, I fixed the ones that I could.

migrations/2023-04-23-164732_add_person_details_indexes/up.sql

docker/Dockerfile

crates/db_views_actor/src/comment_reply_view.rs

crates/db_views/src/post_view.rs

crates/db_schema/src/impls/community.rs

crates/db_views/src/comment_view.rs

Nutomic · 2023-04-24T20:18:24Z

docker/docker_update.sh

@@ -3,4 +3,4 @@ set -e

 mkdir -p volumes/pictrs
 sudo chown -R 991:991 volumes/pictrs
-sudo docker-compose up -d --build
+sudo docker compose up -d --build


Looks like a mistake.

Docker is now bundling compose , and this is the new preferred way anyway.

dessalines requested a review from Nutomic as a code owner April 24, 2023 02:37