17 Занятие 17: Расширяемость / Обзор полнотекстового поиска

student$ zcat ~/mail_messages.sql.gz | psql -d ext_fts_overview

=> \c ext_fts_overview

You are now connected to database "ext_fts_overview" as user "student".

=> SELECT id, parent_id, sent, subject, author, left(body_plain,400) body
FROM mail_messages LIMIT 1 \gx

-[ RECORD 1 ]-------------------------------------------------------------------------
id        | 1842152
parent_id | 1842072
sent      | 2014-04-05 07:39:03
subject   | Re: [PATCH] Negative Transition Aggregate Functions (WIP)
author    | Dean Rasheed <dean.a.rasheed@gmail.com>
body      | On 4 April 2014 11:56, Florian Pflug <fgp@phlo.org> wrote:                +
          | >                                                                         +
          | >> On 04.04.2014, at 09:40, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:+
          | >>                                                                        +
          | >> I'm not sure how much additional work is required to sort this out,    +
          | >> but to me it looks more realistic to target 9.5 than 9.4, so at this   +
          | >> point I tend to think that the patch ought to be marked as returned    +
          | >> with feedback.                                                         +
          | >                                                                         +
          |                                                                           +
          | Just doing the first optim

=> SELECT count(*) FROM mail_messages;

 count
--------
 356125
(1 row)

=> SELECT id, subject FROM mail_messages
WHERE to_tsvector(subject) @@ to_tsquery('magical')
ORDER BY id LIMIT 5;

   id    |                                    subject
---------+-------------------------------------------------------------------------------
 1589495 | Pid file magically disappears
 1609344 | about the magic(?) of palloc() and variable-length user-defined data type
 1609348 | Re: about the magic(?) of palloc() and variable-length user-defined data type
 1609350 | Re: about the magic(?) of palloc() and variable-length user-defined data type
 1678485 | Re: [PATCHES] Magic block for modules
(5 rows)

=> SELECT id, subject FROM mail_messages
WHERE to_tsvector(subject) @@ to_tsquery('magic & value')
ORDER BY id LIMIT 5;

   id    |             subject
---------+----------------------------------
 1778396 | Macros for time magic values
 1778397 | Re: Macros for time magic values
 1778398 | Re: Macros for time magic values
 1778400 | Re: Macros for time magic values
 1778439 | Re: Macros for time magic values
(5 rows)

=> SELECT id, subject FROM mail_messages
WHERE to_tsvector(subject) @@ to_tsquery('magic & value & !time')
ORDER BY id LIMIT 5;

   id    |                            subject
---------+----------------------------------------------------------------
 1868223 | patch: change magic constants to DEFINE value for readability.
(1 row)

=> SELECT id, subject FROM mail_messages
WHERE to_tsvector(subject) @@ to_tsquery('magic & (value | constant)')
ORDER BY id LIMIT 5;

   id    |             subject
---------+----------------------------------
 1743238 | magic constant -1
 1743241 | Re: magic constant -1
 1743248 | Re: magic constant -1
 1778396 | Macros for time magic values
 1778397 | Re: Macros for time magic values
(5 rows)

=> SELECT id, subject FROM mail_messages
WHERE to_tsvector(subject) @@ to_tsquery('time <-> value')
ORDER BY id LIMIT 5;

   id    |                         subject
---------+----------------------------------------------------------
 1847875 | proposal: rounding up time value less than its unit.
 1847989 | Re: proposal: rounding up time value less than its unit.
 1847996 | Re: proposal: rounding up time value less than its unit.
 1849919 | Re: proposal: rounding up time value less than its unit.
 1849928 | Re: proposal: rounding up time value less than its unit.
(5 rows)

=> SELECT id, subject FROM mail_messages
WHERE to_tsvector(subject) @@ to_tsquery('time <2> value')
ORDER BY id LIMIT 5;

   id    |             subject
---------+----------------------------------
 1778396 | Macros for time magic values
 1778397 | Re: Macros for time magic values
 1778398 | Re: Macros for time magic values
 1778400 | Re: Macros for time magic values
 1778439 | Re: Macros for time magic values
(5 rows)

to_tsquery('(time <-> value) & !magic')

=> SELECT id, subject FROM mail_messages
WHERE to_tsvector(subject) @@ websearch_to_tsquery('"time value" -magic')
ORDER BY id LIMIT 5;

   id    |                         subject
