From 12e2d43c7d68a328e9c6e3ff2f3810d3b6799b47 Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Fri, 8 Jan 2016 02:06:01 +0100 Subject: [PATCH] Remove a redundant and quite big index on exif_info. --- sql/pr0n.sql | 5 ++--- sql/upgrade-v3.11.sql | 7 +++++++ 2 files changed, 9 insertions(+), 3 deletions(-) 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; -- 2.39.5