diff options
author | Yorhel <git@yorhel.nl> | 2019-12-16 16:48:12 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-12-16 16:48:14 +0100 |
commit | 939108b4b971edcaf9c70728816fac7676034cda (patch) | |
tree | 19c228b854be0c39cdbab634e094df521b4aac0a /util | |
parent | 153d6578e4c1973f08694fe78259417912e3d5f3 (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.sql | 42 | ||||
-rw-r--r-- | util/updates/update_wip_lists.sql | 2 |
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(); |