LP1778940: Add Indexes to ate.*_output

When there are many rows in action_trigger.event_output
simply ensuring referencial integrity can take an
unacceptably long time as Postgres has to make sure
that the output being deleted isn't referenced in
any of these 3 fields. Adding these indexes keeps
these checks always fast.

Signed-off-by: Jason Boyer <jboyer@library.in.gov>
Signed-off-by: Josh Stompro <stompro@stompro.org>
Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>

diff --git a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql
index 50fa9d6..69b365d 100644 (file)
--- a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql
+++ b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql
@@ -276,6 +276,9 @@ CREATE TABLE action_trigger.event (
 );
 CREATE INDEX atev_target_def_idx ON action_trigger.event (target,event_def);
 CREATE INDEX atev_def_state ON action_trigger.event (event_def,state);
+CREATE INDEX atev_template_output ON action_trigger.event (template_output);
+CREATE INDEX atev_async_output ON action_trigger.event (async_output);
+CREATE INDEX atev_error_output ON action_trigger.event (error_output);
 
 CREATE TABLE action_trigger.event_params (
     id          BIGSERIAL   PRIMARY KEY,

diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.ate_outputs_indexes.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.ate_outputs_indexes.sql
new file mode 100644 (file)
index 0000000..a71030e
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.ate_outputs_indexes.sql
@@ -0,0 +1,9 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+CREATE INDEX atev_template_output ON action_trigger.event (template_output);
+CREATE INDEX atev_async_output ON action_trigger.event (async_output);
+CREATE INDEX atev_error_output ON action_trigger.event (error_output);
+
+COMMIT;