1 files changed, 11 insertions, 11 deletions
diff --git a/db/0-create.psql b/db/0-create.psql
index 8676b79..3479159 100644
--- a/db/0-create.psql
+++ b/db/0-create.psql
@@ -16,6 +16,9 @@ CREATE TABLE users (
     is_bot boolean NOT NULL DEFAULT false,
     profile_bg text
 );
+
+ALTER TABLE users ADD CONSTRAINT users_nick_constraint UNIQUE (LOWER(nick));
+
 CREATE INDEX users_nick_lowercase_idx ON users (lower(nick));
 
 --    ALTER TABLE users
@@ -25,8 +28,6 @@ CREATE INDEX users_nick_lowercase_idx ON users (lower(nick));
 --    ALTER TABLE users
 --    ADD COLUMN last_ip cidr DEFAULT '0';
 
-
-
 CREATE TABLE rooms (
     room_id SERIAL PRIMARY KEY,
     key text UNIQUE NOT NULL,
@@ -46,14 +47,13 @@ CREATE TABLE messages (
     is_image bool NOT NULL,
     is_text bool NOT NULL DEFAULT false
 );
-CREATE INDEX messages_user_id_idx ON messages (user_id);
-CREATE INDEX messages_room_id_idx ON messages (room_id);
-CREATE INDEX messages_created_on_idx ON messages (created_on);
-CREATE INDEX messages_is_image_idx ON messages (is_image);
-CREATE INDEX messages_user_created_on_idx ON messages (user_id, created_on desc);
 
--- Used to load recent dumps in a room
-CREATE INDEX messages_room_id_created_on_idx ON messages (room_id, created_on desc);
+-- historical image dumps per user
+CREATE INDEX messages_user_created_on_image_only_idx ON messages (user_id, created_on)
+    WHERE is_image;
+
+-- historical image dumps in a room. needs to handle non-image messages.
+CREATE INDEX messages_room_id_created_on_idx ON messages (room_id, created_on);
 
 -- Queries to support:
 -- 1) What messages are tagged x? (ordered by time, popularity)
@@ -69,10 +69,10 @@ CREATE TABLE tags (
   tag text NOT NULL,
   PRIMARY KEY (user_id, message_id, tag)
 );
-CREATE INDEX tags_user_id_idx ON tags (user_id);
+
 CREATE INDEX tags_message_id_idx ON tags (message_id);
-CREATE INDEX tags_created_on_id_idx ON tags (created_on DESC);
 CREATE INDEX tags_tag_lowercase_idx ON tags (lower(tag));
+CREATE INDEX tags_user_id_created_on_idx ON tags(user_id, created_on);
 
 CREATE TABLE mutes (
   mute_id SERIAL PRIMARY KEY,