В нашей таблице есть текст твитов - по ним и будем искать.

Для поиска текстовый документ нужно привести к типу tsvector:

=> SELECT message, to_tsvector(message) FROM tweets WHERE id = 948684689555861504\gx

-[ RECORD 1 ]------------------------------------
message     | Just living in the Database Wow Wow
to_tsvector | 'databas':5 'live':2 'wow':6,7

У слов убраны изменяемые окончания, все приведено к нижнему регистру, стоп-слова выброшены...

Поисковый запрос может содержать слова, соединенные логическими операторами. Он приводится к типу tsquery:

=> SELECT to_tsquery('(postgres | PostgreSQL) & pgconf');

               to_tsquery               
----------------------------------------
 ( 'postgr' | 'postgresql' ) & 'pgconf'
(1 row)

Пример запроса с использованием полнотекстового поиска:

=> SELECT left(message,80) FROM tweets WHERE to_tsvector(message)
@@ to_tsquery('(postgres | PostgreSQL) & pgconf');

                                       left                                       
----------------------------------------------------------------------------------
 @PGConfAPAC @amplifypostgres Not everyone gets a 2nd chance! Grab it! #PostgreSQ
 PGConf EU slides on HyperLogLog, Eclipse, and Distributed Postgres https://t.co/
(2 rows)

Запрос выполняется долго: во-первых, еще нет индексной поддержки, во-вторых, каждый раз происходит приведение к tsvector.

GIN

Создадим индекс GIN. Здесь мы воспользуемся индексом по выражению, чтобы не добавлять еще один столбец в таблицу.

=> CREATE INDEX tweets_message_gin ON tweets
USING gin(to_tsvector('english'::regconfig, message));

=> EXPLAIN(costs off,analyze) SELECT * FROM tweets WHERE to_tsvector('english'::regconfig, message)
@@ to_tsquery('(postgres | PostgreSQL) & pgconf');

                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tweets (actual time=0.143..0.146 rows=2 loops=1)
   Recheck Cond: (to_tsvector('english'::regconfig, message) @@ to_tsquery('(postgres | PostgreSQL) & pgconf'::text))
   Heap Blocks: exact=2
   ->  Bitmap Index Scan on tweets_message_gin (actual time=0.136..0.136 rows=2 loops=1)
         Index Cond: (to_tsvector('english'::regconfig, message) @@ to_tsquery('(postgres | PostgreSQL) & pgconf'::text))
 Planning time: 3.413 ms
 Execution time: 0.714 ms
(7 rows)

Интересен случай, когда поисковый запрос состоит из частых и редких лексем. Обычно выгодней найти документы по редким лексемам и затем перепроверить их по tsvector, чем перебирать длинный список документов, содержащих частую лексему.

Например, "databas" - частая лексема, а "zigzag" - редкая:

=> SELECT word, ndoc 
FROM ts_stat('SELECT to_tsvector(message) FROM tweets')
WHERE word IN ('databas','zigzag');

  word   |  ndoc  
---------+--------
 zigzag  |      2
 databas | 371068
(2 rows)

=> SELECT count(*) FROM tweets WHERE to_tsvector('english'::regconfig, message)
@@ to_tsquery('database');

=> SELECT count(*) FROM tweets WHERE to_tsvector('english'::regconfig, message)
@@ to_tsquery('zigzag');

=> SELECT count(*) FROM tweets WHERE to_tsvector('english'::regconfig, message)
@@ to_tsquery('database & zigzag');

Откуда планировщик знает о частоте лексем? Из статистики (в данном случае - из статистики индекса по выражению):

=> SELECT u.elem, f.freq
 FROM pg_stats p,
      unnest(p.most_common_elems::text::text[]) WITH ORDINALITY u(elem),
      unnest(p.most_common_elem_freqs::text::text[]) WITH ORDINALITY f(freq)
WHERE p.tablename = 'tweets_message_gin'
  AND f.ordinality = u.ordinality
ORDER BY f.freq DESC LIMIT 10;

  elem   |   freq    
---------+-----------
 databas | 0.639167
 rt      | 0.580367
 t.co    | 0.517833
 help    | 0.0530333
 one     | 0.0525667
 data    | 0.0517
 find    | 0.0511667
 new     | 0.0501667
 look    | 0.05
 use     | 0.0499667
(10 rows)

GiST

=> CREATE INDEX tweets_message_gist ON tweets
USING gist(to_tsvector('english'::regconfig, message));

=> EXPLAIN(costs off,analyze) SELECT * FROM tweets WHERE to_tsvector('english'::regconfig, message)
@@ to_tsquery('(postgres | PostgreSQL) & pgconf');

                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Index Scan using tweets_message_gist on tweets (actual time=17.130..35.297 rows=2 loops=1)
   Index Cond: (to_tsvector('english'::regconfig, message) @@ to_tsquery('(postgres | PostgreSQL) & pgconf'::text))
 Planning time: 1.369 ms
 Execution time: 35.325 ms
(4 rows)

С GiST-индексом запрос выполняется существенно дольше, чем с GIN.

При этом эффективность не зависит от частоты отдельных лексем.

=> EXPLAIN(costs off,analyze) SELECT count(*) FROM tweets
WHERE to_tsvector('english'::regconfig, message) @@ to_tsquery('database & zigzag');

                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate (actual time=14.715..14.715 rows=1 loops=1)
   ->  Index Scan using tweets_message_gist on tweets (actual time=3.912..14.703 rows=2 loops=1)
         Index Cond: (to_tsvector('english'::regconfig, message) @@ to_tsquery('database & zigzag'::text))
 Planning time: 0.194 ms
 Execution time: 14.750 ms
(5 rows)

=> SELECT pg_size_pretty(pg_total_relation_size('tweets_message_gin')) AS gin,
pg_size_pretty(pg_total_relation_size('tweets_message_gist')) AS gist;

  gin  | gist  
-------+-------
 59 MB | 52 MB
(1 row)

Размер в нашем случае получился примерно одинаковый.

Полнотекстовый поиск

GIN

GiST