summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-22 16:24:58 +0100
committerYorhel <git@yorhel.nl>2019-12-22 16:29:10 +0100
commitcd5e4dffdf4d99cac7d47433981cfa6d669b2b45 (patch)
treedc1f7ee0df86a6f3e38807b07bb1b0de2cd987c6 /lib/VNDB/DB
parent1b47e52e88d90478ff39744732085924fe5a51f2 (diff)
ulist: Use new lists for VN vote stats & listing
To my surprise, I actually managed to achieve acceptable performance by just adding two indices. I totally expected I'd have to keep a cache column in ulist_vns whether the row is private or not. The partial index on the users table in fact improves the performance of the vote graph query. A covering index improves that even further, but that requires Postgres 11+, which the Docker image doesn't have yet (and isn't all that crucial anyway). There's a rather annoying potential for confusion regarding the private flag on votes. The user page & list stats only look at whether the 'Voted' label is private, whereas the VN stats use the "proper" approach of checking for any public label. Not entirely sure which of the two is more intuitive.
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r--lib/VNDB/DB/ULists.pm16
1 files changed, 7 insertions, 9 deletions
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 6f061e97..37fd46d4 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -228,21 +228,19 @@ sub dbVoteGet {
}
-# Arguments: (uid|vid), id, use_ignore_list
+# Arguments: 'vid', id
# Returns an arrayref with 10 elements containing the [ count(vote), sum(vote) ]
# for votes in the range of ($index+0.5) .. ($index+1.4)
sub dbVoteStats {
my($self, $col, $id, $ign) = @_;
- my $u = $self->authInfo->{id};
my $r = [ map [0,0], 0..9 ];
$r->[$_->{idx}] = [ $_->{votes}, $_->{total} ] for (@{$self->dbAll(q|
- SELECT (vote::numeric/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total
- FROM votes
- !s
- !W
- GROUP BY (vote::numeric/10)::int|,
- $ign ? 'JOIN users ON id = uid AND (NOT ign_votes'.($u?sprintf(' OR id = %d',$u):'').')' : '',
- $col ? { '!s = ?' => [ $col, $id ] } : {},
+ SELECT (vote::numeric/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total
+ FROM ulist_vns uv
+ WHERE uv.vote IS NOT NULL AND NOT EXISTS(SELECT 1 FROM users u WHERE u.id = uv.uid AND u.ign_votes)
+ AND uv.vid = ?
+ GROUP BY (vote::numeric/10)::int|,
+ $id
)});
return $r;
}