From cd5e4dffdf4d99cac7d47433981cfa6d669b2b45 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 22 Dec 2019 16:24:58 +0100 Subject: 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. --- util/sql/tableattrs.sql | 2 ++ util/updates/update_wip_lists.sql | 2 ++ 2 files changed, 4 insertions(+) (limited to 'util') 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; -- cgit v1.2.3