FTS is slow when searched word appears frequently in rows I have a message table with a content column indexed for full text search:

create index message_fts on message using GIN(to_tsvector('english', content));


The problem I have and that I'd like to understand is that searches involving frequent words are very slow.

Here's a normal fast enough query on a rare "word":

miaou=&gt; explain analyze select count(*) from message where (to_tsvector('english', content) @@ to_tsquery('fajre'));
                               QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=16808.35..16808.36 rows=1 width=8) (actual time=6.565..6.565 rows=1 loops=1)
   -&gt;  Gather  (cost=16808.13..16808.34 rows=2 width=8) (actual time=1.608..6.556 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         -&gt;  Partial Aggregate  (cost=15808.13..15808.14 rows=1 width=8) (actual time=0.456..0.457 rows=1 loops=3)
               -&gt;  Parallel Bitmap Heap Scan on message  (cost=293.28..15803.69 rows=1776 width=0) (actual time=0.453..0.453 rows=0 loops=3)
                     Recheck Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('fajre'::text))
                     -&gt;  Bitmap Index Scan on message_fts  (cost=0.00..292.22 rows=4262 width=0) (actual time=0.954..0.954 rows=0 loops=1)
                           Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('fajre'::text))
 Planning time: 0.249 ms
 Execution time: 8.400 ms
(11 rows)


And here's a very slow query on a frequent term:

miaou=&gt; explain analyze select count(*) from message where (to_tsvector('english', content) @@ to_tsquery('faire'));
NOTICE:  word is too long to be indexed
DETAIL:  Words longer than 2047 characters are ignored.
&lt;other identical notices removed for clarity&gt;                                            
                               QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=189901.34..189901.35 rows=1 width=8) (actual time=47192.053..47192.053 rows=1 loops=1)
   -&gt;  Gather  (cost=189901.12..189901.33 rows=2 width=8) (actual time=47188.506..47192.042 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         -&gt;  Partial Aggregate  (cost=188901.12..188901.13 rows=1 width=8) (actual time=47183.158..47183.158 rows=1 loops=3)
               -&gt;  Parallel Bitmap Heap Scan on message  (cost=2012.04..188702.23 rows=79558 width=0) (actual time=72.503..47152.312 rows=66781 loops=3)
                     Recheck Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('faire'::text))
                     Rows Removed by Index Recheck: 1329552
                     Heap Blocks: exact=12673 lossy=22516
                     -&gt;  Bitmap Index Scan on message_fts  (cost=0.00..1964.30 rows=190940 width=0) (actual time=64.740..64.740 rows=200347 loops=1)
                           Index Cond: (to_tsvector('english'::regconfig, content) @@ to_tsquery('faire'::text))
 Planning time: 0.268 ms
 Execution time: 47199.290 ms
(13 rows)


It can be noticed that the index correctly finds the relevant records (200347 of them).

What should I fix or look at ? I'm especially surprised by the many notices I get when just querying, given that the matching information is already given by the index.
Your bitmap has grown too large to fit in work_mem, and so it has "gone lossy" where it records just the blocks that contains matches (for some blocks), and then needs to recheck each row in those blocks.

                 Rows Removed by Index Recheck: 1329552
                 Heap Blocks: exact=12673 lossy=22516


Doing this is slow.  Especially so because you are using a functional index, and so it has to re-parse content for each of those rows as the parsed form is not stored in the table.  (That re-parsing is also why you get the NOTICEs).

Increase work_mem so that it can hold the entire bitmap.