Search backend changes: faster results with trigram index + ranking

With this ranking system, searching for titles like 'L' and 'ONE.' is
now at least possible, and YU-NO at least shows up on the first page
when searching for "yu no". The actual normalization and matching
algorithm hasn't really changed, except that all search terms must now
match a single title, but there's still a whole bunch of false
positives.

Ranking is not available through the API yet.

The trigram index should make it possible to do site-wide searching at a
more reasonable speed, I'll experiment with that later.

28 files changed, 370 insertions, 250 deletions

diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index ab4b120e..7a9d7a80 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -639,7 +639,7 @@ my %GET_VN = (
       [ stra  => 'v.olang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ],
     ],
     search => [
-      [ str   => 'v.c_search LIKE ALL (search_query(:value:))', {'~',1} ],
+      [ str   => 'EXISTS(SELECT 1 FROM search_cache sc WHERE sc.id = v.id AND sc.label LIKE ALL (search_query(:value:)))', {'~',1} ],
     ],
     tags => [
       [ int   => 'v.id :op:(SELECT vid FROM tags_vn_inherit WHERE tag = :value:)',   {'=' => 'IN', '!=' => 'NOT IN'}, process => \'g' ],
@@ -903,7 +903,7 @@ my %GET_PRODUCER = (
       [ stra  => 'p.lang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ],
     ],
     search => [
-      [ str   => 'p.c_search LIKE ALL (search_query(:value:))', {'~',1} ],
+      [ str   => 'EXISTS(SELECT 1 FROM search_cache sc WHERE sc.id = p.id AND sc.label LIKE ALL (search_query(:value:)))', {'~',1} ],
     ],
   },
 );
@@ -1016,7 +1016,7 @@ my %GET_CHARACTER = (
       [ str   => 'c.title[4] ILIKE :value:', {'~',1}, process => \'like' ]
     ],
     search => [
-      [ str   => 'c.c_search LIKE ALL (search_query(:value:))', {'~',1} ],
+      [ str   => 'EXISTS(SELECT 1 FROM search_cache sc WHERE sc.id = c.id AND sc.label LIKE ALL (search_query(:value:)))', {'~',1} ],
     ],
     vn => [
       [ 'int' => 'c.id IN(SELECT cv.id FROM chars_vns cv WHERE cv.vid = :value:)', {'=',1}, process => \'v' ],
@@ -1121,7 +1121,7 @@ my %GET_STAFF = (
       [ inta  => 's.id IN(SELECT sa.id FROM staff_alias sa WHERE sa.aid IN(:value:))', {'=',1}, range => [1,1e6], join => ',' ],
     ],
     search => [
-      [ str   => 's.id IN(SELECT sa.id FROM staff_alias sa WHERE sa.c_search LIKE ALL (search_query(:value:)))', {'~',1} ],
+      [ str   => 'EXISTS(SELECT 1 FROM search_cache sc WHERE sc.id = s.id AND sc.label LIKE ALL (search_query(:value:)))', {'~',1} ],
     ],
   },
 );
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index 52af1996..df055b93 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -367,8 +367,8 @@ vn => [ 0, 0, sub {
 
   pg_cmd q{
     SELECT id, title[1+1]
-      FROM vnt
-     WHERE NOT hidden AND c_search LIKE ALL (search_query($1))
+      FROM vnt v
+     WHERE NOT hidden AND EXISTS(SELECT 1 FROM search_cache sc WHERE sc.id = v.id AND sc.label LIKE ALL (search_query($1)))
      ORDER BY sorttitle
      LIMIT 6
   }, [ $q ], sub {
@@ -386,10 +386,10 @@ p => [ 0, 0, sub {
   return $irc->send_msg(PRIVMSG => $chan, 'You forgot the search query, dummy~~!') if !$q;
   pg_cmd q{
     SELECT id, name AS title
-    FROM producers p
-    WHERE hidden = FALSE AND c_search LIKE ALL (search_query($1))
-    ORDER BY name
-    LIMIT 6
+      FROM producers p
+     WHERE NOT hidden AND EXISTS(SELECT 1 FROM search_cache sc WHERE sc.id = p.id AND sc.label LIKE ALL (search_query($1)))
+     ORDER BY name
+     LIMIT 6
   }, [ $q ], sub {
     my $res = shift;
     return if pg_expect $res, 1;
diff --git a/lib/VNDB/Schema.pm b/lib/VNDB/Schema.pm
index ba303f4a..694162c3 100644
--- a/lib/VNDB/Schema.pm
+++ b/lib/VNDB/Schema.pm
@@ -39,12 +39,13 @@ sub schema {
 
         if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) {
             die "Unexpected 'CREATE TABLE $1'\n" if $table;
+            next if /PARTITION OF/;
             $table = $1;
             $schema{$table}{name} = $table;
             $schema{$table}{dbentry_type} = $1 if /--.*\s+dbentry_type=(.)/;
             $schema{$table}{cols} = [];
 
-        } elsif(/^\s*\);/) {
+        } elsif(/^\s*\)(?: PARTITION .+)?;/) {
             $table = undef;
 
         } elsif(/^\s+(?:CHECK|CONSTRAINT)/) {
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm
index 21e72bab..25a49548 100644
--- a/lib/VNWeb/AdvSearch.pm
+++ b/lib/VNWeb/AdvSearch.pm
@@ -311,7 +311,7 @@ my @TYPE; # stack of query types, $TYPE[0] is the top-level query, $TYPE[$#TYPE]
 
 
 f v => 80 => 'id',        { vndbid => 'v' }, sql => sub { sql 'v.id', $_[0], \$_ };
-f v => 81 => 'search',    {}, '=' => sub { sql 'v.c_search LIKE ALL (search_query(', \$_, '))' };
+f v => 81 => 'search',    { searchquery => 1 }, '=' => sub { $_->sql_where('v', 'v.id') };
 f v =>  2 => 'lang',      { enum => \%LANGUAGE }, '=' => sub { sql 'v.c_languages && ARRAY', \$_, '::language[]' };
 f v =>  3 => 'olang',     { enum => \%LANGUAGE }, '=' => sub { sql 'v.olang =', \$_ };
 f v =>  4 => 'platform',  { enum => \%PLATFORM }, '=' => sub { sql 'v.c_platforms && ARRAY', \$_, '::platform[]' };
@@ -358,7 +358,7 @@ f v =>  6 => 'developer-id', { vndbid => 'p' }, '=' => sub { sql 'v.c_developers
 
 
 f r => 80 => 'id',       { vndbid => 'r' }, sql => sub { sql 'r.id', $_[0], \$_ };
-f r => 81 => 'search',   {}, '=' => sub { sql 'r.c_search LIKE ALL (search_query(', \$_, '))' };
+f r => 81 => 'search',   { searchquery => 1 }, '=' => sub { $_->sql_where('r', 'r.id') };
 f r =>  2 => 'lang',     { enum => \%LANGUAGE },
     sql_list => sub {
         my($neg, $all, $val) = @_;
@@ -409,7 +409,7 @@ f r => 63 => 'doujin',      { uint => 1, range => [1,1] }, '=' => sub { 'r.douji
 
 
 f c => 80 => 'id',         { vndbid => 'c' }, sql => sub { sql 'c.id', $_[0], \$_ };
-f c => 81 => 'search',     {}, '=' => sub { sql 'c.c_search LIKE ALL (search_query(', \$_, '))' };
+f c => 81 => 'search',     { searchquery => 1 }, '=' => sub { $_->sql_where('c', 'c.id') };
 f c =>  2 => 'role',       { enum => \%CHAR_ROLE  }, '=' => sub { $#TYPE && $TYPE[$#TYPE-1] eq 'v' ? sql 'cv.role =', \$_ : sql 'c.id IN(SELECT id FROM chars_vns WHERE role =', \$_, ')' };
 f c =>  3 => 'blood_type', { enum => \%BLOOD_TYPE }, '=' => sub { sql 'c.bloodt =', \$_ };
 f c =>  4 => 'sex',        { enum => \%GENDER },     '=' => sub { sql 'c.gender =', \$_ };
@@ -463,16 +463,16 @@ f s =>  5 => 'role',      { enum => [ 'seiyuu', keys %CREDIT_TYPE ] },
 f p =>  2 => 'lang',      { enum => \%LANGUAGE }, '=' => sub { sql 'p.lang =', \$_ };
 f p =>  3 => 'id',        { vndbid => 'p' }, sql => sub { sql 'p.id', $_[0], \$_ };
 f p =>  4 => 'type',      { enum => \%PRODUCER_TYPE }, '=' => sub { sql 'p.type =', \$_ };
-f p => 80 => 'search',    {}, '=' => sub { sql 'p.c_search LIKE ALL (search_query(', \$_, '))' };
+f p => 80 => 'search',    { searchquery => 1 }, '=' => sub { $_->sql_where('p', 'p.id') };
 
 
 f g =>  2 => 'id',        { vndbid => 'g' }, sql => sub { sql 't.id', $_[0], \$_ };
 f g =>  3 => 'category',  { enum => \%TAG_CATEGORY }, '=' => sub { sql 't.cat =', \$_ };
-f g => 80 => 'search',    {}, '=' => sub { sql 't.c_search LIKE ALL (search_query(', \$_, '))' };
+f g => 80 => 'search',    { searchquery => 1 }, '=' => sub { $_->sql_where('g', 't.id') };
 
 
 f i =>  2 => 'id',        { vndbid => 'i' }, sql => sub { sql 't.id', $_[0], \$_ };
-f i => 80 => 'search',    {}, '=' => sub { sql 't.c_search LIKE ALL (search_query(', \$_, '))' };
+f i => 80 => 'search',    { searchquery => 1 }, '=' => sub { $_->sql_where('i', 't.id') };
 
 
 
diff --git a/lib/VNWeb/Chars/Elm.pm b/lib/VNWeb/Chars/Elm.pm
index 5a3e1e35..5d9901dc 100644
--- a/lib/VNWeb/Chars/Elm.pm
+++ b/lib/VNWeb/Chars/Elm.pm
@@ -2,20 +2,15 @@ package VNWeb::Chars::Elm;
 
 use VNWeb::Prelude;
 
-elm_api Chars => undef, { search => {} }, sub {
+elm_api Chars => undef, { search => { searchquery => 1 } }, sub {
     my $q = shift->{search};
 
     my $l = tuwf->dbPagei({ results => 15, page => 1 },
         'SELECT c.id, c.title[1+1] AS title, c.title[1+1+1+1] AS alttitle, c.main, cm.title[1+1] AS main_title, cm.title[1+1+1+1] AS main_alttitle
-           FROM (SELECT MIN(prio), id FROM (',
-			sql_join('UNION ALL',
-                $q =~ /^$RE{cid}$/ ? sql('SELECT 1, id FROM chars WHERE id =', \"$+{id}") : (),
-                sql('SELECT  1+substr_score(lower(name),', \sql_like($q),'), id FROM chars WHERE c_search LIKE ALL (search_query(', \$q, '))'),
-            ), ') x(prio,id) GROUP BY id) x(prio, id)
-           JOIN', charst, 'c ON c.id = x.id
+           FROM', charst, 'c', $q->sql_join('c', 'c.id'), '
            LEFT JOIN', charst, 'cm ON cm.id = c.main
           WHERE NOT c.hidden
-          ORDER BY x.prio, c.title[1+1]
+          ORDER BY sc.score DESC, c.sorttitle
     ');
     for (@$l) {
         $_->{main} = { id => $_->{main}, title => $_->{main_title}, alttitle => $_->{main_alttitle} } if $_->{main};
diff --git a/lib/VNWeb/Chars/List.pm b/lib/VNWeb/Chars/List.pm
index 2423400e..d1e1b13e 100644
--- a/lib/VNWeb/Chars/List.pm
+++ b/lib/VNWeb/Chars/List.pm
@@ -82,11 +82,11 @@ sub enrich_listing {
 
 TUWF::get qr{/c(?:/(?<char>all|[a-z0]))?}, sub {
     my $opt = tuwf->validate(get =>
-        q => { onerror => undef },
+        q => { searchquery => 1 },
         p => { upage => 1 },
         f => { advsearch_err => 'c' },
         ch=> { onerror => [], type => 'array', scalar => 1, values => { onerror => undef, enum => ['0', 'a'..'z'] } },
-        fil => { required => 0 },
+        fil=>{ required => 0 },
         s => { tableopts => $TABLEOPTS },
     )->data;
     $opt->{ch} = $opt->{ch}[0];
@@ -108,15 +108,17 @@ TUWF::get qr{/c(?:/(?<char>all|[a-z0]))?}, sub {
 
     my $where = sql_and
         'NOT c.hidden', $opt->{f}->sql_where(),
-        $opt->{q} ? sql 'c.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
         defined($opt->{ch}) ? sql 'match_firstchar(c.sorttitle, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);
     db_maytimeout {
-        $count = tuwf->dbVali('SELECT count(*) FROM', charst, 'c WHERE', $where);
+        $count = tuwf->dbVali('SELECT count(*) FROM', charst, 'c WHERE', sql_and $where, $opt->{q}->sql_where('c', 'c.id'));
         $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
-            SELECT c.id, c.title, c.gender, c.image FROM', charst, 'c WHERE', $where, 'ORDER BY c.sorttitle, c.id'
+            SELECT c.id, c.title, c.gender, c.image
+              FROM', charst, 'c', $opt->{q}->sql_join('c', 'c.id'), '
+             WHERE', $where, '
+             ORDER BY', $opt->{q} ? 'sc.score DESC, ' : (), 'c.sorttitle, c.id'
         ) : [];
     } || (($count, $list) = (undef, []));
 
diff --git a/lib/VNWeb/Discussions/Elm.pm b/lib/VNWeb/Discussions/Elm.pm
index 312dc92c..500cc3b9 100644
--- a/lib/VNWeb/Discussions/Elm.pm
+++ b/lib/VNWeb/Discussions/Elm.pm
@@ -4,38 +4,29 @@ use VNWeb::Prelude;
 
 # Autocompletion search results for boards
 elm_api Boards => undef, {
-    search => {},
+    search => { searchquery => 1 },
 }, sub {
     return elm_Unauth if !auth->permBoard;
     my $q = shift->{search};
-    my $qs = sql_like $q;
+    my $qs = sql_like "$q";
 
-    my sub item {
-        my($tbl, $type, $title, $filt, $query) = @_;
-        my $title_score = sql "1+substr_score(lower($title),", \$qs, ')';
-        sql 'SELECT',
-                $q =~ /^$type$RE{num}$/
-                ? sql 'CASE WHEN id =', \$q, 'THEN 0 ELSE', $title_score, 'END'
-                : $title_score,
-                ',', \$type, "::board_type, id, $title
-            FROM", $tbl, "x
-           WHERE", $filt, 'AND', sql_or(
-               $query, $q =~ /^$type$RE{num}$/ ? sql 'id =', \$q : ());
-    }
+    my $uscore = sql 'similarity(username, ', \$qs, ')';
+    $uscore = sql 'CASE WHEN id =', \$qs, 'THEN 1+1 ELSE', $uscore, 'END' if $qs =~ /^u$RE{num}$/;
 
     elm_BoardResult tuwf->dbPagei({ results => 10, page => 1 },
         'SELECT btype, iid, title
            FROM (',
              sql_join('UNION ALL',
-                 (map sql('SELECT 1, ', \$_, '::board_type, NULL::vndbid, NULL'),
-                     grep $q eq $_ || $BOARD_TYPE{$_}{txt} =~ /\Q$q/i,
+                 (map sql('SELECT 10, ', \"$_", '::board_type, NULL::vndbid, NULL'),
+                     grep $qs eq $_ || $BOARD_TYPE{$_}{txt} =~ /\Q$qs/i,
                      grep !$BOARD_TYPE{$_}{dbitem} && ($BOARD_TYPE{$_}{post_perm} eq 'board' || auth->permBoardmod),
                      keys %BOARD_TYPE),
-                 item(vnt, 'v', 'title[1+1]', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'),
-                 item(producerst, 'p', 'title[1+1]', 'NOT hidden', sql 'c_search LIKE ALL (search_query(', \$q, '))'),
-                 item('users', 'u', 'username', 'true', sql 'lower(username) LIKE', \lc "%$qs%"),
-             ), ') x(prio, btype, iid, title)
-           ORDER BY prio, btype, title'
+                 sql('SELECT score, \'v\', v.id, title[1+1] FROM', vnt, 'v', $q->sql_join('v', 'v.id'), 'WHERE NOT v.hidden'),
+                 sql('SELECT score, \'p\', p.id, title[1+1] FROM', producerst, 'p', $q->sql_join('p', 'p.id'), 'WHERE NOT p.hidden'),
+                 sql('SELECT', $uscore, ', \'u\', id, username FROM users WHERE lower(username) LIKE', \lc "%$qs%",
+                    $qs =~ /^u$RE{num}$/ ? ('OR id =', \$qs) : ())
+             ), ') x(score, btype, iid, title)
+           ORDER BY score DESC, btype, title'
     )
 };
 
diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index ad13c10a..4238e3b2 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -870,8 +870,8 @@ sub searchbox_ {
               a_ href => '/i',     $sel eq 'i' ? (class => 'sel') : (), 'Traits';
               a_ href => '/u/all', $sel eq 'u' ? (class => 'sel') : (), 'Users';
           };
-          input_ type => 'text', name => 'q', id => 'q', class => 'text', value => $value;
-          input_ type => 'submit', class => 'submit', value => 'Search!';
+          input_ type => 'text', name => 'q', id => 'q', class => 'text', value => "$value";
+          input_ type => 'submit', class => 'submit', name => 'sb', value => 'Search!';
       };
 }
 
diff --git a/lib/VNWeb/Producers/Elm.pm b/lib/VNWeb/Producers/Elm.pm
index 581ed071..ca6bf8c4 100644
--- a/lib/VNWeb/Producers/Elm.pm
+++ b/lib/VNWeb/Producers/Elm.pm
@@ -3,25 +3,17 @@ package VNWeb::Producers::Elm;
 use VNWeb::Prelude;
 
 elm_api Producers => undef, {
-    search => { type => 'array', values => { required => 0, default => '' } },
+    search => { searchquerya => 1 },
     hidden => { anybool => 1 },
 }, sub {
     my($data) = @_;
-    my @q = grep length $_, $data->{search}->@*;
-    die "No query" if !@q;
+    die "No query" if !$data->{search};
 
     elm_ProducerResult tuwf->dbPagei({ results => 15, page => 1 },
         'SELECT p.id, p.title[1+1] AS name, p.title[1+1+1+1] AS altname, p.hidden
-           FROM (',
-			sql_join('UNION ALL', map +(
-                /^$RE{pid}$/ ? sql('SELECT 1, id FROM producers WHERE id =', \"$+{id}") : (),
-                sql('SELECT 1+substr_score(lower(name),', \sql_like($_), '), id FROM producers WHERE c_search LIKE ALL (search_query(', \"$_", '))'),
-            ), @q),
-            ') x(prio, id)
-           JOIN', producerst, 'p ON p.id = x.id
-          WHERE', sql_and($data->{hidden} ? () : 'NOT p.hidden'), '
-          GROUP BY p.id, p.title, p.sorttitle, p.hidden
-          ORDER BY MIN(x.prio), p.sorttitle
+           FROM', producerst, 'p', $data->{search}->sql_join('p', 'p.id'),
+          $data->{hidden} ? () : 'WHERE NOT p.hidden', '
+          ORDER BY sc.score DESC, p.sorttitle
     ');
 };
 
diff --git a/lib/VNWeb/Producers/List.pm b/lib/VNWeb/Producers/List.pm
index 6542d14d..713bcd6e 100644
--- a/lib/VNWeb/Producers/List.pm
+++ b/lib/VNWeb/Producers/List.pm
@@ -27,7 +27,7 @@ TUWF::get qr{/p(?:/(?<char>all|[a-z0]))?}, sub {
     my $char = tuwf->capture('char');
     my $opt = tuwf->validate(get =>
         p => { upage => 1 },
-        q => { onerror => '' },
+        q => { searchquery => 1 },
         f => { advsearch_err => 'p' },
         ch=> { onerror => [], type => 'array', scalar => 1, values => { onerror => undef, enum => ['0', 'a'..'z'] } },
     )->data;
@@ -40,15 +40,17 @@ TUWF::get qr{/p(?:/(?<char>all|[a-z0]))?}, sub {
     $opt->{f} = advsearch_default 'p' if !$opt->{f}{query} && !defined tuwf->reqGet('f');
 
     my $where = sql_and 'NOT p.hidden', $opt->{f}->sql_where(),
-        $opt->{q} ? sql 'p.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
         defined($opt->{ch}) ? sql 'match_firstchar(p.sorttitle, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);
     db_maytimeout {
-        $count = tuwf->dbVali('SELECT COUNT(*) FROM', producerst, 'p WHERE', $where);
+        $count = tuwf->dbVali('SELECT COUNT(*) FROM', producerst, 'p WHERE', sql_and $where, $opt->{q}->sql_where('p', 'p.id'));
         $list = $count ? tuwf->dbPagei({ results => 150, page => $opt->{p} },
-            'SELECT p.id, p.title, p.lang FROM', producerst, 'p WHERE', $where, 'ORDER BY p.sorttitle'
+            'SELECT p.id, p.title, p.lang
+               FROM', producerst, 'p', $opt->{q}->sql_join('p', 'p.id'), '
+              WHERE', $where, '
+              ORDER BY', $opt->{q} ? 'sc.score DESC, ' : (), 'p.sorttitle'
         ) : [];
     } || (($count, $list) = (undef, []));
     $time = time - $time;
diff --git a/lib/VNWeb/Releases/List.pm b/lib/VNWeb/Releases/List.pm
index 3a0cbe34..311b976e 100644
--- a/lib/VNWeb/Releases/List.pm
+++ b/lib/VNWeb/Releases/List.pm
@@ -32,13 +32,15 @@ sub listing_ {
 
 TUWF::get qr{/r}, sub {
     my $opt = tuwf->validate(get =>
-        q => { onerror => undef },
+        q => { searchquery => 1 },
         p => { upage => 1 },
         f => { advsearch_err => 'r' },
-        s => { onerror => 'title', enum => [qw/released minage title/] },
+        s => { onerror => 'qscore', enum => [qw/qscore released minage title/] },
         o => { onerror => 'a', enum => ['a','d'] },
         fil => { required => 0 },
     )->data;
+    $opt->{s} = 'qscore' if $opt->{q} && tuwf->reqGet('sb');
+    $opt->{s} = 'title' if $opt->{s} eq 'qscore' && !$opt->{q};
 
     # URL compatibility with old filters
     if(!$opt->{f}->{query} && $opt->{fil}) {
@@ -50,21 +52,18 @@ TUWF::get qr{/r}, sub {
 
     $opt->{f} = advsearch_default 'r' if !$opt->{f}{query} && !defined tuwf->reqGet('f');
 
-    my $where = sql_and 'NOT r.hidden', $opt->{f}->sql_where(),
-        !$opt->{q} ? () : sql_or
-            sql('r.c_search LIKE ALL (search_query(', \$opt->{q}, '))'),
-            $opt->{q} =~ /^\d+$/ && gtintype($opt->{q}) ? sql 'r.gtin =', \$opt->{q} : (),
-            $opt->{q} =~ /^[a-zA-Z0-9-]+$/ ? sql 'r.catalog =', \$opt->{q} : ();
+    my $where = sql_and 'NOT r.hidden', $opt->{f}->sql_where();
 
     my $time = time;
     my($count, $list);
     db_maytimeout {
-        $count = tuwf->dbVali('SELECT count(*) FROM releases r WHERE', $where);
+        $count = tuwf->dbVali('SELECT count(*) FROM releases r WHERE', sql_and $where, $opt->{q}->sql_where('r', 'r.id'));
         $list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, '
             SELECT r.id, r.patch, r.released, r.gtin, ', sql_extlinks(r => 'r.'), '
-              FROM', releasest, 'r
+              FROM', releasest, 'r', $opt->{q}->sql_join('r', 'r.id'), '
              WHERE', $where, '
              ORDER BY', sprintf {
+                 qscore   => '10 - sc.score %s, r.sorttitle %1$s',
                  title    => 'r.sorttitle %s, r.released %1$s',
                  minage   => 'r.minage %s, r.sorttitle %1$s, r.released %1$s',
                  released => 'r.released %s, r.sorttitle %1$s, r.id %1$s',
diff --git a/lib/VNWeb/Staff/Elm.pm b/lib/VNWeb/Staff/Elm.pm
index 1e514f88..e116aac7 100644
--- a/lib/VNWeb/Staff/Elm.pm
+++ b/lib/VNWeb/Staff/Elm.pm
@@ -2,25 +2,15 @@ package VNWeb::Staff::Elm;
 
 use VNWeb::Prelude;
 
-elm_api Staff => undef, {
-    search => { type => 'array', values => { required => 0, default => '' } },
-}, sub {
-    my @q = grep length $_, shift->{search}->@*;
-    die "No query" if !@q;
+elm_api Staff => undef, { search => { searchquerya => 1 } }, sub {
+    my $q = shift->{search};
+    die "No query" if !$q;
 
     elm_StaffResult tuwf->dbPagei({ results => 15, page => 1 },
         'SELECT s.id, s.lang, s.aid, s.title[1+1], s.title[1+1+1+1] as alttitle
-           FROM (',
-			sql_join('UNION ALL', map +(
-                /^$RE{sid}$/ ? sql('SELECT 0, aid FROM staff_alias WHERE id =', \"$+{id}") : (),
-                sql('SELECT 1+substr_score(lower(name),', \sql_like($_), ')+substr_score(lower(latin),', \sql_like($_), '), aid
-                       FROM staff_alias WHERE c_search LIKE ALL (search_query(', \$_, '))'),
-            ), @q),
-            ') x(prio, aid)
-           JOIN', staff_aliast, 's ON s.aid = x.aid
+           FROM', staff_aliast, 's', $q->sql_join('s', 's.id', 's.aid'), '
           WHERE NOT s.hidden
-          GROUP BY s.id, s.lang, s.aid, s.title, s.sorttitle
-          ORDER BY MIN(x.prio), s.sorttitle
+          ORDER BY sc.score DESC, s.sorttitle
     ');
 };
 
diff --git a/lib/VNWeb/Staff/List.pm b/lib/VNWeb/Staff/List.pm
index add6015c..5bd0397a 100644
--- a/lib/VNWeb/Staff/List.pm
+++ b/lib/VNWeb/Staff/List.pm
@@ -24,7 +24,7 @@ sub listing_ {
 
 TUWF::get qr{/s(?:/(?<char>all|[a-z0]))?}, sub {
     my $opt = tuwf->validate(get =>
-        q => { onerror => undef },
+        q => { searchquery => 1 },
         p => { upage => 1 },
         f => { advsearch_err => 's' },
         n => { onerror => [], type => 'array', scalar => 1, values => { anybool => 1 } },
@@ -52,17 +52,19 @@ TUWF::get qr{/s(?:/(?<char>all|[a-z0]))?}, sub {
     $opt->{f} = advsearch_default 's' if !$opt->{f}{query} && !defined tuwf->reqGet('f');
 
     my $where = sql_and
-        $opt->{n} ? 'main = aid' : (),
-        'NOT hidden', $opt->{f}->sql_where(),
-        $opt->{q} ? sql 'c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
-        defined($opt->{ch}) ? sql 'match_firstchar(sorttitle, ', \$opt->{ch}, ')' : ();
+        $opt->{n} ? 's.main = s.aid' : (),
+        'NOT s.hidden', $opt->{f}->sql_where(),
+        defined($opt->{ch}) ? sql 'match_firstchar(s.sorttitle, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);
     db_maytimeout {
-        $count = tuwf->dbVali('SELECT count(*) FROM', staff_aliast, 's WHERE', $where);
+        $count = tuwf->dbVali('SELECT count(*) FROM', staff_aliast, 's WHERE', sql_and $where, $opt->{q}->sql_where('s', 's.id', 's.aid'));
         $list = $count ? tuwf->dbPagei({results => 150, page => $opt->{p}}, '
-            SELECT id, title, lang FROM', staff_aliast, 's WHERE', $where, 'ORDER BY sorttitle, aid'
+            SELECT s.id, s.title, s.lang
+              FROM', staff_aliast, 's', $opt->{q}->sql_join('s', 's.id', 's.aid'), '
+             WHERE', $where,
+            'ORDER BY', $opt->{q} ? 'sc.score DESC, ' : (), 's.sorttitle, s.aid'
         ) : [];
     } || (($count, $list) = (undef, []));
     $time = time - $time;
diff --git a/lib/VNWeb/TT/Elm.pm b/lib/VNWeb/TT/Elm.pm
index 3146ce23..3f8264b0 100644
--- a/lib/VNWeb/TT/Elm.pm
+++ b/lib/VNWeb/TT/Elm.pm
@@ -2,40 +2,27 @@ package VNWeb::TT::Elm;
 
 use VNWeb::Prelude;
 
-elm_api Tags => undef, { search => {} }, sub {
+elm_api Tags => undef, { search => { searchquery => 1 } }, sub {
     my $q = shift->{search};
-    my $qs = sql_like $q;
 
     elm_TagResult tuwf->dbPagei({ results => 15, page => 1 },
         'SELECT t.id, t.name, t.searchable, t.applicable, t.hidden, t.locked
-           FROM (',
-             sql_join('UNION ALL',
-                 $q =~ /^$RE{gid}$/ ? sql('SELECT 0, id FROM tags WHERE id =', \"$+{id}") : (),
-                 sql('SELECT  1+substr_score(lower(name),',  \$qs, '), id FROM tags WHERE c_search LIKE ALL(search_query(', \$q, '))'),
-             ), ') x (prio, id)
-           JOIN tags t ON t.id = x.id
+           FROM tags t', $q->sql_join('g', 't.id'), '
           WHERE NOT (t.hidden AND t.locked)
-          GROUP BY t.id, t.name, t.searchable, t.applicable, t.hidden, t.locked
-          ORDER BY MIN(x.prio), t.name
+          ORDER BY sc.score DESC, t.name
     ')
 };
 
 
-elm_api Traits => undef, { search => {} }, sub {
+elm_api Traits => undef, { search => { searchquery => 1 } }, sub {
     my $q = shift->{search};
-    my $qs = sql_like $q;
 
     elm_TraitResult tuwf->dbPagei({ results => 15, page => 1 },
         'SELECT t.id, t.name, t.searchable, t.applicable, t.defaultspoil, t.hidden, t.locked, g.id AS group_id, g.name AS group_name
-           FROM (SELECT MIN(prio), id FROM (',
-             sql_join('UNION ALL',
-                 $q =~ /^$RE{iid}$/ ? sql('SELECT 0, id FROM traits WHERE id =', \"$+{id}") : (),
-                 sql('SELECT  1+substr_score(lower(name),',  \$qs, '), id FROM traits WHERE c_search LIKE ALL(search_query(', \$q, '))'),
-             ), ') x(prio, id) GROUP BY id) x(prio,id)
-           JOIN traits t ON t.id = x.id
+           FROM traits t', $q->sql_join('i', 't.id'), '
            LEFT JOIN traits g ON g.id = t.group
           WHERE NOT (t.hidden AND t.locked)
-          ORDER BY x.prio, t.name
+          ORDER BY sc.score DESC, t.name
     ')
 };
 
diff --git a/lib/VNWeb/TT/List.pm b/lib/VNWeb/TT/List.pm
index b4bf2a36..ccabef31 100644
--- a/lib/VNWeb/TT/List.pm
+++ b/lib/VNWeb/TT/List.pm
@@ -38,15 +38,16 @@ sub listing_ {
 TUWF::get qr{/(?<type>[gi])/list}, sub {
     my $type = tuwf->capture('type');
     my $opt = tuwf->validate(get =>
-        s => { onerror => 'name', enum => ['added', 'name', 'vns', 'items'] },
+        s => { onerror => 'qscore', enum => ['qscore', 'added', 'name', 'vns', 'items'] },
         o => { onerror => 'a', enum => ['a', 'd'] },
         p => { upage => 1 },
         t => { onerror => undef, enum => [ -1..2 ] },
         a => { undefbool => 1 },
         b => { undefbool => 1 },
-        q => { onerror => '' },
+        q => { searchquery => 1 },
     )->data;
     $opt->{s} = 'items' if $opt->{s} eq 'vns';
+    $opt->{s} = 'name' if $opt->{s} eq 'qscore' && !$opt->{q};
     $opt->{t} = undef if $opt->{t} && $opt->{t} == -1; # for legacy URLs
 
     my $where = sql_and
@@ -54,16 +55,15 @@ TUWF::get qr{/(?<type>[gi])/list}, sub {
             $opt->{t} == 0 ? 'hidden AND NOT locked' :
             $opt->{t} == 1 ? 'hidden AND locked' : 'NOT hidden',
         defined $opt->{a} ? sql 'applicable =', \$opt->{a} : (),
-        defined $opt->{b} ? sql 'searchable =', \$opt->{b} : (),
-        $opt->{q} ? sql 'c_search LIKE ALL (search_query(', \$opt->{q}, '))' : ();
+        defined $opt->{b} ? sql 'searchable =', \$opt->{b} : ();
 
     my $table = $type eq 'g' ? 'tags' : 'traits';
-    my $count = tuwf->dbVali("SELECT COUNT(*) FROM $table t WHERE", $where);
+    my $count = tuwf->dbVali("SELECT COUNT(*) FROM $table t WHERE", sql_and $where, $opt->{q}->sql_where($type, 't.id'));
     my $list = tuwf->dbPagei({ results => 50, page => $opt->{p} },'
-        SELECT id, name, hidden, locked, searchable, applicable, c_items,', sql_totime('added'), "as added
-          FROM $table
-         WHERE ", $where, '
-         ORDER BY', {qw|added id  name name  items c_items|}->{$opt->{s}}, {qw|a ASC d DESC|}->{$opt->{o}}, ', id'
+        SELECT t.id, name, hidden, locked, searchable, applicable, c_items,', sql_totime('added'), "as added
+          FROM $table t", $opt->{q}->sql_join($type, 't.id'), '
+         WHERE ', $where, '
+         ORDER BY', {qscore => '10 - sc.score', qw|added t.id  name name  items c_items|}->{$opt->{s}}, {qw|a ASC d DESC|}->{$opt->{o}}, ', id'
     );
 
     enrich_group $type, $list;
diff --git a/lib/VNWeb/TableOpts.pm b/lib/VNWeb/TableOpts.pm
index a4100dbb..1ab4eadd 100644
--- a/lib/VNWeb/TableOpts.pm
+++ b/lib/VNWeb/TableOpts.pm
@@ -135,7 +135,8 @@ TUWF::set('custom_validations')->{tableopts} = sub {
     my($t) = @_;
     +{ onerror => sub {
         my $d = $t->{pref} && auth ? tuwf->dbVali('SELECT', $t->{pref}, 'FROM users_prefs WHERE id =', \auth->uid) : undef;
-        bless([$d // $t->{default},$t], __PACKAGE__)
+        my $o = bless([$d // $t->{default},$t], __PACKAGE__);
+        $o->fixup;
     }, func => sub {
         my $obj = bless [undef, $t], __PACKAGE__;
         my($val,$ord) = $_[0] =~ m{^([^/]+)/([ad])$} ? ($1,$2) : ($_[0],undef);
@@ -148,12 +149,22 @@ TUWF::set('custom_validations')->{tableopts} = sub {
         } else {
             $obj->[0] = _dec($_[0]) // return 0;
         }
-        $_[0] = $obj;
+        $_[0] = $obj->fixup;
         # We could do strict validation on the individual fields, but the methods below can handle incorrect data.
         1;
     } }
 };
 
+sub fixup {
+    my($obj) = @_;
+    # Reset sort_col and order to their default if the current sort_col id does not exist.
+    if(!$obj->[1]{sort_ids}[ $obj->sort_col_id ]) {
+        $obj->set_sort_col_id(sort_col_id([$obj->[1]{default}]));
+        $obj->set_order(order([$obj->[1]{default}]));
+    }
+    $obj
+}
+
 sub query_encode { _enc $_[0][0] }
 
 sub view  { $views[$_[0][0] & 3] || $views[$_[0][1]{views}[0]] }
@@ -188,7 +199,7 @@ sub sort_param {
 sub sql_order {
     my($self) = @_;
     my($v,$o) = $self->@*;
-    my $col = $o->{sort_ids}[ $self->sort_col_id ] || $o->{sort_ids}[ sort_col_id([$o->{default}]) ];
+    my $col = $o->{sort_ids}[ $self->sort_col_id ];
     die "No column to sort on" if !$col;
     my $order = $self->order ? 'DESC' : 'ASC';
     my $opposite_order = $self->order ? 'ASC' : 'DESC';
diff --git a/lib/VNWeb/ULists/List.pm b/lib/VNWeb/ULists/List.pm
index aabd3c90..885b32fe 100644
--- a/lib/VNWeb/ULists/List.pm
+++ b/lib/VNWeb/ULists/List.pm
@@ -19,17 +19,18 @@ sub opt {
     state $s_vnlist   = $s_default->sort_param(title => 'a')->vis_param(qw/label vote added started finished/)->query_encode;
     state $s_votes    = $s_default->sort_param(voted => 'd')->vis_param(qw/vote voted/)->query_encode;
     state $s_wishlist = $s_default->sort_param(title => 'a')->vis_param(qw/label added/)->query_encode;
+    state @all = (mul => 0, p => 1, f => '', q => tuwf->compile({ searchquery => 1 })->validate(undef)->data);
 
     my $opt =
         # Presets
-        tuwf->reqGet('vnlist')   ? { mul => 0, p => 1, l => [1,2,3,4,7,0], f => '', s => $s_vnlist,   load 'vnlist' } :
-        tuwf->reqGet('votes')    ? { mul => 0, p => 1, l => [7],           f => '', s => $s_votes,    load 'votes'  } :
-        tuwf->reqGet('wishlist') ? { mul => 0, p => 1, l => [5],           f => '', s => $s_wishlist, load 'wish'   } :
+        tuwf->reqGet('vnlist')   ? { @all, l => [1,2,3,4,7,0], s => $s_vnlist,   load 'vnlist' } :
+        tuwf->reqGet('votes')    ? { @all, l => [7],           s => $s_votes,    load 'votes'  } :
+        tuwf->reqGet('wishlist') ? { @all, l => [5],           s => $s_wishlist, load 'wish'   } :
         # Full options
         tuwf->validate(get =>
             p => { upage => 1 },
             ch=> { onerror => [], type => 'array', scalar => 1, values => { onerror => undef, enum => ['0', 'a'..'z'] } },
-            q => { onerror => undef },
+            q => { searchquery => 1 },
             %VNWeb::ULists::Elm::SAVED_OPTS,
             # Compat for old URLs
             o => { onerror => undef, enum => ['a', 'd'] },
@@ -213,9 +214,9 @@ sub listing_ {
     my $where = sql_and
         sql('uv.uid =', \$uid),
         $opt->{f}->sql_where(),
+        $opt->{q}->sql_where('v', 'v.id'),
         $own ? () : 'NOT uv.c_private AND NOT v.hidden',
         @where_vns ? sql_or(@where_vns) : (),
-        $opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
         defined($opt->{ch}) ? sql 'match_firstchar(v.sorttitle, ', \$opt->{ch}, ')' : ();
 
     my $count = tuwf->dbVali('SELECT count(*) FROM ulist_vns uv JOIN', vnt, 'v ON v.id = uv.vid WHERE', $where);
diff --git a/lib/VNWeb/VN/Elm.pm b/lib/VNWeb/VN/Elm.pm
index eab50ba9..eae316ef 100644
--- a/lib/VNWeb/VN/Elm.pm
+++ b/lib/VNWeb/VN/Elm.pm
@@ -3,25 +3,17 @@ package VNWeb::VN::Elm;
 use VNWeb::Prelude;
 
 elm_api VN => undef, {
-    search => { type => 'array', values => { required => 0, default => '' } },
+    search => { searchquerya => 1 },
     hidden => { anybool => 1 },
 }, sub {
     my($data) = @_;
-    my @q = grep length $_, $data->{search}->@*;
-    die "No query" if !@q;
+    die "No query" if !$data->{search};
 
     elm_VNResult tuwf->dbPagei({ results => $data->{hidden}?50:15, page => 1 },
         'SELECT v.id, v.title[1+1] AS title, v.hidden
-           FROM (',
-            sql_join('UNION ALL', map +(
-                /^$RE{vid}$/ ? sql('SELECT 1, id FROM vn WHERE id =', \"$+{id}") : (),
-                sql('SELECT 1+substr_score(lower(sorttitle),', \sql_like($_), '), id FROM vnt WHERE c_search LIKE ALL (search_query(', \"$_", '))'),
-            ), @q),
-            ') x(prio, id)
-           JOIN', vnt, 'v ON v.id = x.id
-          WHERE', sql_and($data->{hidden} ? () : 'NOT v.hidden'), '
-          GROUP BY v.id, v.title, v.sorttitle, v.hidden
-          ORDER BY MIN(x.prio), v.sorttitle
+           FROM', vnt, 'v', $data->{search}->sql_join('v', 'v.id'),
+          $data->{hidden} ? () : 'WHERE NOT v.hidden', '
+          ORDER BY sc.score DESC, v.sorttitle
     ');
 };
 
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index 6cf78c69..81b55585 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -9,12 +9,14 @@ use VNWeb::TT::Lib 'tagscore_';
 
 # Returns the tableopts config for:
 # - this VN list ('vn')
+# - this VN list with a search query ('vns')
 # - the VN listing on tags ('tags')
 # - a user's VN list ('ulist')
 # The latter has different numeric identifiers, a sad historical artifact. :(
 sub TABLEOPTS {
     my $tags = $_[0] eq 'tags';
-    my $vn = $_[0] eq 'vn';
+    my $vns = $_[0] eq 'vns';
+    my $vn = $vns || $_[0] eq 'vn';
     my $ulist = $_[0] eq 'ulist';
     die if !$tags && !$vn && !$ulist;
 
@@ -25,16 +27,23 @@ sub TABLEOPTS {
             name => 'Tag score',
             compat => 'tagscore',
             sort_id => 0,
-            sort_sql => 'tvi.rating ?o, v.title',
+            sort_sql => 'tvi.rating ?o, v.sorttitle',
             sort_default => 'desc',
             sort_num => 1,
         }) : (),
+        $vns ? (qscore => {
+            name => 'Relevance',
+            sort_id => 0,
+            sort_sql => 'sc.score !o, v.sorttitle',
+            sort_default => 'asc',
+            sort_num => 1,
+        }) : (),
         title => {
             name => 'Title',
             compat => 'title',
             sort_id => $ulist ? 0 : 1,
             sort_sql => 'v.sorttitle',
-            sort_default => $tags ? undef : 'asc',
+            sort_default => $tags || $vns ? undef : 'asc',
         },
         $ulist ? (
             voted => {
@@ -157,6 +166,7 @@ sub TABLEOPTS {
 }
 
 my $TABLEOPTS = TABLEOPTS 'vn';
+my $TABLEOPTS_Q = TABLEOPTS 'vns';
 
 sub len_ {
     my($v) = @_;
@@ -346,17 +356,18 @@ sub enrich_listing {
 
 TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
     my $opt = tuwf->validate(get =>
-        q => { onerror => undef },
-        sq=> { onerror => undef },
+        q => { searchquery => 1 },
+        sq=> { searchquery => 1 },
         p => { upage => 1 },
         f => { advsearch_err => 'v' },
-        s => { tableopts => $TABLEOPTS },
         ch=> { onerror => [], type => 'array', scalar => 1, values => { onerror => undef, enum => ['0', 'a'..'z'] } },
         fil  => { required => 0 },
         rfil => { required => 0 },
         cfil => { required => 0 },
     )->data;
-    $opt->{q} //= $opt->{sq};
+    $opt->{q} = $opt->{sq} if !$opt->{q};
+    $opt->{s} = tuwf->validate(get => s => { tableopts => $opt->{q} ? $TABLEOPTS_Q : $TABLEOPTS })->data;
+    $opt->{s} = $opt->{s}->sort_param(qscore => 'a') if $opt->{q} && tuwf->reqGet('sb');
     $opt->{ch} = $opt->{ch}[0];
 
     # compat with old URLs
@@ -383,18 +394,17 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
 
     my $where = sql_and
         'NOT v.hidden', $opt->{f}->sql_where(),
-        $opt->{q} ? sql 'v.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (),
         defined($opt->{ch}) ? sql 'match_firstchar(v.sorttitle, ', \$opt->{ch}, ')' : ();
 
     my $time = time;
     my($count, $list);
     db_maytimeout {
-        $count = tuwf->dbVali('SELECT count(*) FROM', vnt, 'v WHERE', $where);
+        $count = tuwf->dbVali('SELECT count(*) FROM', vnt, 'v WHERE', sql_and $where, $opt->{q}->sql_where('v', 'v.id'));
         $list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
             SELECT v.id, v.title, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
                  , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang',
                    $opt->{s}->vis('length') ? ', v.length, v.c_length, v.c_lengthnum' : (), '
-              FROM', vnt, 'v
+              FROM', vnt, 'v', $opt->{q}->sql_join('v', 'v.id'), '
              WHERE', $where, '
              ORDER BY', $opt->{s}->sql_order(),
         ) : [];
@@ -409,7 +419,7 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
         form_ action => '/v', method => 'get', sub {
             div_ class => 'mainbox', sub {
                 h1_ 'Browse visual novels';
-                searchbox_ v => $opt->{q}//'';
+                searchbox_ v => $opt->{q};
                 p_ class => 'browseopts', sub {
                     button_ type => 'submit', name => 'ch', value => ($_//''), ($_//'') eq ($opt->{ch}//'') ? (class => 'optselected') : (), !defined $_ ? 'ALL' : $_ ? uc $_ : '#'
                         for (undef, 'a'..'z', 0);
diff --git a/lib/VNWeb/Validation.pm b/lib/VNWeb/Validation.pm
index 1e9c2d01..2aca9162 100644
--- a/lib/VNWeb/Validation.pm
+++ b/lib/VNWeb/Validation.pm
@@ -73,6 +73,8 @@ TUWF::set custom_validations => {
     gtin        => { required => 0, default => 0, func => sub { $_[0] = 0 if !length $_[0]; $_[0] eq 0 || gtintype($_[0]) } },
     rdate       => { uint => 1, func => \&_validate_rdate },
     fuzzyrdate  => { required => 0, default => 0, func => \&_validate_fuzzyrdate },
+    searchquery => { required => 0, default => bless([],'VNWeb::Validate::SearchQuery'), func => sub { $_[0] = bless([$_[0]], 'VNWeb::Validate::SearchQuery') } },
+    searchquerya=> { type => 'array', values => { required => 0, default => '' }, default => bless([],'VNWeb::Validate::SearchQuery'), func => sub { $_[0] = bless([grep length $_, $_[0]->@*], 'VNWeb::Validate::SearchQuery') } },
     # Calendar date, limited to 1970 - 2099 for sanity.
     # TODO: Should also validate whether the day exists, currently "2022-11-31" is accepted, but that's a bug.
     caldate     => { regex => qr/^(?:19[7-9][0-9]|20[0-9][0-9])-(?:0[1-9]|1[0-2])-(?:0[1-9]|[12][0-9]|3[01])$/ },
@@ -379,4 +381,69 @@ sub viewset {
         '-'.auth->csrftoken(0, 'view');
 }
 
+
+# Object returned by the 'searchquery' validation, has some handy methods for generating SQL.
+package VNWeb::Validate::SearchQuery {
+    use TUWF;
+    use VNWeb::DB;
+
+    sub query_encode { $_[0][0] }
+
+    sub _words {
+        $_[0][1] //= length $_[0][0]
+            ? [ map s/%//rg, tuwf->dbVali('SELECT search_query(', \$_[0][0], ')')->@* ]
+            : []
+    }
+
+    use overload bool => sub { $_[0]->_words->@* > 0 };
+    use overload '""' => sub { $_[0][0]//'' };
+
+    sub _isvndbid { my $l = $_[0]->_words; @$l == 1 && $l->[0] =~ /^[vrpcsgi]$num$/ }
+
+    sub _where {
+        my($self, $type) = @_;
+        my $lst = $self->_words;
+        my @keywords = map sql('sc.label LIKE', \"%${_}%"), @$lst;
+        +(
+            $type ? "sc.id BETWEEN '${type}1' AND vndbid_max('$type')" : (),
+            $self->_isvndbid()
+                ? (sql 'sc.id =', \$lst->[0], 'OR', sql_and(@keywords))
+                : @keywords
+        )
+    }
+
+    sub sql_where {
+        my($self, $type, $id, $subid) = @_;
+        return '1=1' if !$self;
+        sql 'EXISTS(SELECT 1 FROM search_cache sc WHERE', sql_and(
+            sql('sc.id =', $id), $subid ? sql('sc.subid =', $subid) : (),
+            $self->_where($type),
+        ), ')';
+    }
+
+    # Returns a subquery that can be joined to get the search score.
+    # Columns (id, subid, score)
+    sub sql_score {
+        my($self, $type) = @_;
+        my $lst = $self->_words;
+        my $q = join '', @$lst;
+        sql '(SELECT id, subid, max(sc.prio * (', VNWeb::DB::sql_join('+',
+                $self->_isvndbid() ? sql('CASE WHEN sc.id =', \$q, 'THEN 1+1 ELSE 0 END') : (),
+                sql('CASE WHEN sc.label LIKE', \"$q%", 'THEN 1::float/(1+1) ELSE 0 END'),
+                sql('similarity(sc.label,', \$q, ')'),
+            ), ')) AS score
+            FROM search_cache sc
+           WHERE', sql_and($self->_where($type)), '
+           GROUP BY id, subid
+        )';
+    }
+
+    # Optionally returns a JOIN clause for sql_score, aliassed 'sc'
+    sub sql_join {
+        my($self, $type, $id, $subid) = @_;
+        return '' if !$self;
+        sql 'JOIN', $self->sql_score($type), 'sc ON sc.id =', $id, $subid ? ('AND sc.subid =', $subid) : ();
+    }
+};
+
 1;
diff --git a/sql/func.sql b/sql/func.sql
index 5e381f46..67f2eea4 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -21,43 +21,85 @@ CREATE OR REPLACE FUNCTION fmtip(n ipinfo) RETURNS text AS $$
 $$ LANGUAGE SQL IMMUTABLE;
 
 
-CREATE OR REPLACE FUNCTION search_gen_vn(vnid vndbid) RETURNS text AS $$
-  SELECT coalesce(string_agg(t, ' '), '') FROM (
-    SELECT t FROM (
-                SELECT search_norm_term(title) FROM vn_titles WHERE id = vnid
-      UNION ALL SELECT search_norm_term(latin) FROM vn_titles WHERE id = vnid
-      UNION ALL SELECT search_norm_term(a) FROM vn, regexp_split_to_table(alias, E'\n') a(a) WHERE vnid = id
-      -- Remove the various editions/version strings from release titles,
-      -- this reduces the index size and makes VN search more relevant.
-      -- People looking for editions should be using the release search.
-      UNION ALL SELECT regexp_replace(search_norm_term(t), '(?:
-           体験|ダウンロド|初回限定|初回|限定|通常|廉価|豪華|追加|コレクション
-          |パッケージ|ダウンロード|ベスト|復刻|新装|7対応|版|生産|リメイク
-          |first|press|limited|regular|standard|full|remake
-          |pack|package|boxed|download|complete|popular|premium|deluxe|collectors?|collection
-          |lowprice|price|free|best|thebest|cheap|budget|reprint|bundle|set|renewal|extended
-          |special|trial|demo|allages|voiced?|uncensored|web|patch|port|r18|18|earlyaccess
-          |cd|cdr|cdrom|dvdrom|dvd|dvdpg|disk|disc|steam|for
-          |(?:win|windows)(?:7|10|95)?|vista|pc9821|support(?:ed)?
-          |(?:parts?|vol|volumes?|chapters?|v|ver|versions?)(?:[0-9]+)
-          |editions?|version|production|thebest|append|scenario|dlc)+$', '', 'xg')
-        FROM (
-          SELECT title FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
-          UNION ALL
-          SELECT latin FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = vnid
-        ) r(t)
-    ) x(t) WHERE t IS NOT NULL AND t <> '' GROUP BY t ORDER BY t
-  ) x(t);
-$$ LANGUAGE SQL;
+
+-- Helper function for `update_search()`
+CREATE OR REPLACE FUNCTION update_search_terms(objid vndbid) RETURNS SETOF record AS $$
+DECLARE
+  e int; -- because I'm too lazy to write out 'NULL::int' every time.
+BEGIN
+  CASE vndbid_type(objid)
+  WHEN 'v' THEN RETURN QUERY
+              SELECT e, 3, search_norm_term(title) FROM vn_titles WHERE id = objid
+    UNION ALL SELECT e, 3, search_norm_term(latin) FROM vn_titles WHERE id = objid
+    UNION ALL SELECT e, 2, search_norm_term(a) FROM vn, regexp_split_to_table(alias, E'\n') a(a) WHERE objid = id
+    -- Remove the various editions/version strings from release titles,
+    -- this reduces the index size and makes VN search more relevant.
+    -- People looking for editions should be using the release search.
+    UNION ALL SELECT e, 1, regexp_replace(search_norm_term(t), '(?:
+         体験|ダウンロド|初回限定|初回|限定|通常|廉価|豪華|追加|コレクション
+        |パッケージ|ダウンロード|ベスト|復刻|新装|7対応|版|生産|リメイク
+        |first|press|limited|regular|standard|full|remake
+        |pack|package|boxed|download|complete|popular|premium|deluxe|collectors?|collection
+        |lowprice|price|free|best|thebest|cheap|budget|reprint|bundle|set|renewal|extended
+        |special|trial|demo|allages|voiced?|uncensored|web|patch|port|r18|18|earlyaccess
+        |cd|cdr|cdrom|dvdrom|dvd|dvdpg|disk|disc|steam|for
+        |(?:win|windows)(?:7|10|95)?|vista|pc9821|support(?:ed)?
+        |(?:parts?|vol|volumes?|chapters?|v|ver|versions?)(?:[0-9]+)
+        |editions?|version|production|thebest|append|scenario|dlc)+$', '', 'xg')
+      FROM (
+        SELECT title FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = objid
+        UNION ALL
+        SELECT latin FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = objid
+      ) r(t);
+
+  WHEN 'r' THEN RETURN QUERY
+              SELECT e, 3, search_norm_term(title) FROM releases_titles WHERE id = objid
+    UNION ALL SELECT e, 3, search_norm_term(latin) FROM releases_titles WHERE id = objid
+    UNION ALL SELECT e, 1, gtin::text FROM releases WHERE id = objid AND gtin <> 0
+    UNION ALL SELECT e, 1, search_norm_term(catalog) FROM releases WHERE id = objid AND catalog <> '';
+
+  WHEN 'c' THEN RETURN QUERY
+              SELECT e, 3, search_norm_term(name)  FROM chars WHERE id = objid
+    UNION ALL SELECT e, 3, search_norm_term(latin) FROM chars WHERE id = objid
+    UNION ALL SELECT e, 2, search_norm_term(a) FROM chars, regexp_split_to_table(alias, E'\n') a(a) WHERE id = objid;
+
+  WHEN 'p' THEN RETURN QUERY
+              SELECT e, 3, search_norm_term(name)  FROM producers WHERE id = objid
+    UNION ALL SELECT e, 3, search_norm_term(latin) FROM producers WHERE id = objid
+    UNION ALL SELECT e, 2, search_norm_term(a) FROM producers, regexp_split_to_table(alias, E'\n') a(a) WHERE id = objid;
+
+  WHEN 's' THEN RETURN QUERY
+              SELECT aid, 3, search_norm_term(name)  FROM staff_alias WHERE id = objid
+    UNION ALL SELECT aid, 3, search_norm_term(latin) FROM staff_alias WHERE id = objid;
+
+  WHEN 'g' THEN RETURN QUERY
+              SELECT e, 3, search_norm_term(name) FROM tags WHERE id = objid
+    UNION ALL SELECT e, 2, search_norm_term(a)    FROM tags, regexp_split_to_table(alias, E'\n') a(a) WHERE objid = id;
+
+  WHEN 'i' THEN RETURN QUERY
+              SELECT e, 3, search_norm_term(name) FROM traits WHERE id = objid
+    UNION ALL SELECT e, 2, search_norm_term(a)    FROM traits, regexp_split_to_table(alias, E'\n') a(a) WHERE objid = id;
+
+  ELSE RAISE 'unknown objid type';
+  END CASE;
+END;
+$$ LANGUAGE plpgsql;
 
 
-CREATE OR REPLACE FUNCTION search_gen_release(relid vndbid) RETURNS text AS $$
-  SELECT coalesce(string_agg(t, ' '), '') FROM (
-    SELECT t FROM (
-                SELECT search_norm_term(title) FROM releases_titles WHERE id = relid
-      UNION ALL SELECT search_norm_term(latin) FROM releases_titles WHERE id = relid
-    ) x(t) WHERE t IS NOT NULL AND t <> '' GROUP BY t ORDER BY t
-  ) x(t);
+CREATE OR REPLACE FUNCTION update_search(objid vndbid) RETURNS void AS $$
+  WITH uniq(subid, prio, label) AS (
+    SELECT subid, MAX(prio)::smallint, label
+      FROM update_search_terms(objid) x (subid int, prio int, label text)
+     WHERE label IS NOT NULL AND label <> ''
+     GROUP BY subid, label
+  ), n(subid, prio, label) AS (
+     SELECT COALESCE(t.subid, o.subid), t.prio, COALESCE(t.label, o.label)
+       FROM uniq t
+       FULL OUTER JOIN (SELECT subid, label FROM search_cache WHERE id = objid) o ON o.subid IS NOT DISTINCT FROM t.subid AND o.label = t.label
+  ) MERGE INTO search_cache o USING n ON o.id = objid AND (o.subid, o.label) IS NOT DISTINCT FROM (n.subid, n.label)
+      WHEN NOT MATCHED THEN INSERT (id, subid, prio, label) VALUES (objid, subid, n.prio, n.label)
+      WHEN MATCHED AND n.prio IS NULL THEN DELETE
+      WHEN MATCHED AND n.prio <> o.prio THEN UPDATE SET prio = n.prio;
 $$ LANGUAGE SQL;
 
 
@@ -182,7 +224,7 @@ $$ LANGUAGE SQL STABLE;
 -- Same for staff_aliast
 CREATE OR REPLACE FUNCTION staff_aliast(p titleprefs) RETURNS SETOF staff_aliast AS $$
     SELECT s.id, s.gender, s.lang, s.l_anidb, s.l_wikidata, s.l_pixiv, s.locked, s.hidden, s."desc", s.l_wp, s.l_site, s.l_twitter, s.aid AS main
-         , sa.aid, sa.name, sa.latin, sa.c_search
+         , sa.aid, sa.name, sa.latin
          , titleprefs_swap(p, s.lang, sa.name, sa.latin), COALESCE(sa.latin, sa.name)
       FROM staff s
       JOIN staff_alias sa ON sa.id = s.id
@@ -644,13 +686,10 @@ DECLARE
 BEGIN
   SELECT id INTO xoldchid FROM changes WHERE itemid = nitemid AND rev = nrev-1;
 
-  -- Update vn.c_search and tags_vn_*
-  IF vndbid_type(nitemid) = 'v' THEN
-    UPDATE vn SET c_search = search_gen_vn(id) WHERE id = nitemid;
-    PERFORM tag_vn_calc(nitemid); -- actually only necessary when the hidden flag is changed
-  END IF;
+  -- Update search_cache
+  PERFORM update_search(nitemid);
 
-  -- Update vn.c_search when
+  -- Update search_cache for related VNs when
   -- 1. A new release is created
   -- 2. A release has been hidden or unhidden
   -- 3. The releases_titles have changed
@@ -667,13 +706,13 @@ BEGIN
        EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = nchid) OR
        EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = nchid    EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid)
     THEN
-      UPDATE vn SET c_search = search_gen_vn(id) WHERE id IN(SELECT vid FROM releases_vn_hist WHERE chid IN(nchid, xoldchid));
+      PERFORM update_search(vid) FROM releases_vn_hist WHERE chid IN(nchid, xoldchid);
     END IF;
   END IF;
 
-  -- Update releases.c_search
-  IF vndbid_type(nitemid) = 'r' THEN
-    UPDATE releases SET c_search = search_gen_release(id) WHERE id = nitemid;
+  -- Update tags_vn_* when the VN's hidden flag is changed
+  IF vndbid_type(nitemid) = 'v' AND EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = nchid AND c2.id = xoldchid) THEN
+    PERFORM tag_vn_calc(nitemid);
   END IF;
 
   -- Ensure chars.c_lang is updated when the related VN or char has been edited
diff --git a/sql/perms.sql b/sql/perms.sql
index 2f5fc6b5..03799132 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -57,6 +57,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_posts            TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON reviews_votes            TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON rlists                   TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON saved_queries            TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON search_cache             TO vndb_site;
 -- No access to the 'sessions' table, managed by the user_* functions.
 GRANT SELECT                         ON shop_denpa               TO vndb_site;
 GRANT SELECT                         ON shop_dlsite              TO vndb_site;
@@ -160,6 +161,7 @@ GRANT SELECT,         UPDATE         ON reviews                  TO vndb_multi;
 GRANT SELECT                         ON reviews_posts            TO vndb_multi;
 GRANT SELECT                         ON reviews_votes            TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON rlists                   TO vndb_multi;
+GRANT SELECT                         ON search_cache             TO vndb_multi;
 GRANT SELECT (expires, type)         ON sessions                 TO vndb_multi;
 GRANT                         DELETE ON sessions                 TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON shop_denpa               TO vndb_multi;
diff --git a/sql/rebuild-search-cache.sql b/sql/rebuild-search-cache.sql
index e595ced1..06b26e95 100644
--- a/sql/rebuild-search-cache.sql
+++ b/sql/rebuild-search-cache.sql
@@ -1,4 +1,4 @@
--- This is a maintenance script to update all 'c_search' cache columns.
+-- This is a maintenance script to update all rows in search_cache.
 -- It should be run whenever the search normalization functions in func.sql are updated.
 
 -- This script is intentionally slow and performs the updates in smaller
@@ -6,9 +6,6 @@
 -- site to become unresponsive. It also tries to avoid table bloat by only
 -- updating rows that need to be updated.
 
--- I don't like how the c_search generated column expressions are repeated in
--- this script, but it is what it is.
-
 DO $$
 DECLARE
   rows_per_transaction CONSTANT integer := 1000;
@@ -18,9 +15,7 @@ BEGIN
   -- chars
   FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM chars), rows_per_transaction) x(n)
   LOOP
-    UPDATE chars SET name = name
-     WHERE id BETWEEN vndbid('c', i+1) AND vndbid('c', i+rows_per_transaction)
-       AND c_search IS DISTINCT FROM search_gen(ARRAY[name, original]::text[]||string_to_array(alias,E'\n'));
+    PERFORM update_search(vndbid('c', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
     COMMIT;
     PERFORM pg_sleep(sleep_seconds);
   END LOOP;
@@ -28,9 +23,7 @@ BEGIN
   -- producers
   FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM producers), rows_per_transaction) x(n)
   LOOP
-    UPDATE producers SET name = name
-     WHERE id BETWEEN vndbid('p', i+1) AND vndbid('p', i+rows_per_transaction)
-       AND c_search IS DISTINCT FROM search_gen(ARRAY[name, original]::text[]||string_to_array(alias,E'\n'));
+    PERFORM update_search(vndbid('p', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
     COMMIT;
     PERFORM pg_sleep(sleep_seconds);
   END LOOP;
@@ -38,8 +31,7 @@ BEGIN
   -- vn
   FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM vn), rows_per_transaction) x(n)
   LOOP
-    WITH x(n, s) AS (SELECT id, search_gen_vn(id) FROM vn WHERE id BETWEEN vndbid('v', i+1) AND vndbid('v', i+rows_per_transaction))
-    UPDATE vn SET c_search = s FROM x WHERE id = n AND c_search IS DISTINCT FROM s;
+    PERFORM update_search(vndbid('v', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
     COMMIT;
     PERFORM pg_sleep(sleep_seconds);
   END LOOP;
@@ -47,9 +39,7 @@ BEGIN
   -- releases
   FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(vndbid_num(id)) FROM releases), rows_per_transaction) x(n)
   LOOP
-    UPDATE releases SET title = title
-     WHERE id BETWEEN vndbid('r', i+1) AND vndbid('r', i+rows_per_transaction)
-       AND c_search IS DISTINCT FROM search_gen(ARRAY[title, original]);
+    PERFORM update_search(vndbid('r', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
     COMMIT;
     PERFORM pg_sleep(sleep_seconds);
   END LOOP;
@@ -57,14 +47,14 @@ BEGIN
   -- staff_alias
   FOR i IN SELECT n FROM generate_series(0, (SELECT MAX(aid) FROM staff_alias), rows_per_transaction) x(n)
   LOOP
-    UPDATE staff_alias SET name = name
-     WHERE aid BETWEEN i+1 AND i+rows_per_transaction
-       AND c_search IS DISTINCT FROM search_gen(ARRAY[name, original]);
+    PERFORM update_search(vndbid('s', x)) FROM generate_series(i+1, i+rows_per_transaction) x(x);
     COMMIT;
     PERFORM pg_sleep(sleep_seconds);
   END LOOP;
 END$$;
 
 -- These tables are small enough
-UPDATE tags SET name = name WHERE c_search IS DISTINCT FROM search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n'));
-UPDATE traits SET name = name WHERE c_search IS DISTINCT FROM search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n'));
+SELECT count(*) FROM (SELECT update_search(id) FROM tags) x;
+SELECT count(*) FROM (SELECT update_search(id) FROM traits) x;
+
+ANALYZE search_cache;
diff --git a/sql/schema.sql b/sql/schema.sql
index f2ca9998..75108dc0 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -237,7 +237,6 @@ CREATE TABLE chars ( -- dbentry_type=c
   latin        varchar(250), -- [pub]
   alias        varchar(500) NOT NULL DEFAULT '', -- [pub]
   "desc"       text NOT NULL DEFAULT '', -- [pub]
-  c_search     text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name, latin]::text[]||string_to_array(alias,E'\n'))) STORED,
   c_lang       language NOT NULL DEFAULT 'ja'
 );
 
@@ -401,8 +400,7 @@ CREATE TABLE producers ( -- dbentry_type=p
   alias      varchar(500) NOT NULL DEFAULT '', -- [pub]
   website    varchar(1024) NOT NULL DEFAULT '', -- [pub]
   "desc"     text NOT NULL DEFAULT '', -- [pub]
-  l_wp       varchar(150), -- (deprecated)
-  c_search   text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name, latin]::text[]||string_to_array(alias,E'\n'))) STORED
+  l_wp       varchar(150) -- (deprecated)
 );
 
 -- producers_hist
@@ -515,8 +513,7 @@ CREATE TABLE releases ( -- dbentry_type=r
   l_playstation_hk text NOT NULL DEFAULT '', -- [pub]
   l_nintendo    text NOT NULL DEFAULT '', -- [pub]
   l_gyutto     integer[] NOT NULL DEFAULT '{}', -- [pub]
-  l_dmm        text[] NOT NULL DEFAULT '{}', -- [pub]
-  c_search     text
+  l_dmm        text[] NOT NULL DEFAULT '{}' -- [pub]
 );
 
 -- releases_hist
@@ -746,6 +743,22 @@ CREATE TABLE saved_queries (
   PRIMARY KEY(uid, qtype, name)
 );
 
+-- search_cache
+CREATE TABLE search_cache (
+    id    vndbid NOT NULL,
+    subid integer, -- only for staff_alias.id at the moment
+    prio  smallint NOT NULL, -- 1 for indirect titles, 2 for aliases, 3 for main titles
+    label text NOT NULL COLLATE "C"
+) PARTITION BY RANGE(id);
+
+CREATE TABLE search_cache_v PARTITION OF search_cache FOR VALUES FROM ('v1') TO (vndbid_max('v'));
+CREATE TABLE search_cache_r PARTITION OF search_cache FOR VALUES FROM ('r1') TO (vndbid_max('r'));
+CREATE TABLE search_cache_c PARTITION OF search_cache FOR VALUES FROM ('c1') TO (vndbid_max('c'));
+CREATE TABLE search_cache_p PARTITION OF search_cache FOR VALUES FROM ('p1') TO (vndbid_max('p'));
+CREATE TABLE search_cache_s PARTITION OF search_cache FOR VALUES FROM ('s1') TO (vndbid_max('s'));
+CREATE TABLE search_cache_g PARTITION OF search_cache FOR VALUES FROM ('g1') TO (vndbid_max('g'));
+CREATE TABLE search_cache_i PARTITION OF search_cache FOR VALUES FROM ('i1') TO (vndbid_max('i'));
+
 -- sessions
 CREATE TABLE sessions (
   uid      vndbid NOT NULL,
@@ -848,8 +861,7 @@ CREATE TABLE staff_alias (
   id         vndbid NOT NULL, -- [pub]
   aid        SERIAL PRIMARY KEY, -- [pub] Globally unique ID of this alias
   name       varchar(200) NOT NULL DEFAULT '', -- [pub]
-  latin      varchar(200), -- [pub]
-  c_search   text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name, latin])) STORED
+  latin      varchar(200) -- [pub]
 );
 
 -- staff_alias_hist
@@ -880,8 +892,7 @@ CREATE TABLE tags ( -- dbentry_type=g
   applicable   boolean NOT NULL DEFAULT TRUE, -- [pub]
   name         varchar(250) NOT NULL DEFAULT '' UNIQUE, -- [pub]
   alias        varchar(500) NOT NULL DEFAULT '', -- [pub]
-  description  text NOT NULL DEFAULT '', -- [pub]
-  c_search     text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n'))) STORED
+  description  text NOT NULL DEFAULT '' -- [pub]
 );
 
 -- tags_hist
@@ -1025,8 +1036,7 @@ CREATE TABLE traits ( -- dbentry_type=i
   applicable    boolean NOT NULL DEFAULT true, -- [pub]
   name          varchar(250) NOT NULL DEFAULT '', -- [pub]
   alias         varchar(500) NOT NULL DEFAULT '', -- [pub]
-  description   text NOT NULL DEFAULT '', -- [pub]
-  c_search      text NOT NULL GENERATED ALWAYS AS (public.search_gen(ARRAY[name]::text[]||string_to_array(alias,E'\n'))) STORED
+  description   text NOT NULL DEFAULT '' -- [pub]
 );
 
 -- traits_hist
@@ -1261,7 +1271,6 @@ CREATE TABLE vn ( -- dbentry_type=v
   l_encubed     varchar(100) NOT NULL DEFAULT '', -- (deprecated)
   l_renai       varchar(100) NOT NULL DEFAULT '', -- [pub]
   "desc"        text NOT NULL DEFAULT '', -- [pub]
-  c_search      text,
   c_languages   language[] NOT NULL DEFAULT '{}',
   c_platforms   platform[] NOT NULL DEFAULT '{}',
   c_developers  vndbid[] NOT NULL DEFAULT '{}'
@@ -1508,7 +1517,7 @@ CREATE VIEW charst AS
 CREATE VIEW staff_aliast AS
            -- Everything from 'staff', except 'aid' is renamed to 'main'
     SELECT s.id, s.gender, s.lang, s.l_anidb, s.l_wikidata, s.l_pixiv, s.locked, s.hidden, s."desc", s.l_wp, s.l_site, s.l_twitter, s.aid AS main
-         , sa.aid, sa.name, sa.latin, sa.c_search
+         , sa.aid, sa.name, sa.latin
          , ARRAY [ s.lang::text, COALESCE(sa.latin, sa.name)
                  , s.lang::text, sa.name ] AS title
          , COALESCE(sa.latin, sa.name) AS sorttitle
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 6e55fa35..db6f98d1 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -26,6 +26,8 @@ CREATE        INDEX tags_vn_date           ON tags_vn (date);
 CREATE        INDEX tags_vn_direct_vid     ON tags_vn_direct (vid);
 CREATE        INDEX tags_vn_uid            ON tags_vn (uid) WHERE uid IS NOT NULL;
 CREATE        INDEX tags_vn_vid            ON tags_vn (vid);
+CREATE        INDEX search_cache_id        ON search_cache (id);
+CREATE        INDEX search_cache_label     ON search_cache USING GIN (label gin_trgm_ops);
 CREATE        INDEX shop_playasia__gtin    ON shop_playasia (gtin);
 CREATE        INDEX threads_posts_date     ON threads_posts (date);
 CREATE        INDEX threads_posts_ts       ON threads_posts USING gin(bb_tsvector(msg));
diff --git a/sql/util.sql b/sql/util.sql
index e592d329..4a369fea 100644
--- a/sql/util.sql
+++ b/sql/util.sql
@@ -93,20 +93,11 @@ CREATE OR REPLACE FUNCTION search_norm_term(str text) RETURNS text AS $$
 $$ LANGUAGE SQL IMMUTABLE;
 
 
-CREATE OR REPLACE FUNCTION search_gen(terms text[]) RETURNS text AS $$
-  SELECT coalesce(string_agg(t, ' '), '') FROM (
-    SELECT t FROM (
-      SELECT public.search_norm_term(t) FROM unnest(terms) x(t)
-    ) x(t) WHERE t IS NOT NULL AND t <> '' GROUP BY t ORDER BY t
-  ) x(t);
-$$ LANGUAGE SQL IMMUTABLE;
-
-
 -- Split a search query into LIKE patterns.
 -- Supports double quoting for adjacent terms.
 -- e.g. 'SEARCH que.ry "word here"' -> '{%search%,%query%,%wordhere%}'
 --
--- Can be efficiently used as follows: c_search LIKE ALL (search_query('query here'))
+-- Can be efficiently used as follows: label LIKE ALL (search_query('query here'))
 CREATE OR REPLACE FUNCTION search_query(q text) RETURNS text[] AS $$
 DECLARE
   tmp text;
diff --git a/sql/vndbid.sql b/sql/vndbid.sql
index 022aa8a0..385435c6 100644
--- a/sql/vndbid.sql
+++ b/sql/vndbid.sql
@@ -85,3 +85,4 @@ CREATE OPERATOR CLASS vndbid_hash_ops DEFAULT FOR TYPE vndbid USING hash AS
 
 -- Unrelated to the vndbid type, but put here because this file is, ultimately, where all extensions are loaded.
 CREATE EXTENSION unaccent;
+CREATE EXTENSION pg_trgm;
diff --git a/util/updates/2023-03-24-search-cache.sql b/util/updates/2023-03-24-search-cache.sql
new file mode 100644
index 00000000..f72034cf
--- /dev/null
+++ b/util/updates/2023-03-24-search-cache.sql
@@ -0,0 +1,44 @@
+-- Part one, can be done while the site is running old code
+
+CREATE EXTENSION pg_trgm;
+
+CREATE TABLE search_cache (
+    id    vndbid NOT NULL,
+    subid integer, -- only for staff_alias.id at the moment
+    prio  smallint NOT NULL, -- 1 for indirect titles, 2 for aliases, 3 for main titles
+    label text NOT NULL COLLATE "C"
+) PARTITION BY RANGE(id);
+
+CREATE TABLE search_cache_v PARTITION OF search_cache FOR VALUES FROM ('v1') TO (vndbid_max('v'));
+CREATE TABLE search_cache_r PARTITION OF search_cache FOR VALUES FROM ('r1') TO (vndbid_max('r'));
+CREATE TABLE search_cache_c PARTITION OF search_cache FOR VALUES FROM ('c1') TO (vndbid_max('c'));
+CREATE TABLE search_cache_p PARTITION OF search_cache FOR VALUES FROM ('p1') TO (vndbid_max('p'));
+CREATE TABLE search_cache_s PARTITION OF search_cache FOR VALUES FROM ('s1') TO (vndbid_max('s'));
+CREATE TABLE search_cache_g PARTITION OF search_cache FOR VALUES FROM ('g1') TO (vndbid_max('g'));
+CREATE TABLE search_cache_i PARTITION OF search_cache FOR VALUES FROM ('i1') TO (vndbid_max('i'));
+
+CREATE INDEX search_cache_id ON search_cache (id);
+CREATE INDEX search_cache_label ON search_cache USING GIN (label gin_trgm_ops);
+
+\i sql/perms.sql
+\i sql/func.sql
+\i sql/rebuild-search-cache.sql
+
+
+-- Part two, can be done after the site has been reloaded with the new code
+
+ALTER TABLE chars       DROP COLUMN c_search CASCADE;
+ALTER TABLE producers   DROP COLUMN c_search CASCADE;
+ALTER TABLE releases    DROP COLUMN c_search CASCADE;
+ALTER TABLE staff_alias DROP COLUMN c_search CASCADE;
+ALTER TABLE tags        DROP COLUMN c_search CASCADE;
+ALTER TABLE traits      DROP COLUMN c_search CASCADE;
+ALTER TABLE vn          DROP COLUMN c_search CASCADE;
+
+\i sql/schema.sql
+\i sql/func.sql
+\i sql/perms.sql
+
+DROP FUNCTION search_gen_vn(vndbid);
+DROP FUNCTION search_gen_release(vndbid);
+DROP FUNCTION search_gen(text[]);