From e29bac21efa46736919f501d938c65149b3b0fd5 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 28 Feb 2013 13:28:20 -0500 Subject: [PATCH] Observed speed increases through judicious indexing These indexes drove high-cost queries down into the noise at several sites. They are more applicable for larger installations, where "larger" can be defined as either "lots of data in the db" or "has used Evergreen for a long time". Signed-off-by: Mike Rylander Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/005.schema.actors.sql | 1 + Open-ILS/src/sql/Pg/040.schema.asset.sql | 1 + Open-ILS/src/sql/Pg/090.schema.action.sql | 3 ++ Open-ILS/src/sql/Pg/200.schema.acq.sql | 2 + .../src/sql/Pg/400.schema.action_trigger.sql | 1 + .../XXXX.schema.general-indexing-needs.sql | 49 +++++++++++++++++++ 6 files changed, 57 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.general-indexing-needs.sql diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql index e6d8cd6102..c92ede4fdc 100644 --- a/Open-ILS/src/sql/Pg/005.schema.actors.sql +++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql @@ -667,6 +667,7 @@ CREATE TABLE actor.usr_activity ( etype INT NOT NULL REFERENCES config.usr_activity_type (id), event_time TIMESTAMPTZ NOT NULL DEFAULT NOW() ); +CREATE INDEX usr_activity_usr_idx ON actor.usr_activity (usr); CREATE TABLE actor.toolbar ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 541cff5bb5..af449351ab 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -109,6 +109,7 @@ CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number); CREATE INDEX cp_creator_idx ON asset.copy ( creator ); CREATE INDEX cp_editor_idx ON asset.copy ( editor ); CREATE INDEX cp_create_date ON asset.copy (create_date); +CREATE INDEX cp_available_by_circ_lib_idx ON asset.copy (circ_lib) WHERE status IN (0,7); CREATE RULE protect_copy_delete AS ON DELETE TO asset.copy DO INSTEAD UPDATE asset.copy SET deleted = TRUE WHERE OLD.id = asset.copy.id; CREATE TABLE asset.copy_part_map ( diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index fd82cf0281..9dec15f306 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -417,6 +417,8 @@ CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy) CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time); CREATE INDEX hold_request_fulfillment_staff_idx ON action.hold_request ( fulfillment_staff ); CREATE INDEX hold_request_requestor_idx ON action.hold_request ( requestor ); +CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL; +CREATE INDEX hold_request_current_copy_before_cap_idx ON action.hold_request (current_copy) WHERE capture_time IS NULL AND cancel_time IS NULL; CREATE TABLE action.hold_request_note ( @@ -483,6 +485,7 @@ ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id); CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest); CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source); CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy); +CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold); CREATE TABLE action.unfulfilled_hold_list ( diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index d32b519560..ddb6e17192 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -785,6 +785,8 @@ CREATE TABLE acq.edi_message ( 'OSTRPT' )) ); +CREATE INDEX edi_message_account_status_idx ON acq.edi_message (account,status); +CREATE INDEX edi_message_po_idx ON acq.edi_message (purchase_order); -- Note below that the primary key is NOT a SERIAL type. We will periodically truncate and rebuild -- the table, assigning ids programmatically instead of using a sequence. 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 4701a904a3..bf5e61be97 100644 --- a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql +++ b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql @@ -229,6 +229,7 @@ CREATE TABLE action_trigger.event ( async_output BIGINT REFERENCES action_trigger.event_output (id) ); 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 TABLE action_trigger.event_params ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.general-indexing-needs.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.general-indexing-needs.sql new file mode 100644 index 0000000000..51d9897dfe --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.general-indexing-needs.sql @@ -0,0 +1,49 @@ + +-- No transaction needed. This can be run on a live, production server. +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- On a heavily used system, user activity lookup is painful. This is used +-- on the patron display in the staff client. +-- +-- Measured speed increase: ~2s -> .01s +create index concurrently usr_activity_usr_idx on actor.usr_activity (usr); + +-- Finding open holds, often as a subquery within larger hold-related logic, +-- can be sped up with the following. +-- +-- Measured speed increase: ~3s -> .02s +create index concurrently hold_request_open_idx on action.hold_request (id) where cancel_time IS NULL AND fulfillment_time IS NULL; + +-- Hold queue position is a particularly difficult thing to calculate +-- efficiently. Recent changes in the query structure now allow some +-- optimization via indexing. These do that. +-- +-- Measured speed increase: ~6s -> ~0.4s +create index concurrently cp_available_by_circ_lib_idx on asset.copy (circ_lib) where status IN (0,7); +create index concurrently hold_request_current_copy_before_cap_idx on action.hold_request (current_copy) where capture_time IS NULL AND cancel_time IS NULL; + +-- After heavy use, fetching EDI messages becomes time consuming. The following +-- index addresses that for large-data environments. +-- +-- Measured speed increase: ~3s -> .1s +create index concurrently edi_message_account_status_idx on acq.edi_message (account,status); + +-- After heavy use, fetching POs becomes time consuming. The following +-- index addresses that for large-data environments. +-- +-- Measured speed increase: ~1.5s -> .1s +create index concurrently edi_message_po_idx on acq.edi_message (purchase_order); + +-- Related to EDI messages, fetching of certain A/T events benefit from specific +-- indexing. This index is more general than necessary for the observed query +-- but ends up speeding several other (already relatively fast) queries. +-- +-- Measured speed increase: ~2s -> .06s +create index concurrently atev_def_state on action_trigger.event (event_def,state); + +-- Retrieval of hold transit by hold id (for transit completion or cancelation) +-- is slow in some query formulations. +-- +-- Measured speed increase: ~.5s -> .1s +create index concurrently hold_transit_copy_hold_idx on action.hold_transit_copy (hold); + -- 2.43.2