Caching the global database statistics

Because I can't say no to a performance increase of 4 to 7ms
for -every- pageview!
Makes use of postgresql triggers and stored procedures.

1 files changed, 74 insertions, 6 deletions

diff --git a/util/dump.sql b/util/dump.sql
index 487950e7..0bbbecbd 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -137,6 +137,12 @@ CREATE TABLE screenshots (
   height smallint NOT NULL DEFAULT 0
 );
 
+-- stats_cache
+CREATE TABLE stats_cache (
+  section varchar(25) NOT NULL PRIMARY KEY,
+  count integer NOT NULL DEFAULT 0
+);
+
 -- threads
 CREATE TABLE threads (
   id SERIAL NOT NULL PRIMARY KEY,
@@ -404,7 +410,17 @@ END;
 $$ LANGUAGE plpgsql;
 
 
--- trigger function to keep the c_* columns in the users table up to date
+
+
+
+
+
+-----------------------
+--  T R I G G E R S  --
+-----------------------
+
+
+-- keep the c_* columns in the users table up to date
 CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
 BEGIN
   IF TG_TABLE_NAME = 'votes' THEN
@@ -424,6 +440,53 @@ BEGIN
 END;
 $$ LANGUAGE 'plpgsql';
 
+CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_votes_update   AFTER INSERT OR DELETE ON votes   FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+
+
+-- the stats_cache table
+CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
+BEGIN
+  IF TG_OP = 'INSERT' THEN
+    IF TG_TABLE_NAME = 'users' THEN
+      UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+    ELSIF NEW.hidden = FALSE THEN
+      IF TG_TABLE_NAME = 'threads_posts' THEN
+        IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN
+          UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+        END IF;
+      ELSE
+        UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+      END IF;
+    END IF;
+
+  ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN
+    IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN
+      IF TG_TABLE_NAME = 'threads' THEN
+        UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
+      END IF;
+      UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+    ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN
+      IF TG_TABLE_NAME = 'threads' THEN
+        UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
+      END IF;
+      UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
+    END IF;
+
+  ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN
+    UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
+  END IF;
+  RETURN NULL;
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE TRIGGER vn_stats_update            AFTER INSERT OR UPDATE ON vn            FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER producers_stats_update     AFTER INSERT OR UPDATE ON producers     FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER releases_stats_update      AFTER INSERT OR UPDATE ON releases      FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER threads_stats_update       AFTER INSERT OR UPDATE ON threads       FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER users_stats_update         AFTER INSERT OR DELETE ON users         FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+
 
 
 
@@ -434,16 +497,21 @@ $$ LANGUAGE 'plpgsql';
 ---------------------------------
 
 
--- triggers
-CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_votes_update   AFTER INSERT OR DELETE ON votes   FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-
-
 -- Sequences used for ID generation of items not in the DB
 CREATE SEQUENCE covers_seq;
 
+
+-- Rows that are assumed to be available
 INSERT INTO users (id, username, mail, rank)
   VALUES (0, 'deleted', 'del@vndb.org', 0);
 INSERT INTO users (username, mail, rank, registered)
   VALUES ('multi', 'multi@vndb.org', 0, EXTRACT(EPOCH FROM NOW()));
 
+INSERT INTO stats_cache (section, count) VALUES
+  ('users',         1),
+  ('vn',            0),
+  ('producers',     0),
+  ('releases',      0),
+  ('threads',       0),
+  ('threads_posts', 0);
+