---------+----------------------------------------------------------
 1847875 | proposal: rounding up time value less than its unit.
 1847989 | Re: proposal: rounding up time value less than its unit.
 1847996 | Re: proposal: rounding up time value less than its unit.
 1849919 | Re: proposal: rounding up time value less than its unit.
 1849928 | Re: proposal: rounding up time value less than its unit.
(5 rows)

=> SELECT body_plain FROM mail_messages
WHERE to_tsvector(body_plain) @@ to_tsquery('magic')
ORDER BY id LIMIT 1;

                                body_plain
--------------------------------------------------------------------------
 I know that there has been some discussion on this, but thought a       +
 more-or-less explicit roadmap to v7.0 might be helpful. Here are some   +
 thoughts:                                                               +
                                                                         +
 1) dump/reload is allowed or required on every formal release (e.g. for +
 v6.1->v6.2). This will allow incremental improvements to the backend    +
 code without having to put in everything at once.                       +
                                                                         +
 2) time travel code can be removed during the v6.x releases. It has been+
 unsupported and de-emphasized (not the word I'm thinking of, but can't  +
 recall what de-x word I want) since v6.0.                               +
                                                                         +
 3) the most visible improvements and changes from a user's point of view+
 are in capabilities and user interfaces.                                +
                                                                         +
 How about targeting an accumulation of capabilities for a v7.0 release? +
 The accumulated changes might consist of Purifying the code, Vadim's    +
 backend optimizations (fsync improvements, index improvements and       +
 time-travel-removal?), SQL enhancements including subselects, unions,   +
 and rules, and ?? Assuming that we will continue to get incremental     +
 improvements in data types, parsing, etc, I think that subselects must  +
 be high on the list of desirable features which may not magically appear+
 without a concerted effort.                                             +
                                                                         +
 I would guess that the SQL enhancements will take the most time and     +
 would show up later in the v6.x releases than some of the other items...+
                                                                         +
 Some statement like this on the Web page or attached to the ToDo list   +
 might be helpful.                                                       +
                                                                         +
 Comments?                                                               +
                                                                         +
                         - Tom                                           +
                                                                         +
 ------------------------------
(1 row)

=> SELECT id, translate(
  ts_headline(body_plain,
              to_tsquery('magic'),
              'StartSel=*,StopSel=*,MinWords=8,MaxWords=10'),
  E'\n',
  ' '
) FROM mail_messages
WHERE to_tsvector(body_plain) @@ to_tsquery('magic')
ORDER BY id LIMIT 5;

   id    |                           translate
---------+---------------------------------------------------------------
 1562872 | *magically* appear without a concerted effort.  I would
 1562878 | *magically* appear > without a concerted effort. >  > I would
 1563678 | *magic* which I should not worry about? There
 1563681 | *magic* which I should not worry about? There
 1563878 | *magic* which I should not worry about? There
(5 rows)

=> \dFp

        List of text search parsers
   Schema   |  Name   |     Description
------------+---------+---------------------
 pg_catalog | default | default word parser
(1 row)

=> SELECT *
FROM ts_parse('default', 'The bells from the chapel went jingle-jangle');

 tokid |     token
-------+---------------
     1 | The
    12 |
     1 | bells
    12 |
     1 | from
    12 |
     1 | the
    12 |
     1 | chapel
    12 |
     1 | went
    12 |
    16 | jingle-jangle
    11 | jingle
    12 | -
    11 | jangle
(16 rows)

=> \dFp+

    Text search parser "pg_catalog.default"
     Method      |    Function    | Description
-----------------+----------------+-------------
 Start parse     | prsd_start     | (internal)
 Get next token  | prsd_nexttoken | (internal)
 End parse       | prsd_end       | (internal)
 Get headline    | prsd_headline  | (internal)
 Get token types | prsd_lextype   | (internal)

        Token types for parser "pg_catalog.default"
   Token name    |               Description
-----------------+------------------------------------------
 asciihword      | Hyphenated word, all ASCII
 asciiword       | Word, all ASCII
 blank           | Space symbols
 email           | Email address
 entity          | XML entity
 file            | File or path name
 float           | Decimal notation
 host            | Host
 hword           | Hyphenated word, all letters
 hword_asciipart | Hyphenated word part, all ASCII
 hword_numpart   | Hyphenated word part, letters and digits
 hword_part      | Hyphenated word part, all letters
 int             | Signed integer
 numhword        | Hyphenated word, letters and digits
 numword         | Word, letters and digits
 protocol        | Protocol head
 sfloat          | Scientific notation
 tag             | XML tag
 uint            | Unsigned integer
 url             | URL
 url_path        | URL path
 version         | Version number
 word            | Word, all letters
