summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-16 16:48:12 +0100
committerYorhel <git@yorhel.nl>2019-12-16 16:48:14 +0100
commit939108b4b971edcaf9c70728816fac7676034cda (patch)
tree19c228b854be0c39cdbab634e094df521b4aac0a /util
parent153d6578e4c1973f08694fe78259417912e3d5f3 (diff)
ulist: Convert vn.c_* vote stat updating to use ulist_vns table
It turned out that naively changing the existing queries to refer to ulist_vns with a (WHERE vote IS NOT NULL) made them ultra slow (like, DNF-slow), for a reason I haven't been able to figure out yet. I had to rewrite the queries to use CTEs and in doing so figured that I could get an extra performance improvement by combining the popularity and rating updates in one query. The combined update function now runs faster than the old queries.
Diffstat (limited to 'util')
-rw-r--r--util/sql/func.sql42
-rw-r--r--util/updates/update_wip_lists.sql2
2 files changed, 31 insertions, 13 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index f326d9be..4dbb4653 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -102,24 +102,40 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
$$ LANGUAGE sql;
-
--- recalculate vn.c_popularity
-CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
- -- the following querie only update VNs which have valid votes, so make sure to reset all rows first.
- UPDATE vn SET c_popularity = NULL;
- WITH t2(vid, win) AS (
+-- Update vn.c_popularity, c_rating and c_votecount
+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)
+ ), avgcount(avgcount) AS ( -- Average number of votes per VN
+ SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes
+ ), avgavg(avgavg) AS ( -- Average vote average
+ SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) x(a)
+ ), ratings(vid, count, rating) AS ( -- Ratings and vote counts
+ SELECT vid, COALESCE(COUNT(uid), 0),
+ COALESCE(
+ ((SELECT avgcount FROM avgcount) * (SELECT avgavg FROM avgavg) + SUM(vote)::real) /
+ ((SELECT avgcount FROM avgcount) + COUNT(uid)::real),
+ 0)
+ FROM votes
+ GROUP BY vid
+ ), popularities(vid, win) AS ( -- Popularity scores (before normalization)
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
+ 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
+ SELECT v.id, COALESCE(r.rating, 0), COALESCE(r.count, 0)
+ , p.win/(SELECT MAX(win) FROM popularities)
+ 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_popularity = s1.win/(SELECT MAX(win) FROM t2) FROM t2 s1 WHERE s1.vid = vn.id AND s1.win > 0;
+ UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity FROM stats WHERE id = vid;
$$ LANGUAGE SQL;
+
-- Recalculate tags_vn_inherit.
-- When a vid is given, only the tags for that vid will be updated. These
-- incremental updates do not affect tags.c_items, so that may still get
@@ -738,7 +754,7 @@ CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype
SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester
-- look for users who should get this notify
FROM (
- UNION SELECT uid FROM ulist_vns WHERE xtype = 'v' AND vid = xedit.itemid
+ SELECT uid FROM ulist_vns WHERE xtype = 'v' AND vid = xedit.itemid
UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
) u
-- fetch info about this edit
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index 1bbf700f..e6f37184 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -113,6 +113,8 @@ COMMIT;
\timing
+DROP FUNCTION update_vnpopularity();
+
\i util/sql/func.sql
CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();