From 38a7ef37196a550ecebdb8d1631bdccf47a86620 Mon Sep 17 00:00:00 2001 From: miker Date: Sun, 11 Jul 2010 23:35:00 +0000 Subject: [PATCH] break facets out to a separate table for speed git-svn-id: svn://svn.open-ils.org/ILS/trunk@16904 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 22 +----- .../OpenILS/Application/Search/Biblio.pm | 2 - .../Storage/Driver/Pg/QueryParser.pm | 2 +- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 33 ++++++--- .../upgrade/0330.schema.facet_entry_table.sql | 73 +++++++++++++++++++ 6 files changed, 100 insertions(+), 34 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0330.schema.facet_entry_table.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 4df20313d9..1ad2fb0ffb 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -2156,26 +2156,10 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - - SELECT * FROM ( - SELECT * FROM metabib.author_field_entry - UNION ALL - SELECT * FROM metabib.keyword_field_entry - UNION ALL - SELECT * FROM metabib.identifier_field_entry - UNION ALL - SELECT * FROM metabib.title_field_entry - UNION ALL - SELECT * FROM metabib.subject_field_entry - UNION ALL - SELECT * FROM metabib.series_field_entry - )x - WHERE x.index_vector = '' - - - + + + diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm b/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm index d93d7a7980..38b444baf9 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm @@ -1359,12 +1359,10 @@ sub cache_facets { }, from => { mfae => { - cmf => { field => 'id', fkey => 'field' }, mmrsm => { field => 'source', fkey => 'source' } } }, where => { - '+cmf' => 'facet_field', '+mmrsm' => { $count_field => $results } } } diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index c05d7afbd0..02e54f4ee1 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -650,7 +650,7 @@ sub flatten { my $table = $node->table; my $talias = $node->table_alias; - $from .= "\n\tJOIN (\n\t\tSELECT * /* facet */\n\t\t FROM $table\n\t\t WHERE index_vector = ''::TSVECTOR AND ". + $from .= "\n\tJOIN (\n\t\tSELECT * /* facet */\n\t\t FROM metabib.facet_entry\n\t\t WHERE ". "SUBSTRING(value,1,1024) IN (" . join(",", map { $self->QueryParser->quote_value($_) } @{$node->values}) . ")". "\n\t\t\tAND field IN (SELECT id FROM config.metabib_field WHERE field_class = ". $self->QueryParser->quote_value($node->classname) ." AND facet_field"; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 7c54ad6ea3..cdc26e7023 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0329'); -- dbs +INSERT INTO config.upgrade_log (version) VALUES ('0330'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 298807f6de..3c03e0aade 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -125,6 +125,17 @@ CREATE INDEX metabib_series_field_entry_value_idx ON metabib.series_field_entry CREATE INDEX metabib_series_field_entry_source_idx ON metabib.series_field_entry (source); +CREATE TABLE metabib.facet_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL +); +CREATE INDEX metabib_facet_entry_field_idx ON metabib.facet_entry (field); +CREATE INDEX metabib_facet_entry_value_idx ON metabib.facet_entry (SUBSTRING(value,1,1024)); +CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source); + + CREATE TABLE metabib.rec_descriptor ( id BIGSERIAL PRIMARY KEY, record BIGINT, @@ -736,29 +747,29 @@ CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT DECLARE fclass RECORD; ind_data metabib.field_entry_template%ROWTYPE; - ind_vector TSVECTOR; BEGIN FOR fclass IN SELECT * FROM config.metabib_class LOOP -- RAISE NOTICE 'Emptying out %', fclass.name; EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id; END LOOP; + DELETE FROM metabib.facet_entry WHERE source = bib_id; + FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP IF ind_data.field < 0 THEN - ind_vector = ''; ind_data.field = -1 * ind_data.field; + INSERT INTO metabib.facet_entry (field, source, value) + VALUES (ind_data.field, ind_data.source, ind_data.value); ELSE - ind_vector = NULL; + EXECUTE $$ + INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value) + VALUES ($$ || + quote_literal(ind_data.field) || $$, $$ || + quote_literal(ind_data.source) || $$, $$ || + quote_literal(ind_data.value) || + $$);$$; END IF; - EXECUTE $$ - INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value, index_vector) - VALUES ($$ || - quote_literal(ind_data.field) || $$, $$ || - quote_literal(ind_data.source) || $$, $$ || - quote_literal(ind_data.value) || $$, $$ || - COALESCE(quote_literal(ind_vector),'NULL'::TEXT) || - $$);$$; END LOOP; RETURN; diff --git a/Open-ILS/src/sql/Pg/upgrade/0330.schema.facet_entry_table.sql b/Open-ILS/src/sql/Pg/upgrade/0330.schema.facet_entry_table.sql new file mode 100644 index 0000000000..799aa43b7a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0330.schema.facet_entry_table.sql @@ -0,0 +1,73 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0330'); + +CREATE TABLE metabib.facet_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL +); + +INSERT INTO metabib.facet_entry (source, field, value) + SELECT source, field, value FROM ( + SELECT source, field, value FROM metabib.author_field_entry + UNION ALL + SELECT source, field, value FROM metabib.keyword_field_entry + UNION ALL + SELECT source, field, value FROM metabib.identifier_field_entry + UNION ALL + SELECT source, field, value FROM metabib.title_field_entry + UNION ALL + SELECT source, field, value FROM metabib.subject_field_entry + UNION ALL + SELECT source, field, value FROM metabib.series_field_entry + )x + WHERE x.index_vector = ''; + +DELETE FROM metabib.author_field_entry WHERE index_vector = ''; +DELETE FROM metabib.keyword_field_entry WHERE index_vector = ''; +DELETE FROM metabib.identifier_field_entry WHERE index_vector = ''; +DELETE FROM metabib.title_field_entry WHERE index_vector = ''; +DELETE FROM metabib.subject_field_entry WHERE index_vector = ''; +DELETE FROM metabib.series_field_entry WHERE index_vector = ''; + +CREATE INDEX metabib_facet_entry_field_idx ON metabib.facet_entry (field); +CREATE INDEX metabib_facet_entry_value_idx ON metabib.facet_entry (SUBSTRING(value,1,1024)); +CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source); + +CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT ) RETURNS VOID AS $func$ +DECLARE + fclass RECORD; + ind_data metabib.field_entry_template%ROWTYPE; +BEGIN + FOR fclass IN SELECT * FROM config.metabib_class LOOP + -- RAISE NOTICE 'Emptying out %', fclass.name; + EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id; + END LOOP; + + DELETE FROM metabib.facet_entry WHERE source = bib_id; + + FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP + IF ind_data.field < 0 THEN + ind_data.field = -1 * ind_data.field; + INSERT INTO metabib.facet_entry (field, source, value) + VALUES (ind_data.field, ind_data.source, ind_data.value); + ELSE + EXECUTE $$ + INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value) + VALUES ($$ || + quote_literal(ind_data.field) || $$, $$ || + quote_literal(ind_data.source) || $$, $$ || + quote_literal(ind_data.value) || + $$);$$; + END IF; + + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.43.2