(23 rows)

=> SELECT ts_lexize('english_stem','The');

 ts_lexize
-----------
 {}
(1 row)

=> SELECT ts_lexize('english_stem','Bells');

 ts_lexize
-----------
 {bell}
(1 row)

=> SELECT ts_lexize('english_stem','went');

 ts_lexize
-----------
 {went}
(1 row)

=> SELECT ts_lexize('english_stem','Röntgen');

 ts_lexize
-----------
 {röntgen}
(1 row)

=> CREATE EXTENSION unaccent;

CREATE EXTENSION

=> SELECT ts_lexize('unaccent','Röntgen');

 ts_lexize
-----------
 {Rontgen}
(1 row)

=> \dF

               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | arabic     | configuration for arabic language
 pg_catalog | armenian   | configuration for armenian language
 pg_catalog | basque     | configuration for basque language
 pg_catalog | catalan    | configuration for catalan language
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | greek      | configuration for greek language
 pg_catalog | hindi      | configuration for hindi language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | indonesian | configuration for indonesian language
 pg_catalog | irish      | configuration for irish language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | lithuanian | configuration for lithuanian language
 pg_catalog | nepali     | configuration for nepali language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | serbian    | configuration for serbian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | tamil      | configuration for tamil language
 pg_catalog | turkish    | configuration for turkish language
 pg_catalog | yiddish    | configuration for yiddish language
(29 rows)

=> SHOW default_text_search_config;

 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

=> \dF+ english

Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
      Token      | Dictionaries
-----------------+--------------
 asciihword      | english_stem
 asciiword       | english_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | english_stem
 hword_asciipart | english_stem
 hword_numpart   | simple
 hword_part      | english_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | english_stem

=> ALTER TEXT SEARCH CONFIGURATION english
ALTER MAPPING FOR word WITH unaccent, english_stem;

ALTER TEXT SEARCH CONFIGURATION

=> \dF+ english

Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
      Token      |     Dictionaries
-----------------+-----------------------
 asciihword      | english_stem
 asciiword       | english_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | english_stem
 hword_asciipart | english_stem
 hword_numpart   | simple
 hword_part      | english_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | unaccent,english_stem

=> SELECT to_tsvector('Wilhelm Röntgen');

       to_tsvector
-------------------------
 'rontgen':2 'wilhelm':1
(1 row)

=> SELECT to_tsvector('The bells from the chapel went jingle-jangle');

                            to_tsvector
-------------------------------------------------------------------
 'bell':2 'chapel':5 'jangl':9 'jingl':8 'jingle-jangl':7 'went':6
(1 row)

=> SELECT to_tsquery('Jingle & bells');

    to_tsquery
------------------
 'jingl' & 'bell'
(1 row)

=> \timing on

Timing is on.

=> SELECT count(*) FROM mail_messages
WHERE to_tsvector(subject) @@ to_tsquery('magic');

 count
-------
   103
(1 row)

Time: 5943,915 ms (00:05,944)

=> \timing off

Timing is off.

=> ALTER TABLE mail_messages
ADD search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english',subject) || to_tsvector('english',body_plain)
) STORED;

NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
ALTER TABLE

=> EXPLAIN (costs off)
SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('magic');

                                QUERY PLAN
--------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on mail_messages
                     Filter: (search_vector @@ to_tsquery('magic'::text))
(6 rows)

=> CREATE INDEX ON mail_messages USING gin(search_vector);

CREATE INDEX

=> EXPLAIN (costs off)
SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('magic');

                               QUERY PLAN
------------------------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on mail_messages
         Recheck Cond: (search_vector @@ to_tsquery('magic'::text))
         ->  Bitmap Index Scan on mail_messages_search_vector_idx
               Index Cond: (search_vector @@ to_tsquery('magic'::text))
(5 rows)

=> \timing on

Timing is on.

=> SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('magic');

 count
-------
  3148
(1 row)

Time: 849,410 ms

=> \timing off

Timing is off.

