From 95cd0f6e9e319f8eb94b941f5df611dd95d4fac9 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 17 May 2012 13:59:04 -0400 Subject: [PATCH] Maybe speed up search https://bugs.launchpad.net/evergreen/+bug/844374 For investigative purposes, I'm bringing back James' original idea of removing naco_normalize (now spelled search_normalize) from the left side of the comparison. In order to support this we need to apply all normalizers to the value column in the field entry tables. That's done in a straight-forward way be just pushing the data into the column at the appropriate time in the appropriate trigger. There is a small loss of accuracy, because some fields don't get that normalizations when being stored, but the user-supplied value will still be normalized. But, these are just for relevance adjustment and not matching, so it's not too bad IMO. Next, we make QueryParser forget about applying search_normalize to the column (but still to the value). Testing appreciated! (basically untested at this point) You'll need to rewrite the the field entry tables. The following should be enough: UPDATE metabib.author_field_entry SET id = id; UPDATE metabib.title_field_entry SET id = id; UPDATE metabib.subject_field_entry SET id = id; UPDATE metabib.series_field_entry SET id = id; UPDATE metabib.keyword_field_entry SET id = id; UPDATE metabib.identifier_field_entry SET id = id; Signed-off-by: Mike Rylander --- .../lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm | 6 +++--- Open-ILS/src/sql/Pg/002.schema.config.sql | 10 ++-------- 2 files changed, 5 insertions(+), 11 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index a405c1fbef..e7991c2b63 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -705,12 +705,12 @@ sub rel_bump { return '' if (!@$only_atoms); if ($bump eq 'first_word') { - return " /* first_word */ COALESCE(NULLIF( (search_normalize(".$node->table_alias.".value) ~ ('^'||search_normalize(".$self->QueryParser->quote_phrase_value($only_atoms->[0]->content)."))), FALSE )::INT * $multiplier, 1)"; + return " /* first_word */ COALESCE(NULLIF( ".$node->table_alias.".value ~ ('^'||search_normalize(".$self->QueryParser->quote_phrase_value($only_atoms->[0]->content)."))), FALSE )::INT * $multiplier, 1)"; } elsif ($bump eq 'full_match') { - return " /* full_match */ COALESCE(NULLIF( (search_normalize(".$node->table_alias.".value) ~ ('^'||". + return " /* full_match */ COALESCE(NULLIF( ".$node->table_alias.".value ~ ('^'||". join( "||' '||", map { "search_normalize(".$self->QueryParser->quote_phrase_value($_->content).")" } @$only_atoms )."||'\$')), FALSE )::INT * $multiplier, 1)"; } elsif ($bump eq 'word_order') { - return " /* word_order */ COALESCE(NULLIF( (search_normalize(".$node->table_alias.".value) ~ (". + return " /* word_order */ COALESCE(NULLIF( ".$node->table_alias.".value ~ (". join( "||'.*'||", map { "search_normalize(".$self->QueryParser->quote_phrase_value($_->content).")" } @$only_atoms ).")), FALSE )::INT * $multiplier, 1)"; } diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index a51ec77e4a..8b582aa866 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -783,14 +783,6 @@ BEGIN END LOOP; - NEW.value := value; - END IF; - - IF NEW.index_vector = ''::tsvector THEN - RETURN NEW; - END IF; - - IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN FOR normalizer IN SELECT n.func AS func, n.param_count AS param_count, @@ -809,6 +801,8 @@ BEGIN ')' INTO value; END LOOP; + + NEW.value := value; END IF; IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN -- 2.11.0