summaryrefslogtreecommitdiff
path: root/lib
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 /lib
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 'lib')
-rw-r--r--lib/Multi/Maintenance.pm14
1 files changed, 2 insertions, 12 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index e19c1890..846abcd0 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -71,18 +71,8 @@ my %dailies = (
# takes about 25 seconds, OK
traitcache => 'SELECT traits_chars_calc(NULL)',
- # takes about 140 seconds, not really OK
- vnpopularity => 'SELECT update_vnpopularity()',
-
- # takes about 3 seconds, can be performed in ranges as well when necessary
- vnrating => q|
- UPDATE vn SET
- c_rating = (SELECT (
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) /
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
- ) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
- ),
- c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)), 0)|,
+ # takes about 4 seconds, OK
+ vnstats => 'SELECT update_vnvotestats()',
# should be pretty fast
cleangraphs => q|