=> EXPLAIN (costs off)
SELECT id, subject, ts_rank(search_vector, to_tsquery('magic')) rank
FROM mail_messages
WHERE search_vector @@ to_tsquery('magic')
ORDER BY rank DESC LIMIT 10;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: (ts_rank(search_vector, to_tsquery('magic'::text))) DESC
         ->  Bitmap Heap Scan on mail_messages
               Recheck Cond: (search_vector @@ to_tsquery('magic'::text))
               ->  Bitmap Index Scan on mail_messages_search_vector_idx
                     Index Cond: (search_vector @@ to_tsquery('magic'::text))
(7 rows)

=> CREATE EXTENSION rum;

CREATE EXTENSION

=> CREATE INDEX ON mail_messages USING rum(search_vector);

CREATE INDEX

=> EXPLAIN (costs off)
SELECT id, subject, search_vector <=> to_tsquery('magic') distance
FROM mail_messages
WHERE search_vector @@ to_tsquery('magic')
ORDER BY search_vector <=> to_tsquery('magic')
LIMIT 10;

                                QUERY PLAN
--------------------------------------------------------------------------
 Limit
   ->  Index Scan using mail_messages_search_vector_idx1 on mail_messages
         Index Cond: (search_vector @@ to_tsquery('magic'::text))
         Order By: (search_vector <=> to_tsquery('magic'::text))
(4 rows)

=> \timing on

Timing is on.

=> SELECT id, subject, search_vector <=> to_tsquery('magic') distance
FROM mail_messages
WHERE search_vector @@ to_tsquery('magic')
ORDER BY search_vector <=> to_tsquery('magic')
LIMIT 10;

   id    |                                 subject                                  |
distance
---------+--------------------------------------------------------------------------+-----
-------
 2304690 | Re: [HACKERS] Radix tree for character conversion                        |
10.471326
 2303834 | Re: [HACKERS] Radix tree for character conversion                        |
10.471326
 1847532 | Re: WAL replay bugs                                                      |
10.614068
 1847429 | Re: WAL replay bugs                                                      |
10.6830015
 1847613 | Re: WAL replay bugs                                                      |
10.6830015
 1813935 | [PATCH 3/5] Split out xlog reading into its own module called xlogreader |
10.769349
 1767721 | Protecting against unexpected zero-pages: proposal                       |
10.769349
 1834012 | Re: invalid magic number in log segment                                  |
10.769349
 1754494 | Problem of Magic Block in Postgres 8.2                                   |
10.880653
 1595154 | Proposal for fixing IPC key assignment                                   |
10.880653
(10 rows)

Time: 15,683 ms

=> \timing off

Timing is off.

=> ALTER TABLE books ADD tsv tsvector;

ALTER TABLE

=> CREATE FUNCTION build_tsv(
    book_id bigint,
    title text,
    abstract text
)
RETURNS tsvector
LANGUAGE sql STABLE
BEGIN ATOMIC
    WITH a(names) AS (
        SELECT string_agg(
            a.last_name || ' ' ||
            a.first_name || ' ' ||
            coalesce(a.middle_name,''),
            ' '
        )
        FROM authorships ash
            JOIN authors a ON a.author_id = ash.author_id
        WHERE ash.book_id = build_tsv.book_id
    )
    SELECT to_tsvector(
        'russian',
        build_tsv.title || ' ' ||
        a.names || ' ' ||
        coalesce(build_tsv.abstract,'')
    )
    FROM a;
END;

CREATE FUNCTION

