From a7b5226fc1ee73896b0145866c4fab6070d2683a Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 11 Aug 2010 20:18:26 +0000
Subject: [PATCH] Use a COALESCE/NULLIF nested structure instead of CASE (one
 branch, easy peasy) for 4x speedup in the core search query

git-svn-id: svn://svn.open-ils.org/ILS/trunk@17174 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 .../OpenILS/Application/Storage/Driver/Pg/QueryParser.pm | 16 +++++++---------
 1 file changed, 7 insertions(+), 9 deletions(-)

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 cbff570567..4717380a11 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
@@ -570,17 +570,13 @@ sub rel_bump {
     return '' if (!@$only_atoms);
 
     if ($bump eq 'first_word') {
-        return "/* first_word */ CASE WHEN naco_normalize(".$node->table_alias.".value) ".
-                    "LIKE naco_normalize(".$self->QueryParser->quote_value($only_atoms->[0]->content).") \|\| '\%' ".
-                    "THEN $multiplier ELSE 1 END";
+        return " /* first_word */ COALESCE(NULLIF( (naco_normalize(".$node->table_alias.".value) ~ ('^'||naco_normalize(".$self->QueryParser->quote_value($only_atoms->[0]->content).")))::BOOL::INT, 0 ) * $multiplier, 1)";
     } elsif ($bump eq 'full_match') {
-        return "/* full_match */ CASE WHEN naco_normalize(".$node->table_alias.".value) ".
-                    "LIKE". join( '||\'%\'||', map { " naco_normalize(".$self->QueryParser->quote_value($_->content).") " } @$only_atoms ) .
-                    "THEN $multiplier ELSE 1 END";
+        return " /* full_match */ COALESCE(NULLIF( (naco_normalize(".$node->table_alias.".value) ~ ('^'||".
+                    join( "||' '||", map { "naco_normalize(".$self->QueryParser->quote_value($_->content).")" } @$only_atoms )."||'\$'))::BOOL::INT, 0 ) * $multiplier, 1)";
     } elsif ($bump eq 'word_order') {
-        return "/* word_order */ CASE WHEN naco_normalize(".$node->table_alias.".value) ".
-                    "LIKE '\%'||". join( '||\'%\'||', map { " naco_normalize(".$self->QueryParser->quote_value($_->content).") " } @$only_atoms ) . '||\'%\' '.
-                    "THEN $multiplier ELSE 1 END";
+        return " /* word_order */ COALESCE(NULLIF( (naco_normalize(".$node->table_alias.".value) ~ (".
+                    join( "||'.*'||", map { "naco_normalize(".$self->QueryParser->quote_value($_->content).")" } @$only_atoms )."))::BOOL::INT, 0 ) * $multiplier, 1)";
     }
 
     return '';
@@ -634,6 +630,8 @@ sub flatten {
                         next if ($used_bumps{$b});
                         $used_bumps{$b} = 1;
 
+                        next if ($$bumps{$b}{multiplier} == 1); # optimization to remove unneeded bumps
+
                         my $bump_case = $self->rel_bump( $node, $b, $$bumps{$b}{multiplier} );
                         $node_rank .= "\n\t\t\t\t * " . $bump_case if ($bump_case);
                     }
-- 
2.11.0