From 45b1a0d4fb491abe9aef3e70ada27d8bc8d2658e Mon Sep 17 00:00:00 2001 From: phiresky Date: Wed, 5 Jul 2023 17:50:26 +0200 Subject: [PATCH] improve performance of community followers inbox query (#3482) * improve performance of community followers inbox query * nightly format * force woodpecker to retry --------- Co-authored-by: Dessalines --- crates/apub/src/objects/community.rs | 13 ++---- .../protocol/collections/group_followers.rs | 4 +- .../src/community_follower_view.rs | 40 +++++++++++++++---- 3 files changed, 37 insertions(+), 20 deletions(-) diff --git a/crates/apub/src/objects/community.rs b/crates/apub/src/objects/community.rs index 672b25eb..113fa6c0 100644 --- a/crates/apub/src/objects/community.rs +++ b/crates/apub/src/objects/community.rs @@ -14,7 +14,6 @@ use activitypub_federation::{ traits::{Actor, Object}, }; use chrono::NaiveDateTime; -use itertools::Itertools; use lemmy_api_common::{ context::LemmyContext, utils::{generate_featured_url, generate_moderators_url, generate_outbox_url}, @@ -188,17 +187,11 @@ impl ApubCommunity { let id = self.id; let local_site_data = local_site_data_cached(context.pool()).await?; - let follows = CommunityFollowerView::for_community(context.pool(), id).await?; + let follows = CommunityFollowerView::get_community_follower_inboxes(context.pool(), id).await?; + let inboxes: Vec = follows .into_iter() - .filter(|f| !f.follower.local) - .map(|f| { - f.follower - .shared_inbox_url - .unwrap_or(f.follower.inbox_url) - .into() - }) - .unique() + .map(Into::into) .filter(|inbox: &Url| inbox.host_str() != Some(&context.settings().hostname)) // Don't send to blocked instances .filter(|inbox| check_apub_id_valid(inbox, &local_site_data).is_ok()) diff --git a/crates/apub/src/protocol/collections/group_followers.rs b/crates/apub/src/protocol/collections/group_followers.rs index c7df8df2..964bbdf8 100644 --- a/crates/apub/src/protocol/collections/group_followers.rs +++ b/crates/apub/src/protocol/collections/group_followers.rs @@ -22,12 +22,12 @@ impl GroupFollowers { ) -> Result { let community_id = community.id; let community_followers = - CommunityFollowerView::for_community(context.pool(), community_id).await?; + CommunityFollowerView::count_community_followers(context.pool(), community_id).await?; Ok(GroupFollowers { id: generate_followers_url(&community.actor_id)?.into(), r#type: CollectionType::Collection, - total_items: community_followers.len() as i32, + total_items: community_followers as i32, items: vec![], }) } diff --git a/crates/db_views_actor/src/community_follower_view.rs b/crates/db_views_actor/src/community_follower_view.rs index ccc7ae70..11baddc0 100644 --- a/crates/db_views_actor/src/community_follower_view.rs +++ b/crates/db_views_actor/src/community_follower_view.rs @@ -1,8 +1,14 @@ use crate::structs::CommunityFollowerView; -use diesel::{result::Error, ExpressionMethods, QueryDsl}; +use diesel::{ + dsl::{count_star, not}, + result::Error, + sql_function, + ExpressionMethods, + QueryDsl, +}; use diesel_async::RunQueryDsl; use lemmy_db_schema::{ - newtypes::{CommunityId, PersonId}, + newtypes::{CommunityId, DbUrl, PersonId}, schema::{community, community_follower, person}, source::{community::Community, person::Person}, traits::JoinView, @@ -11,19 +17,37 @@ use lemmy_db_schema::{ type CommunityFollowerViewTuple = (Community, Person); +sql_function!(fn coalesce(x: diesel::sql_types::Nullable, y: diesel::sql_types::Text) -> diesel::sql_types::Text); + impl CommunityFollowerView { - pub async fn for_community(pool: &DbPool, community_id: CommunityId) -> Result, Error> { + pub async fn get_community_follower_inboxes( + pool: &DbPool, + community_id: CommunityId, + ) -> Result, Error> { let conn = &mut get_conn(pool).await?; let res = community_follower::table - .inner_join(community::table) + .filter(community_follower::community_id.eq(community_id)) + .filter(not(person::local)) .inner_join(person::table) - .select((community::all_columns, person::all_columns)) + .select(coalesce(person::shared_inbox_url, person::inbox_url)) + .distinct() + .load::(conn) + .await?; + + Ok(res) + } + pub async fn count_community_followers( + pool: &DbPool, + community_id: CommunityId, + ) -> Result { + let conn = &mut get_conn(pool).await?; + let res = community_follower::table .filter(community_follower::community_id.eq(community_id)) - .order_by(community::title) - .load::(conn) + .select(count_star()) + .first::(conn) .await?; - Ok(res.into_iter().map(Self::from_tuple).collect()) + Ok(res) } pub async fn for_person(pool: &DbPool, person_id: PersonId) -> Result, Error> { -- 2.44.1