From 7a97fc370bee9e81e9d7b39db491d5c8c27f36ba Mon Sep 17 00:00:00 2001
From: Dessalines <tyhou13@gmx.com>
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