From 2786577189a2a3e838ea5a0830b7c16708bf7d0c Mon Sep 17 00:00:00 2001 From: dbs Date: Tue, 13 Oct 2009 15:09:58 +0000 Subject: [PATCH] Enhance upgrade for complete subject indexing: drop the unnecessary final ORDER BY clause and create an index on the source record. Cuts upgrade time to about one one-hundredth of what it was before. git-svn-id: svn://svn.open-ils.org/ILS/trunk@14400 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 1 + Open-ILS/src/sql/Pg/upgrade/0030.data.flat-subject-entries.sql | 5 +++-- 2 files changed, 4 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index f6b87270e0..6f2d440a10 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -69,6 +69,7 @@ CREATE TRIGGER metabib_subject_field_entry_fti_trigger FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('subject'); CREATE INDEX metabib_subject_field_entry_index_vector_idx ON metabib.subject_field_entry USING GIST (index_vector); +CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source); CREATE TABLE metabib.keyword_field_entry ( diff --git a/Open-ILS/src/sql/Pg/upgrade/0030.data.flat-subject-entries.sql b/Open-ILS/src/sql/Pg/upgrade/0030.data.flat-subject-entries.sql index 1fcab40ec4..e1ac1fd8b7 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0030.data.flat-subject-entries.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0030.data.flat-subject-entries.sql @@ -8,6 +8,8 @@ INSERT INTO config.upgrade_log (version) VALUES ('0030'); -- dbs INSERT INTO config.metabib_field (field_class, name, format, xpath ) VALUES ( 'subject', 'complete', 'mods32', $$//mods32:mods/mods32:subject//text()$$ ); +CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source); + INSERT INTO metabib.subject_field_entry (source, field, value) SELECT source, ( SELECT id @@ -26,7 +28,6 @@ INSERT INTO metabib.subject_field_entry (source, field, value) SELECT source FROM metabib.subject_field_entry GROUP BY source - ) AS groupee - ORDER BY source; + ) AS groupee; COMMIT; -- 2.11.0