summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-10-12 20:58:02 +0200
committerYorhel <git@yorhel.nl>2015-10-12 21:17:48 +0200
commit61251790bd1da10eebee3c56a70a9711e7680ce8 (patch)
treeccf47fd384b54cf4d5c9bc378c556fc3aa3e6535
parent1a6dd0160a75bc83390912dfb2635cd5664ce9ee (diff)
Notifications: Allow max 500 notifies per user + add SQL index on uid
Turns out that fetching whether or not you have unread notifications (done on every pageview if you're logged in) was pretty slow. The index speeds up both that query and the "my notifications" view. The extra purge for old notifications for users with more than 500 notifications ensures that the index stays effective for the unread notifications count. Otherwise it'll have to read half of the notifications table anyway to check the 'unread' filter.
-rw-r--r--lib/Multi/Maintenance.pm2
-rw-r--r--util/sql/tableattrs.sql1
-rw-r--r--util/updates/update_2.25.sql4
3 files changed, 7 insertions, 0 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 23fb6db5..23e17fff 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -93,6 +93,8 @@ my %dailies = (
cleansessions => q|DELETE FROM sessions WHERE lastused < NOW()-'1 month'::interval|,
cleannotifications => q|DELETE FROM notifications WHERE read < NOW()-'1 month'::interval|,
+ cleannotifications2=> q|DELETE FROM notifications WHERE id IN (
+ SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY uid ORDER BY id DESC) > 500 from notifications) AS x(id,del) WHERE x.del)|,
rmunconfirmusers => q|DELETE FROM users WHERE registered < NOW()-'1 week'::interval AND NOT email_confirmed|,
cleanthrottle => q|DELETE FROM login_throttle WHERE timeout < NOW()|,
);
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 1e812f01..4028e98e 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -77,6 +77,7 @@ ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOR
CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden;
+CREATE INDEX notifications_uid ON notifications (uid);
CREATE INDEX releases_producers_rid ON releases_producers (rid);
CREATE INDEX releases_vn_vid ON releases_vn (vid);
CREATE INDEX tags_vn_date ON tags_vn (date);
diff --git a/util/updates/update_2.25.sql b/util/updates/update_2.25.sql
index 6302daaf..55fdb537 100644
--- a/util/updates/update_2.25.sql
+++ b/util/updates/update_2.25.sql
@@ -74,3 +74,7 @@ CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng');
ALTER TABLE producers_rev ALTER COLUMN type DROP DEFAULT;
ALTER TABLE producers_rev ALTER COLUMN type TYPE producer_type USING type::producer_type;
ALTER TABLE producers_rev ALTER COLUMN type SET DEFAULT 'co';
+
+
+-- Extra index
+CREATE INDEX notifications_uid ON notifications (uid);