Cache VN popularity & rating rankings

Should shave off about 10-30ms of the VN page generation times.

4 files changed, 15 insertions, 12 deletions

diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index d5c4b202..6451aaf5 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -628,12 +628,7 @@ sub stats_ {
           LIMIT', \8
     );
 
-    my $rank = $v->{c_votecount} && tuwf->dbRowi('
-        SELECT c_rating, c_popularity
-             , (SELECT COUNT(*)+1 FROM vn iv WHERE NOT iv.hidden AND iv.c_popularity > COALESCE(v.c_popularity, 0.0)) AS pop_rank
-             , (SELECT COUNT(*)+1 FROM vn iv WHERE NOT iv.hidden AND iv.c_rating > COALESCE(v.c_rating, 0.0)) AS rating_rank
-          FROM vn v WHERE id =', \$v->{id}
-    );
+    my $rank = $v->{c_votecount} && tuwf->dbRowi('SELECT c_rating, c_popularity, c_pop_rank, c_rat_rank FROM vn v WHERE id =', \$v->{id});
 
     my sub votestats_ {
         table_ class => 'votegraph', sub {
@@ -672,8 +667,8 @@ sub stats_ {
         clearfloat_;
         div_ sub {
             h3_ 'Ranking';
-            p_ sprintf 'Popularity: ranked #%d with a score of %.2f', $rank->{pop_rank}, ($rank->{c_popularity}||0)*100;
-            p_ sprintf 'Bayesian rating: ranked #%d with a rating of %.2f', $rank->{rating_rank}, $rank->{c_rating}/10;
+            p_ sprintf 'Popularity: ranked #%d with a score of %.2f', $rank->{c_pop_rank}, ($rank->{c_popularity}||0)*100;
+            p_ sprintf 'Bayesian rating: ranked #%d with a rating of %.2f', $rank->{c_rat_rank}, $rank->{c_rating}/10;
         } if $v->{c_votecount};
     }
 
diff --git a/sql/func.sql b/sql/func.sql
index 58012afd..8492b412 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -102,7 +102,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
 $$ LANGUAGE sql;
 
 
--- Update vn.c_popularity, c_rating and c_votecount
+-- Update vn.c_popularity, c_rating, c_votecount, c_pop_rank and c_rat_rank
 CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$
   WITH votes(vid, uid, vote) AS ( -- List of all non-ignored VN votes
     SELECT vid, uid, vote FROM ulist_vns WHERE vote IS NOT NULL AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
@@ -124,14 +124,16 @@ CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$
         SELECT uid, vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788 FROM votes
       ) x(uid, vid, rank)
      GROUP BY vid
-  ), stats(vid, rating, count, popularity) AS ( -- Combined stats
+  ), stats(vid, rating, count, popularity, pop_rank, rat_rank) AS ( -- Combined stats
     SELECT v.id, COALESCE(r.rating, 0), COALESCE(r.count, 0)
          , p.win/(SELECT MAX(win) FROM popularities)
+         , CASE WHEN p.win    IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, p.win    DESC NULLS LAST) END
+         , CASE WHEN r.rating IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, r.rating DESC NULLS LAST) END
       FROM vn v
       LEFT JOIN ratings r ON r.vid = v.id
       LEFT JOIN popularities p ON p.vid = v.id AND p.win > 0
   )
-  UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity FROM stats WHERE id = vid;
+  UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity, c_pop_rank = pop_rank, c_rat_rank = rat_rank FROM stats WHERE id = vid;
 $$ LANGUAGE SQL;
 
 
diff --git a/sql/schema.sql b/sql/schema.sql
index 2e10006d..eb2aa9a2 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -868,7 +868,9 @@ CREATE TABLE vn ( -- dbentry_type=v
   c_rating   real, -- [pub]
   c_votecount integer NOT NULL DEFAULT 0, -- [pub]
   c_search   text,
-  l_wikidata integer -- [pub]
+  l_wikidata integer, -- [pub]
+  c_pop_rank integer,
+  c_rat_rank integer
 );
 
 -- vn_hist
diff --git a/util/updates/2020-06-04-vn-ranking-cache.sql b/util/updates/2020-06-04-vn-ranking-cache.sql
new file mode 100644
index 00000000..3eb02392
--- /dev/null
+++ b/util/updates/2020-06-04-vn-ranking-cache.sql
@@ -0,0 +1,4 @@
+ALTER TABLE vn ADD COLUMN c_pop_rank integer;
+ALTER TABLE vn ADD COLUMN c_rat_rank integer;
+\i sql/func.sql
+select update_vnvotestats();