From 7a97fc370bee9e81e9d7b39db491d5c8c27f36ba Mon Sep 17 00:00:00 2001 From: Dessalines Date: Thu, 7 Jan 2021 16:22:17 -0500 Subject: [PATCH] Adding stickied to post_aggregates. - Added more indexes to account for sorting by stickied first. - Changed all order bys in the diesel views to use post_aggregates. --- .../src/aggregates/post_aggregates.rs | 1 + lemmy_db_schema/src/schema.rs | 1 + lemmy_db_views/src/post_view.rs | 7 +++--- .../down.sql | 4 +++- .../up.sql | 22 ++++++++++++++++++- .../down.sql | 7 ++++-- .../up.sql | 12 ++++++---- .../generate_reports.sh | 16 ++++++++++++-- .../views_to_diesel_migration/timings-1.out | 9 -------- 9 files changed, 57 insertions(+), 22 deletions(-) delete mode 100644 query_testing/views_to_diesel_migration/timings-1.out diff --git a/lemmy_db_queries/src/aggregates/post_aggregates.rs b/lemmy_db_queries/src/aggregates/post_aggregates.rs index 6c1dbed2..d5f78bf1 100644 --- a/lemmy_db_queries/src/aggregates/post_aggregates.rs +++ b/lemmy_db_queries/src/aggregates/post_aggregates.rs @@ -11,6 +11,7 @@ pub struct PostAggregates { pub score: i64, pub upvotes: i64, pub downvotes: i64, + pub stickied: bool, pub published: chrono::NaiveDateTime, pub newest_comment_time: chrono::NaiveDateTime, } diff --git a/lemmy_db_schema/src/schema.rs b/lemmy_db_schema/src/schema.rs index fa5d8c21..bbc2e7b8 100644 --- a/lemmy_db_schema/src/schema.rs +++ b/lemmy_db_schema/src/schema.rs @@ -282,6 +282,7 @@ table! { score -> Int8, upvotes -> Int8, downvotes -> Int8, + stickied -> Bool, published -> Timestamp, newest_comment_time -> Timestamp, } diff --git a/lemmy_db_views/src/post_view.rs b/lemmy_db_views/src/post_view.rs index 7b88cfca..2f82f8fe 100644 --- a/lemmy_db_views/src/post_view.rs +++ b/lemmy_db_views/src/post_view.rs @@ -302,14 +302,14 @@ impl<'a> PostQueryBuilder<'a> { if let Some(community_id) = self.community_id { query = query .filter(post::community_id.eq(community_id)) - .then_order_by(post::stickied.desc()); + .then_order_by(post_aggregates::stickied.desc()); } if let Some(community_name) = self.community_name { query = query .filter(community::name.eq(community_name)) .filter(community::local.eq(true)) - .then_order_by(post::stickied.desc()); + .then_order_by(post_aggregates::stickied.desc()); } if let Some(url_search) = self.url_search { @@ -354,7 +354,7 @@ impl<'a> PostQueryBuilder<'a> { SortType::Hot => query .then_order_by(hot_rank(post_aggregates::score, post_aggregates::published).desc()) .then_order_by(post_aggregates::published.desc()), - SortType::New => query.then_order_by(post::published.desc()), + SortType::New => query.then_order_by(post_aggregates::published.desc()), SortType::TopAll => query.then_order_by(post_aggregates::score.desc()), SortType::TopYear => query .filter(post::published.gt(now - 1.years())) @@ -605,6 +605,7 @@ mod tests { score: 1, upvotes: 1, downvotes: 0, + stickied: false, published: agg.published, newest_comment_time: inserted_post.published, }, diff --git a/migrations/2020-12-10-152350_create_post_aggregates/down.sql b/migrations/2020-12-10-152350_create_post_aggregates/down.sql index 113f26d0..7b4024cd 100644 --- a/migrations/2020-12-10-152350_create_post_aggregates/down.sql +++ b/migrations/2020-12-10-152350_create_post_aggregates/down.sql @@ -3,7 +3,9 @@ drop table post_aggregates; drop trigger post_aggregates_post on post; drop trigger post_aggregates_comment_count on comment; drop trigger post_aggregates_score on post_like; +drop trigger post_aggregates_stickied on post; drop function post_aggregates_post, post_aggregates_comment_count, - post_aggregates_score; + post_aggregates_score, + post_aggregates_stickied; diff --git a/migrations/2020-12-10-152350_create_post_aggregates/up.sql b/migrations/2020-12-10-152350_create_post_aggregates/up.sql index 784f33e2..aaa611c4 100644 --- a/migrations/2020-12-10-152350_create_post_aggregates/up.sql +++ b/migrations/2020-12-10-152350_create_post_aggregates/up.sql @@ -6,18 +6,20 @@ create table post_aggregates ( score bigint not null default 0, upvotes bigint not null default 0, downvotes bigint not null default 0, + stickied boolean not null default false, published timestamp not null default now(), newest_comment_time timestamp not null default now(), unique (post_id) ); -insert into post_aggregates (post_id, comments, score, upvotes, downvotes, published, newest_comment_time) +insert into post_aggregates (post_id, comments, score, upvotes, downvotes, stickied, published, newest_comment_time) select p.id, coalesce(ct.comments, 0::bigint) as comments, coalesce(pl.score, 0::bigint) as score, coalesce(pl.upvotes, 0::bigint) as upvotes, coalesce(pl.downvotes, 0::bigint) as downvotes, + p.stickied, p.published, greatest(ct.recent_comment_time, p.published) as newest_activity_time from post p @@ -115,3 +117,21 @@ create trigger post_aggregates_score after insert or delete on post_like for each row execute procedure post_aggregates_score(); + +-- post stickied +create function post_aggregates_stickied() +returns trigger language plpgsql +as $$ +begin + update post_aggregates pa + set stickied = NEW.stickied + where pa.post_id = NEW.id; + + return null; +end $$; + +create trigger post_aggregates_stickied +after update on post +for each row +when (OLD.stickied is distinct from NEW.stickied) +execute procedure post_aggregates_stickied(); diff --git a/migrations/2021-01-05-200932_add_hot_rank_indexes/down.sql b/migrations/2021-01-05-200932_add_hot_rank_indexes/down.sql index 2ec455a5..55e83332 100644 --- a/migrations/2021-01-05-200932_add_hot_rank_indexes/down.sql +++ b/migrations/2021-01-05-200932_add_hot_rank_indexes/down.sql @@ -10,11 +10,14 @@ end; $$ LANGUAGE plpgsql; drop index - idx_post_published, - idx_post_stickied, idx_post_aggregates_hot, + idx_post_aggregates_stickied_hot, idx_post_aggregates_active, + idx_post_aggregates_stickied_active, idx_post_aggregates_score, + idx_post_aggregates_stickied_score, + idx_post_aggregates_published, + idx_post_aggregates_stickied_published, idx_comment_published, idx_comment_aggregates_hot, idx_comment_aggregates_score, diff --git a/migrations/2021-01-05-200932_add_hot_rank_indexes/up.sql b/migrations/2021-01-05-200932_add_hot_rank_indexes/up.sql index a6c45234..f4d41471 100644 --- a/migrations/2021-01-05-200932_add_hot_rank_indexes/up.sql +++ b/migrations/2021-01-05-200932_add_hot_rank_indexes/up.sql @@ -12,15 +12,19 @@ end; $$ LANGUAGE plpgsql IMMUTABLE; --- Post -create index idx_post_published on post (published desc); -create index idx_post_stickied on post (stickied desc); - -- Post_aggregates +create index idx_post_aggregates_stickied_hot on post_aggregates (stickied desc, hot_rank(score, published) desc, published desc); create index idx_post_aggregates_hot on post_aggregates (hot_rank(score, published) desc, published desc); + +create index idx_post_aggregates_stickied_active on post_aggregates (stickied desc, hot_rank(score, newest_comment_time) desc, newest_comment_time desc); create index idx_post_aggregates_active on post_aggregates (hot_rank(score, newest_comment_time) desc, newest_comment_time desc); + +create index idx_post_aggregates_stickied_score on post_aggregates (stickied desc, score desc); create index idx_post_aggregates_score on post_aggregates (score desc); +create index idx_post_aggregates_stickied_published on post_aggregates (stickied desc, published desc); +create index idx_post_aggregates_published on post_aggregates (published desc); + -- Comment create index idx_comment_published on comment (published desc); diff --git a/query_testing/views_to_diesel_migration/generate_reports.sh b/query_testing/views_to_diesel_migration/generate_reports.sh index fe4880cd..12993a08 100755 --- a/query_testing/views_to_diesel_migration/generate_reports.sh +++ b/query_testing/views_to_diesel_migration/generate_reports.sh @@ -16,8 +16,20 @@ cat explain.sql | $PSQL_CMD > post.json echo "explain (analyze, format json) select * from post p, post_aggregates pa where p.id = pa.post_id order by hot_rank(pa.score, pa.published) desc, pa.published desc limit 100" > explain.sql cat explain.sql | $PSQL_CMD > post_ordered_by_rank.json -echo "explain (analyze, format json) select * from post p, post_aggregates pa where p.id = pa.post_id order by p.stickied desc, hot_rank(pa.score, pa.published) desc, pa.published desc limit 100" > explain.sql -cat explain.sql | $PSQL_CMD > post_ordered_by_stickied.json +echo "explain (analyze, format json) select * from post p, post_aggregates pa where p.id = pa.post_id order by pa.stickied desc, hot_rank(pa.score, pa.published) desc, pa.published desc limit 100" > explain.sql +cat explain.sql | $PSQL_CMD > post_ordered_by_stickied_then_rank.json + +echo "explain (analyze, format json) select * from post p, post_aggregates pa where p.id = pa.post_id order by pa.score desc limit 100" > explain.sql +cat explain.sql | $PSQL_CMD > post_ordered_by_score.json + +echo "explain (analyze, format json) select * from post p, post_aggregates pa where p.id = pa.post_id order by pa.stickied desc, pa.score desc limit 100" > explain.sql +cat explain.sql | $PSQL_CMD > post_ordered_by_stickied_then_score.json + +echo "explain (analyze, format json) select * from post p, post_aggregates pa where p.id = pa.post_id order by pa.published desc limit 100" > explain.sql +cat explain.sql | $PSQL_CMD > post_ordered_by_published.json + +echo "explain (analyze, format json) select * from post p, post_aggregates pa where p.id = pa.post_id order by pa.stickied desc, pa.published desc limit 100" > explain.sql +cat explain.sql | $PSQL_CMD > post_ordered_by_stickied_then_published.json echo "explain (analyze, format json) select * from comment limit 100" > explain.sql cat explain.sql | $PSQL_CMD > comment.json diff --git a/query_testing/views_to_diesel_migration/timings-1.out b/query_testing/views_to_diesel_migration/timings-1.out deleted file mode 100644 index 2df77431..00000000 --- a/query_testing/views_to_diesel_migration/timings-1.out +++ /dev/null @@ -1,9 +0,0 @@ -comment.json: "Execution Time": 12.263 -community.json: "Execution Time": 1.225 -community_ordered_by_subscribers.json: "Execution Time": 170.255 -post.json: "Execution Time": 5.373 -post_ordered_by_rank.json: "Execution Time": 1458.801 -private_message.json: "Execution Time": 0.306 -site.json: "Execution Time": 0.064 -user_.json: "Execution Time": 2.606 -user_mention.json: "Execution Time": 0.135 -- 2.44.1