=> CREATE FUNCTION fill_tsv() RETURNS trigger
AS $$
BEGIN
    NEW.tsv := build_tsv(
        NEW.book_id, NEW.title, NEW.additional->>'Аннотация'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE FUNCTION

=> CREATE TRIGGER books_tsv
AFTER INSERT OR UPDATE OF title, additional
ON books
FOR EACH ROW
EXECUTE FUNCTION fill_tsv();

CREATE TRIGGER

=> CREATE FUNCTION update_authors_tsv() RETURNS trigger
AS $$
BEGIN
    UPDATE books b
    SET tsv = build_tsv(b.book_id, b.title, b.additional->>'Аннотация')

    WHERE b.book_id IN (
        SELECT ash.book_id
        FROM authorships ash
            JOIN books b ON b.book_id = ash.book_id
        WHERE ash.author_id = NEW.author_id
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE FUNCTION

=> CREATE TRIGGER authors_tsv
AFTER UPDATE OF first_name, last_name, middle_name
ON authors
FOR EACH ROW
EXECUTE FUNCTION update_authors_tsv();

CREATE TRIGGER

=> CREATE FUNCTION update_authorships_tsv() RETURNS trigger
AS $$
BEGIN
    UPDATE books b
    SET tsv = build_tsv(b.book_id, b.title, b.additional->>'Аннотация')
    WHERE b.book_id IN (OLD.book_id, NEW.book_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE FUNCTION

=> CREATE TRIGGER authorships_tsv
AFTER INSERT OR UPDATE OF book_id, author_id OR DELETE
ON authorships
FOR EACH ROW
EXECUTE FUNCTION update_authorships_tsv();

CREATE TRIGGER

=> UPDATE books
SET tsv = build_tsv(book_id, title, additional->>'Аннотация');

UPDATE 100

=> CREATE OR REPLACE FUNCTION public.search_cond(search text) RETURNS text
LANGUAGE sql IMMUTABLE
RETURN CASE
    WHEN coalesce(search,'') = '' THEN
        'true'
    ELSE
        format('b.tsv @@ websearch_to_tsquery(''russian'',%L)', search)
    END;

CREATE FUNCTION

=> CREATE DATABASE ext_fts_overview;

CREATE DATABASE

student$ zcat ~/mail_messages.sql.gz | psql -d ext_fts_overview

SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
ALTER TABLE
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
SET
SET
CREATE TABLE
COPY 356125
 setval
---------
 2317563
(1 row)

ALTER TABLE

=> \c ext_fts_overview

You are now connected to database "ext_fts_overview" as user "student".

=> ALTER TABLE mail_messages
ADD search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english',subject) || to_tsvector('english',body_plain)
) STORED;

NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
ALTER TABLE

=> ANALYZE mail_messages;

ANALYZE

=> \timing on

Timing is on.

=> SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('(vacuum <-> full) & (index <-> page)');

 count
-------
    45
(1 row)

Time: 14513,190 ms (00:14,513)

=> \timing off

Timing is off.

=> CREATE INDEX mm_gist ON mail_messages USING gist(search_vector);

CREATE INDEX

=> SELECT pg_size_pretty(pg_indexes_size('mail_messages'));

 pg_size_pretty
----------------
 134 MB
(1 row)

=> \timing on

Timing is on.

=> SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('(vacuum <-> full) & (index <-> page)');

 count
-------
    45
(1 row)

Time: 2435,401 ms (00:02,435)

=> \timing off

Timing is off.

=> DROP INDEX mm_gist;

DROP INDEX

=> CREATE INDEX mm_gin ON mail_messages USING gin(search_vector);

CREATE INDEX

=> SELECT pg_size_pretty(pg_indexes_size('mail_messages'));

 pg_size_pretty
----------------
 188 MB
(1 row)

=> \timing on

Timing is on.

=> SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('(vacuum <-> full) & (index <-> page)');

 count
-------
    45
(1 row)

Time: 487,821 ms

=> \timing off

Timing is off.

=> DROP INDEX mm_gin;

DROP INDEX

=> CREATE EXTENSION rum;

CREATE EXTENSION

=> CREATE INDEX mm_rum ON mail_messages USING rum(search_vector);

CREATE INDEX

=> SELECT pg_size_pretty(pg_indexes_size('mail_messages'));

 pg_size_pretty
----------------
 383 MB
(1 row)

=> \timing on

Timing is on.

=> SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('(vacuum <-> full) & (index <-> page)');

 count
-------
    45
(1 row)

Time: 106,608 ms

=> \timing off

Timing is off.

=> DROP INDEX mm_rum;

DROP INDEX

=> ALTER TEXT SEARCH CONFIGURATION english
ALTER MAPPING FOR asciiword WITH simple;

ALTER TEXT SEARCH CONFIGURATION

=> SELECT to_tsquery('vacuuming');

 to_tsquery
-------------
 'vacuuming'
(1 row)

=> ALTER TABLE mail_messages
DROP search_vector;

ALTER TABLE

=> ALTER TABLE mail_messages
ADD search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('english',subject) || to_tsvector('english',body_plain)
) STORED;

NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
ALTER TABLE

=> SELECT count(*) FROM mail_messages
WHERE search_vector @@ to_tsquery('vacuuming');

 count
-------
  2245
(1 row)