LP1758160_Deleting_patrons_can_exceed_staff_client_timeouts

Added an index to action.usr_circ_history for much needed postgresql justice.
This will increase the speed when deleting patrons with large number of circulations.

Signed-off-by: blake <blake@mobiusconsortium.org>
Signed-off-by: Jason Boyer <jboyer@library.in.gov>
Signed-off-by: Jason Stephenson <jason@sigio.com>

diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index 7d06aba..2fb3659 100644 (file)
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -1547,6 +1547,7 @@ CREATE TABLE action.usr_circ_history (
 );
 
 CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr );
+CREATE INDEX action_usr_circ_history_source_circ_idx ON action.usr_circ_history ( source_circ );
 
 CREATE TRIGGER action_usr_circ_history_target_copy_trig 
     AFTER INSERT OR UPDATE ON action.usr_circ_history 

diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.auch-source_circ-index.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.auch-source_circ-index.sql
new file mode 100644 (file)
index 0000000..2f0673a
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.auch-source_circ-index.sql
@@ -0,0 +1,12 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+-- Add an index to action.usr_circ_history (source_circ) to speed up aging circs and purging accounts
+
+CREATE INDEX action_usr_circ_history_source_circ_idx 
+  ON action.usr_circ_history
+  USING btree
+  (source_circ);
+
+COMMIT;