From f2a11f55a53d3f5a5af1510a4d1016711e0763da Mon Sep 17 00:00:00 2001
From: Dan Scott <dscott@laurentian.ca>
Date: Wed, 16 May 2012 00:29:11 -0400
Subject: [PATCH] Optimize full text search with a CTE

The tsquery used directly in a JOIN was resulting in bad
plan with lots of nested loops and operations. Using a CTE
to define the query improves the plan.

Also, instead of ARRAY_AGG(DISTINCT foo), just return the
whole thing in an array. Not great for dupes, but we can
handle those inside search.query_parser_fts if we really
need to.

Signed-off-by: Dan Scott <dscott@laurentian.ca>
Signed-off-by: Mike Rylander <mrylander@gmail.com>
---
 .../Storage/Driver/Pg/QueryParser.pm          | 20 +++++++++++++++----
 1 file changed, 16 insertions(+), 4 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 efd83e07a6..a405c1fbef 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
@@ -655,10 +655,20 @@ sub toSQL {
     } else {
         $flat_where = "AND $flat_where";
     }
+    my $with = $$flat_plan{with};
+
+    # Need an array for query parser db function; this gives a better plan
+    # than the ARRAY_AGG(DISTINCT m.source) option as of PostgreSQL 9.1
+    my $agg_records = 'ARRAY[m.source] AS records';
+    if ($key =~ /metarecord/) {
+        # metarecord searches still require the ARRAY_AGG approach
+        $agg_records = 'ARRAY_AGG(DISTINCT m.source) AS records';
+    }
 
     my $sql = <<SQL;
+$with
 SELECT  $key AS id,
-        ARRAY_ACCUM(DISTINCT m.source) AS records,
+        $agg_records,
         $rel AS rel,
         $rank AS rank, 
         FIRST(mrd.attrs->'date1') AS tie_break
@@ -712,6 +722,7 @@ sub flatten {
 
     my $from = shift || '';
     my $where = shift || '(';
+    my $with = '';
 
     my @rank_list;
     for my $node ( @{$self->query_nodes} ) {
@@ -730,11 +741,12 @@ sub flatten {
                 my $node_rank = 'COALESCE(' . $node->rank . " * ${talias}.weight, 0.0)";
 
                 my $core_limit = $self->QueryParser->core_limit || 25000;
-                $from .= "\n\tLEFT JOIN (\n\t\tSELECT fe.*, fe_weight.weight, x.tsq /* search */\n\t\t  FROM  $table AS fe";
+                $from .= "\n\tLEFT JOIN (\n\t\tSELECT fe.*, fe_weight.weight, xq.tsq /* search */\n\t\t  FROM  $table AS fe";
                 $from .= "\n\t\t\tJOIN config.metabib_field AS fe_weight ON (fe_weight.id = fe.field)";
 
                 if ($node->dummy_count < @{$node->only_atoms} ) {
-                    $from .= "\n\t\t\tJOIN (SELECT ". $node->tsquery ." AS tsq ) AS x ON (fe.index_vector @@ x.tsq)";
+                    $with.= "\n\t\t\tWITH xq AS (SELECT ". $node->tsquery ." AS tsq )";
+                    $from .= "\n\t\t\tJOIN xq ON (fe.index_vector @@ xq.tsq)";
                 } else {
                     $from .= "\n\t\t\t, (SELECT NULL::tsquery AS tsq ) AS x";
                 }
@@ -810,7 +822,7 @@ sub flatten {
         }
     }
 
-    return { rank_list => \@rank_list, from => $from, where => $where.')' };
+    return { rank_list => \@rank_list, from => $from, where => $where.')', with => $with };
 
 }
 
-- 
2.43.2