diff options
author | Yorhel <git@yorhel.nl> | 2019-10-07 12:02:55 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-10-07 12:10:02 +0200 |
commit | bf734c442f14d32a166034ebc076c84b52b07840 (patch) | |
tree | 1707c6ff571ce3c44eb8edd6e4118041cdd27ae9 /util | |
parent | 4844a42cb554253340391fdbdc5a7634ee287b1e (diff) |
SQL: Improve performance of update_vnpopularity()
Goes from 1.5 minutes to 1.5 seconds.
Temporary views instead of CTEs and self-joins instead of window
functions. That code was *old*.
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/func.sql | 27 |
1 files changed, 12 insertions, 15 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql index 98614b69..dab2ddbb 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -105,22 +105,19 @@ $$ LANGUAGE sql; -- recalculate vn.c_popularity CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$ -BEGIN - -- the following queries only update rows with popularity > 0, so make sure to reset all rows first + -- the following querie only update VNs which have valid votes, so make sure to reset all rows first. UPDATE vn SET c_popularity = NULL; - CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS - SELECT v.uid, v.vid, count(*)::real ^ 0.36788 - FROM votes v - JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote - JOIN users u ON u.id = v.uid AND NOT ign_votes - GROUP BY v.vid, v.uid; - CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS - SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid; - UPDATE vn SET c_popularity = s1.win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 s1 WHERE s1.vid = vn.id; - RETURN; -END; -$$ LANGUAGE plpgsql; - + WITH t2(vid, win) AS ( + SELECT vid, SUM(rank) + FROM ( + SELECT v.uid, v.vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788 + FROM votes v + JOIN users u ON u.id = v.uid AND NOT ign_votes + ) t1(uid, vid, rank) + GROUP BY vid + ) + UPDATE vn SET c_popularity = s1.win/(SELECT MAX(win) FROM t2) FROM t2 s1 WHERE s1.vid = vn.id AND s1.win > 0; +$$ LANGUAGE SQL; -- Recalculate tags_vn_inherit. |