From 4491c46ffcecb1f317b1b35ac6afa8c148b50371 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Thu, 28 Jun 2018 14:36:27 -0400 Subject: [PATCH] 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 Signed-off-by: Josh Stompro Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/400.schema.action_trigger.sql | 3 +++ .../sql/Pg/upgrade/XXXX.schema.ate_outputs_indexes.sql | 9 +++++++++ 2 files changed, 12 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.ate_outputs_indexes.sql 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 50fa9d65f0..69b365d3f8 100644 --- 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 index 0000000000..a71030e8b4 --- /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; -- 2.43.2