Bug 1892238 - quick search does not find a bug which contains a specific string

Improve full FTS rebuild time.

Change-Id: I70c5a46544753f26ad90fb2329cdcaa82b9dbd3c

1 files changed, 8 insertions, 54 deletions

diff --git a/extensions/RedHat/Extension.pm b/extensions/RedHat/Extension.pm
index 73d6a478d..d05469f78 100644
--- a/extensions/RedHat/Extension.pm
+++ b/extensions/RedHat/Extension.pm
@@ -6273,66 +6273,20 @@ sub _rebuild_fts {
   my $file = 'extensions/RedHat/bin/rebuild_fts_index.sql';
 
 ## Bug 1265486.  This will rebuild the full text search indexes
-## It will reequire table locks, so should be done offline
+## It will require table locks, so should be done offline
+## Turns out one big transaction takes about 66% of the time of the loop.
+## e.g. 2 hours instead of 3 hours!
   $dbh->do(<<'SQL');
 
 DROP INDEX IF EXISTS comments_vect_idx;
 DROP INDEX IF EXISTS comments_noprivate_vect_idx;
 DROP INDEX IF EXISTS short_desc_vect;
 
-CREATE OR REPLACE FUNCTION public.update_fulltext()
- RETURNS integer
-AS $$
-DECLARE
-    rowdata bugs_fulltext%ROWTYPE;
-    count integer;
-    printer integer;
-BEGIN
-    count := 0;
-
-    printer := 0;
-    FOR rowdata IN SELECT * FROM bugs_fulltext WHERE comments IS NOT NULL ORDER BY bug_id LOOP
-        EXECUTE 'UPDATE bugs_fulltext SET comments_vect = to_tsvector(''english'','
-         || quote_literal(escape_special_tokens(rowdata.comments)) || ') WHERE bug_id = ' || rowdata.bug_id;
-        count = count + 1;
-        printer = printer + 1;
-        IF printer >= 1000 THEN
-            RAISE NOTICE '(comments) Bug %', rowdata.bug_id;
-            printer := 0;
-        END IF;
-    END LOOP;
-
-    printer := 0;
-    FOR rowdata IN SELECT * FROM bugs_fulltext WHERE comments_noprivate IS NOT NULL ORDER BY bug_id LOOP
-        EXECUTE 'UPDATE bugs_fulltext SET comments_noprivate_vect = to_tsvector(''english'','
-         || quote_literal(escape_special_tokens(rowdata.comments_noprivate)) || ') WHERE bug_id = ' || rowdata.bug_id;
-        count = count + 1;
-        printer = printer + 1;
-        IF printer >= 1000 THEN
-            RAISE NOTICE '(comments_noprivate) Bug %', rowdata.bug_id;
-            printer := 0;
-        END IF;
-    END LOOP;
-
-    printer := 0;
-    FOR rowdata IN SELECT * FROM bugs_fulltext WHERE short_desc IS NOT NULL ORDER BY bug_id LOOP
-        EXECUTE 'UPDATE bugs_fulltext SET short_desc_vect = to_tsvector(''english'','
-         || quote_literal(escape_special_tokens(rowdata.short_desc)) || ') WHERE bug_id = ' || rowdata.bug_id;
-        count = count + 1;
-        printer = printer + 1;
-        IF printer >= 1000 THEN
-            RAISE NOTICE '(short_desc) Bug %', rowdata.bug_id;
-            printer := 0;
-        END IF;
-    END LOOP;
-
-    RETURN count;
-END;
-$$  LANGUAGE plpgsql;
-
-SELECT public.update_fulltext();
-
-DROP FUNCTION update_fulltext();
+UPDATE bugs_fulltext SET 
+    comments_vect = to_tsvector('english', quote_literal(escape_special_tokens(comments))),
+    comments_noprivate_vect = to_tsvector('english', quote_literal(escape_special_tokens(comments_noprivate))),
+    short_desc_vect = to_tsvector('english', quote_literal(escape_special_tokens(short_desc)))
+;
 
 CREATE INDEX comments_vect_idx ON bugs_fulltext USING gin(comments_vect);
 CREATE INDEX comments_noprivate_vect_idx ON bugs_fulltext USING gin(comments_noprivate_vect);