From: Steinar H. Gunderson <sgunderson@bigfoot.com>
Date: Fri, 8 Jan 2016 01:06:01 +0000 (+0100)
Subject: Remove a redundant and quite big index on exif_info.
X-Git-Url: https://git.sesse.net/?p=pr0n;a=commitdiff_plain;h=12e2d43c7d68a328e9c6e3ff2f3810d3b6799b47;ds=inline

Remove a redundant and quite big index on exif_info.
---

diff --git a/sql/pr0n.sql b/sql/pr0n.sql
index 860fd47..4729a37 100644
--- a/sql/pr0n.sql
+++ b/sql/pr0n.sql
@@ -77,11 +77,10 @@ CREATE TABLE exif_info (
     key varchar NOT NULL,
     value varchar NOT NULL,
 
-    PRIMARY KEY ( image, key )
+    PRIMARY KEY ( key, image )
 );
 
-CREATE INDEX exif_info_key ON exif_info ( key );
-CLUSTER exif_info_key ON exif_info;
+CLUSTER exif_info_pkey ON exif_info;
 
 GRANT INSERT ON TABLE deleted_images TO pr0n;
 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE events TO pr0n;
diff --git a/sql/upgrade-v3.11.sql b/sql/upgrade-v3.11.sql
index 7996fea..8f066aa 100644
--- a/sql/upgrade-v3.11.sql
+++ b/sql/upgrade-v3.11.sql
@@ -1,3 +1,5 @@
+set work_mem to 1048576;
+
 alter table images add column is_render boolean NOT NULL DEFAULT false;
 alter table images add column render_id integer;
 CREATE UNIQUE INDEX unique_id_for_render ON images USING btree (vhost, event, id);
@@ -6,3 +8,8 @@ alter table images add check (NOT (is_render AND (render_id IS NOT NULL)));
 
 alter table deleted_images add column is_render boolean NOT NULL DEFAULT false;
 alter table deleted_images add column render_id integer;
+
+drop index exif_info_key;
+alter table exif_info drop constraint exif_info_pkey;
+alter table exif_info create primary key ( key, image );
+cluster exif_info_pkey on exif_info;