summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-01-29 11:37:10 +0100
committerYorhel <git@yorhel.nl>2021-01-29 11:37:14 +0100
commitdb621ea9e1c827735bef546e08d15a69e50b81d1 (patch)
tree63453e3db07da86bf6c9c802d900a9bbd40f35d4
parentb9ad660694de8c1df6fa3f409dd8cbc2ee266829 (diff)
SQL: Add index on releases.released to speed up homepage queries
Last time I tried this in PostgreSQL 11 the index would only get used for one of the two queries, now it properly works on both and gives a nice ~25ms boost.
-rw-r--r--sql/tableattrs.sql1
1 files changed, 1 insertions, 0 deletions
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 4ff8c43c..a43e1c62 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -121,6 +121,7 @@ CREATE INDEX chars_image ON chars (image);
CREATE UNIQUE INDEX image_votes_pkey ON image_votes (uid, id);
CREATE INDEX image_votes_id ON image_votes (id);
CREATE INDEX notifications_uid_iid ON notifications (uid,iid);
+CREATE INDEX releases_released ON releases (released) WHERE NOT hidden; -- Mainly for the homepage
CREATE INDEX releases_producers_pid ON releases_producers (pid);
CREATE INDEX releases_vn_vid ON releases_vn (vid);
CREATE INDEX reports_new ON reports (date) WHERE status = 'new';