From 6205d9b43d60939fdc173ff77e661fa7ebe3dbdd Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Thu, 13 Jun 2013 01:16:23 -0400 Subject: [PATCH 1/1] Retain index granularity with minimal bloat The previous approach to reducing index bloat arguably went too far, in that analytics such as separately catalogued songs for an album were all simply aggregated together in a single metabib.title_field_entry row, rather than being added as separated metabib.title_field_entry rows for each unique value. To avoid the original problem of exact duplicate rows being inserted, we now check for an existing matching row before inserting into the index. A good test record is title "Cello concerto." which results in 1 title proper row and 4 added entry title rows in metabib.title_field_entry after this change. Signed-off-by: Dan Scott --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 13 +++++++++++-- 1 file changed, 11 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 a989a4fee2..68d44fd102 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -641,14 +641,23 @@ BEGIN VALUES (mbe_id, ind_data.field, ind_data.source); END IF; - IF ind_data.search_field AND NOT b_skip_search THEN - EXECUTE $$ + -- Avoid inserting duplicate rows, but retain granularity of being + -- able to search browse fields with "starts with" type operators + -- (for example, for titles of songs in music albums) + IF (ind_data.search_field OR ind_data.browse_field) AND NOT b_skip_search THEN + EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class || + '_field_entry WHERE field = $1 AND source = $2 AND value = $3' + INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value; + -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id; + IF mbe_id IS NULL THEN + 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 IF; END LOOP; -- 2.43.2