Improving FTS phrase search on postgres for common terms

Question

I have a table defined like

CREATE TABLE details_search (
    id int4 NOT NULL PRIMARY KEY,
    "search" tsvector NULL
);
CREATE INDEX details_search_idx ON details_search USING gin (search);

I ran this to get an idea of its size:

SELECT pg_size_pretty(pg_relation_size('details_search')) relation_size,
       pg_size_pretty(pg_total_relation_size('details_search')) total_relation_size,
       pg_size_pretty(pg_table_size('details_search')) table_size,
       pg_size_pretty(pg_indexes_size('details_search')) indexes_size;

and these are the results

relation_size|total_relation_size|table_size|indexes_size|
-------------+-------------------+----------+------------+
800 MB       |64 GB              |57 GB     |6830 MB     |

I'm interested in only performing phrase searches, and these are used aggregated. When I perform phrase searches with uncommon terms, things work fine. Now when I use a phrase that has a common term, performance suffers a lot.

This query took 192s:

SELECT COUNT(id)
FROM   details_search
WHERE  search @@ phraseto_tsquery('simple', 'data management') 

Here's the query plan (and here the query plan in a nice interface):

  Output: count(id)
  Buffers: shared hit=25942383 read=6354221 written=4588
  I/O Timings: shared/local read=512605.708 write=122.864
  ->  Gather  (cost=178176.43..178176.64 rows=2 width=8) (actual time=192857.512..192861.652 rows=3 loops=1)
        Output: (PARTIAL count(id))
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=25942383 read=6354221 written=4588
        I/O Timings: shared/local read=512605.708 write=122.864
        ->  Partial Aggregate  (cost=177176.43..177176.44 rows=1 width=8) (actual time=192852.434..192852.435 rows=1 loops=3)
              Output: PARTIAL count(id)
              Buffers: shared hit=25942383 read=6354221 written=4588
              I/O Timings: shared/local read=512605.708 write=122.864
              Worker 0:  actual time=192851.530..192851.531 rows=1 loops=1
                Buffers: shared hit=8650807 read=2115877 written=1469
                I/O Timings: shared/local read=170775.853 write=38.985
              Worker 1:  actual time=192848.579..192848.581 rows=1 loops=1
                Buffers: shared hit=8623424 read=2115864 written=1551
                I/O Timings: shared/local read=170720.335 write=41.527
              ->  Parallel Bitmap Heap Scan on details_search  (cost=33664.19..173376.94 rows=1519795 width=4) (actual time=1231.216..192758.374 rows=121050 loops=3)
                    Output: id, search
                    Recheck Cond: (search @@ '''data'' <-> ''management'''::tsquery)
                    Rows Removed by Index Recheck: 2268868
                    Heap Blocks: exact=12114 lossy=22061
                    Buffers: shared hit=25942383 read=6354221 written=4588
                    I/O Timings: shared/local read=512605.708 write=122.864
                    Worker 0:  actual time=1230.572..192759.521 rows=121482 loops=1
                      Buffers: shared hit=8650807 read=2115877 written=1469
                      I/O Timings: shared/local read=170775.853 write=38.985
                    Worker 1:  actual time=1227.317..192754.854 rows=120483 loops=1
                      Buffers: shared hit=8623424 read=2115864 written=1551
                      I/O Timings: shared/local read=170720.335 write=41.527
                    ->  Bitmap Index Scan on job_posts_details_search_idx  (cost=0.00..32752.32 rows=3647509 width=0) (actual time=1226.674..1226.675 rows=3956386 loops=1)
                          Index Cond: (search @@ '''data'' <-> ''management'''::tsquery)
                          Buffers: shared hit=832 read=2242
                          I/O Timings: shared/local read=424.365
Settings: effective_cache_size = '13153520kB', search_path = 'public, public, "$user"'
Query Identifier: 1461135140272243366
Planning:
  Buffers: shared hit=194
Planning Time: 7.346 ms
Execution Time: 192861.763 ms

Most of the time went to reading on the parallel bitmap heap scan. It was also a fairly slow read, at 97 MB/s considering it has SSDs (and have an SSD exclusively for data caching). This is not improved if I use pg_prewarm to load the table before the query.

I see it has Recheck Cond: (search @@ '''data'' <-> ''management'''::tsquery), so I guess it's pulling all the job data from disk to check the condition on the actual search column, as if just checking the index wasn't enough to validate if there's a phrase match. That would explain why this issue only occurs on common terms.

What could I do to optimize for these phrase searches? I'd be happy to consider limitations of possible things to search for (like "only do queries up to 3 words") or server setting changes (to speed up those pesky reads) if that can bring consistency to the query time.

Answer 1

There is nothing much you can do about that. A GIN index indexes the individual constituents, not the phrase. So the "bitmap index scan" will give you all rows that contain "data" and "management", and the recheck in the "bitmap heap scan" weeds out the 95% false positives.

The mere 170 seconds it took to read the over 2 million 8kB blocks indicate that most of your data were cached in the kernel page cache anyway. You can boost the performance slightly by increasing work_mem so that you get no more "lossy" blocks.

If the problem were to boost the search for certain specific phrases, you could use a thesaurus dictionary to replace them with single words like "datamanagement", which would make the index scan more effective. But I guess you want to speed up the search for arbitrary phrases.