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.

2 files changed, 4 insertions, 0 deletions

diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index a63b2fcd..54361c0a 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -134,6 +134,8 @@ CREATE        INDEX votes_uid              ON votes (uid);
 CREATE UNIQUE INDEX changes_itemrev        ON changes (type, itemid, rev);
 CREATE UNIQUE INDEX chars_vns_pkey         ON chars_vns (id, vid, COALESCE(rid, 0));
 CREATE UNIQUE INDEX chars_vns_hist_pkey    ON chars_vns_hist (chid, vid, COALESCE(rid, 0));
+CREATE        INDEX ulist_vns_voted        ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more
+CREATE        INDEX users_ign_votes        ON users (id) WHERE ign_votes;
 
 
 
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index a327f854..c07e4ba1 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -129,3 +129,5 @@ ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0;
 
 \timing
 SELECT update_users_ulist_stats(NULL);
+CREATE        INDEX ulist_vns_voted        ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL;
+CREATE        INDEX users_ign_votes        ON users (id) WHERE ign_votes;