From 378588d937e19146ead613fb8ff40de1bb2c7289 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Thu, 25 Feb 2021 10:06:38 -0500 Subject: [PATCH] LP1703658: Convert GIST Indexes to GIN Rather than suggest that sites experiencing search speed issues do this conversion on demand just knock it out from the start. Signed-off-by: Jason Boyer Signed-off-by: Jason Stephenson --- Open-ILS/src/sql/Pg/011.schema.authority.sql | 4 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 28 +++--- .../upgrade/XXXX.data.dont_get_the_gist.sql | 91 +++++++++++++++++++ 3 files changed, 107 insertions(+), 16 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index 6e1e034126..f0485a7a9a 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -279,7 +279,7 @@ CREATE TRIGGER authority_full_rec_fti_trigger BEFORE UPDATE OR INSERT ON authority.full_rec FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); -CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector); +CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIN (index_vector); /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */ CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops); /* But we still need this (boooo) for paging using >, <, etc */ @@ -407,7 +407,7 @@ CREATE TRIGGER authority_simple_heading_fti_trigger BEFORE UPDATE OR INSERT ON authority.simple_heading FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); -CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector); +CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIN (index_vector); CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value); CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value); CREATE INDEX authority_simple_heading_record_idx ON authority.simple_heading (record); diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 0c82fc89d9..ac23dbb803 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -40,7 +40,7 @@ CREATE TRIGGER metabib_identifier_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.identifier_field_entry FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('identifier'); -CREATE INDEX metabib_identifier_field_entry_index_vector_idx ON metabib.identifier_field_entry USING GIST (index_vector); +CREATE INDEX metabib_identifier_field_entry_index_vector_idx ON metabib.identifier_field_entry USING GIN (index_vector); CREATE INDEX metabib_identifier_field_entry_value_idx ON metabib.identifier_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; CREATE INDEX metabib_identifier_field_entry_source_idx ON metabib.identifier_field_entry (source); @@ -50,7 +50,7 @@ CREATE TABLE metabib.combined_identifier_field_entry ( index_vector tsvector NOT NULL ); CREATE UNIQUE INDEX metabib_combined_identifier_field_entry_fakepk_idx ON metabib.combined_identifier_field_entry (record, COALESCE(metabib_field::TEXT,'')); -CREATE INDEX metabib_combined_identifier_field_entry_index_vector_idx ON metabib.combined_identifier_field_entry USING GIST (index_vector); +CREATE INDEX metabib_combined_identifier_field_entry_index_vector_idx ON metabib.combined_identifier_field_entry USING GIN (index_vector); CREATE INDEX metabib_combined_identifier_field_source_idx ON metabib.combined_identifier_field_entry (metabib_field); CREATE TABLE metabib.title_field_entry ( @@ -64,7 +64,7 @@ CREATE TRIGGER metabib_title_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.title_field_entry FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('title'); -CREATE INDEX metabib_title_field_entry_index_vector_idx ON metabib.title_field_entry USING GIST (index_vector); +CREATE INDEX metabib_title_field_entry_index_vector_idx ON metabib.title_field_entry USING GIN (index_vector); CREATE INDEX metabib_title_field_entry_value_idx ON metabib.title_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; CREATE INDEX metabib_title_field_entry_source_idx ON metabib.title_field_entry (source); @@ -74,7 +74,7 @@ CREATE TABLE metabib.combined_title_field_entry ( index_vector tsvector NOT NULL ); CREATE UNIQUE INDEX metabib_combined_title_field_entry_fakepk_idx ON metabib.combined_title_field_entry (record, COALESCE(metabib_field::TEXT,'')); -CREATE INDEX metabib_combined_title_field_entry_index_vector_idx ON metabib.combined_title_field_entry USING GIST (index_vector); +CREATE INDEX metabib_combined_title_field_entry_index_vector_idx ON metabib.combined_title_field_entry USING GIN (index_vector); CREATE INDEX metabib_combined_title_field_source_idx ON metabib.combined_title_field_entry (metabib_field); CREATE TABLE metabib.author_field_entry ( @@ -88,7 +88,7 @@ CREATE TRIGGER metabib_author_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.author_field_entry FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('author'); -CREATE INDEX metabib_author_field_entry_index_vector_idx ON metabib.author_field_entry USING GIST (index_vector); +CREATE INDEX metabib_author_field_entry_index_vector_idx ON metabib.author_field_entry USING GIN (index_vector); CREATE INDEX metabib_author_field_entry_value_idx ON metabib.author_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; CREATE INDEX metabib_author_field_entry_source_idx ON metabib.author_field_entry (source); @@ -98,7 +98,7 @@ CREATE TABLE metabib.combined_author_field_entry ( index_vector tsvector NOT NULL ); CREATE UNIQUE INDEX metabib_combined_author_field_entry_fakepk_idx ON metabib.combined_author_field_entry (record, COALESCE(metabib_field::TEXT,'')); -CREATE INDEX metabib_combined_author_field_entry_index_vector_idx ON metabib.combined_author_field_entry USING GIST (index_vector); +CREATE INDEX metabib_combined_author_field_entry_index_vector_idx ON metabib.combined_author_field_entry USING GIN (index_vector); CREATE INDEX metabib_combined_author_field_source_idx ON metabib.combined_author_field_entry (metabib_field); CREATE TABLE metabib.subject_field_entry ( @@ -112,7 +112,7 @@ CREATE TRIGGER metabib_subject_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.subject_field_entry 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_index_vector_idx ON metabib.subject_field_entry USING GIN (index_vector); CREATE INDEX metabib_subject_field_entry_value_idx ON metabib.subject_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source); @@ -122,7 +122,7 @@ CREATE TABLE metabib.combined_subject_field_entry ( index_vector tsvector NOT NULL ); CREATE UNIQUE INDEX metabib_combined_subject_field_entry_fakepk_idx ON metabib.combined_subject_field_entry (record, COALESCE(metabib_field::TEXT,'')); -CREATE INDEX metabib_combined_subject_field_entry_index_vector_idx ON metabib.combined_subject_field_entry USING GIST (index_vector); +CREATE INDEX metabib_combined_subject_field_entry_index_vector_idx ON metabib.combined_subject_field_entry USING GIN (index_vector); CREATE INDEX metabib_combined_subject_field_source_idx ON metabib.combined_subject_field_entry (metabib_field); CREATE TABLE metabib.keyword_field_entry ( @@ -136,7 +136,7 @@ CREATE TRIGGER metabib_keyword_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); -CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON metabib.keyword_field_entry USING GIST (index_vector); +CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON metabib.keyword_field_entry USING GIN (index_vector); CREATE INDEX metabib_keyword_field_entry_value_idx ON metabib.keyword_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; CREATE INDEX metabib_keyword_field_entry_source_idx ON metabib.keyword_field_entry (source); @@ -146,7 +146,7 @@ CREATE TABLE metabib.combined_keyword_field_entry ( index_vector tsvector NOT NULL ); CREATE UNIQUE INDEX metabib_combined_keyword_field_entry_fakepk_idx ON metabib.combined_keyword_field_entry (record, COALESCE(metabib_field::TEXT,'')); -CREATE INDEX metabib_combined_keyword_field_entry_index_vector_idx ON metabib.combined_keyword_field_entry USING GIST (index_vector); +CREATE INDEX metabib_combined_keyword_field_entry_index_vector_idx ON metabib.combined_keyword_field_entry USING GIN (index_vector); CREATE INDEX metabib_combined_keyword_field_source_idx ON metabib.combined_keyword_field_entry (metabib_field); CREATE TABLE metabib.series_field_entry ( @@ -160,7 +160,7 @@ CREATE TRIGGER metabib_series_field_entry_fti_trigger BEFORE UPDATE OR INSERT ON metabib.series_field_entry FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('series'); -CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field_entry USING GIST (index_vector); +CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field_entry USING GIN (index_vector); CREATE INDEX metabib_series_field_entry_value_idx ON metabib.series_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; CREATE INDEX metabib_series_field_entry_source_idx ON metabib.series_field_entry (source); @@ -170,7 +170,7 @@ CREATE TABLE metabib.combined_series_field_entry ( index_vector tsvector NOT NULL ); CREATE UNIQUE INDEX metabib_combined_series_field_entry_fakepk_idx ON metabib.combined_series_field_entry (record, COALESCE(metabib_field::TEXT,'')); -CREATE INDEX metabib_combined_series_field_entry_index_vector_idx ON metabib.combined_series_field_entry USING GIST (index_vector); +CREATE INDEX metabib_combined_series_field_entry_index_vector_idx ON metabib.combined_series_field_entry USING GIN (index_vector); CREATE INDEX metabib_combined_series_field_source_idx ON metabib.combined_series_field_entry (metabib_field); CREATE VIEW metabib.combined_all_field_entry AS @@ -566,7 +566,7 @@ CREATE TABLE metabib.record_attr ( id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE, attrs HSTORE NOT NULL DEFAULT ''::HSTORE ); -CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIST (attrs); +CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIN (attrs); CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ((attrs->'date1')); CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ((attrs->'date1'),(attrs->'date2')); */ @@ -653,7 +653,7 @@ CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec (substring(valu /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */ CREATE INDEX metabib_full_rec_value_tpo_index ON metabib.real_full_rec (substring(value,1,1024) text_pattern_ops); CREATE INDEX metabib_full_rec_record_idx ON metabib.real_full_rec (record); -CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING GIST (index_vector); +CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING GIN (index_vector); CREATE INDEX metabib_full_rec_isxn_caseless_idx ON metabib.real_full_rec (LOWER(value)) WHERE tag IN ('020', '022', '024'); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql new file mode 100644 index 0000000000..cd97165622 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.dont_get_the_gist.sql @@ -0,0 +1,91 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DO LANGUAGE plpgsql $$ +DECLARE + ind RECORD; + tablist TEXT; +BEGIN + + -- We only want to mess with gist indexes in stock Evergreen. + -- If you've added your own convert them or don't as you see fit. + PERFORM + FROM pg_index idx + JOIN pg_class cls ON cls.oid=idx.indexrelid + JOIN pg_namespace sc ON sc.oid = cls.relnamespace + JOIN pg_class tab ON tab.oid=idx.indrelid + JOIN pg_attribute at ON (at.attnum = ANY(idx.indkey) AND at.attrelid = tab.oid) + JOIN pg_am am ON am.oid=cls.relam + WHERE am.amname = 'gist' + AND cls.relname IN ( + 'authority_full_rec_index_vector_idx', + 'authority_simple_heading_index_vector_idx', + 'metabib_identifier_field_entry_index_vector_idx', + 'metabib_combined_identifier_field_entry_index_vector_idx', + 'metabib_title_field_entry_index_vector_idx', + 'metabib_combined_title_field_entry_index_vector_idx', + 'metabib_author_field_entry_index_vector_idx', + 'metabib_combined_author_field_entry_index_vector_idx', + 'metabib_subject_field_entry_index_vector_idx', + 'metabib_combined_subject_field_entry_index_vector_idx', + 'metabib_keyword_field_entry_index_vector_idx', + 'metabib_combined_keyword_field_entry_index_vector_idx', + 'metabib_series_field_entry_index_vector_idx', + 'metabib_combined_series_field_entry_index_vector_idx', + 'metabib_full_rec_index_vector_idx' + ); + + IF NOT FOUND THEN + RETURN; + END IF; + + tablist := ''; + + RAISE NOTICE 'Converting GIST indexes into GIN indexes...'; + + FOR ind IN SELECT sc.nspname AS sch, tab.relname AS tab, cls.relname AS idx, at.attname AS col + FROM pg_index idx + JOIN pg_class cls ON cls.oid=idx.indexrelid + JOIN pg_namespace sc ON sc.oid = cls.relnamespace + JOIN pg_class tab ON tab.oid=idx.indrelid + JOIN pg_attribute at ON (at.attnum = ANY(idx.indkey) AND at.attrelid = tab.oid) + JOIN pg_am am ON am.oid=cls.relam + WHERE am.amname = 'gist' + AND cls.relname IN ( + 'authority_full_rec_index_vector_idx', + 'authority_simple_heading_index_vector_idx', + 'metabib_identifier_field_entry_index_vector_idx', + 'metabib_combined_identifier_field_entry_index_vector_idx', + 'metabib_title_field_entry_index_vector_idx', + 'metabib_combined_title_field_entry_index_vector_idx', + 'metabib_author_field_entry_index_vector_idx', + 'metabib_combined_author_field_entry_index_vector_idx', + 'metabib_subject_field_entry_index_vector_idx', + 'metabib_combined_subject_field_entry_index_vector_idx', + 'metabib_keyword_field_entry_index_vector_idx', + 'metabib_combined_keyword_field_entry_index_vector_idx', + 'metabib_series_field_entry_index_vector_idx', + 'metabib_combined_series_field_entry_index_vector_idx', + 'metabib_full_rec_index_vector_idx' + ); + LOOP + -- Move existing index out of the way so there's no difference between new databases and upgraded databases + EXECUTE FORMAT('ALTER INDEX %I.%I RENAME TO %I_gist', ind.sch, ind.idx, ind.idx); + + -- Meet the new index, same as the old index (almost) + EXECUTE FORMAT('CREATE INDEX %I ON %I.%I USING GIN (%I)', ind.idx, ind.sch, ind.tab, ind.col); + + -- And drop the old index + EXECUTE FORMAT('DROP INDEX %I.%I_gist', ind.sch, ind.idx); + + tablist := tablist || ' ' || ind.sch || '.' || ind.tab || E'\n'; + + END LOOP; + + RAISE NOTICE E'Conversion Complete.\n\n You should run a VACUUM ANALYZE on the following tables soon:\n%', tablist; + +END $$; + +COMMIT; + -- 2.43.2