From 00692d6b4a022ed1c725b26b6daf373cf678b754 Mon Sep 17 00:00:00 2001 From: Josh Stompro Date: Mon, 29 Aug 2016 14:07:48 -0500 Subject: [PATCH] LP#1615736 - Add an index on the usr field of action.usr_circ_history. Improve the performance of looking up a users circ history by adding an index on action.usr_circ_history( usr ). To test for performance improvements time doing a search for a particular users history before and after the index is created. Signed-off-by: Josh Stompro Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 ++ Open-ILS/src/sql/Pg/upgrade/XXXX.action_usr_circ_history_index.sql | 7 +++++++ 2 files changed, 9 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.action_usr_circ_history_index.sql diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 731ab3c14c..19e1f1ea1e 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -1412,6 +1412,8 @@ CREATE TABLE action.usr_circ_history ( ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); +CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr ); + CREATE TRIGGER action_usr_circ_history_target_copy_trig AFTER INSERT OR UPDATE ON action.usr_circ_history FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy'); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.action_usr_circ_history_index.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.action_usr_circ_history_index.sql new file mode 100644 index 0000000000..965d9832b7 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.action_usr_circ_history_index.sql @@ -0,0 +1,7 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('xxxx', :eg_version); + +CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history ( usr ); + +COMMIT; -- 2.11.0