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 /lib | |
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 'lib')
-rw-r--r-- | lib/Multi/Maintenance.pm | 14 |
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| |