How to index a Postgres Full-Text Search across relations

Question

I'm in the process of adding indices to support full-text searching in our PostgreSQL (12) database. I noticed that everything works well if all searched columns are in the same table, but that is not my use case unfortunately. Instead, the query no longer uses the index as soon as I search a column from an associated table.

Consider this minimal example:

CREATE TABLE categories (id BIGINT, name TEXT, PRIMARY KEY(id));

CREATE TABLE items (
  id BIGINT, title TEXT, body TEXT,
  category_id BIGINT,
  PRIMARY KEY(id),
  CONSTRAINT fk_item_categories FOREIGN KEY (category_id) REFERENCES categories(id)
);

Now I want to search for Items, but not just by their own properties but also by their category name, so I add these indices:

CREATE INDEX idx_item_category_id ON items (id); -- index on foreign key
CREATE INDEX fts_category_name ON categories USING GIN (to_tsvector('simple', name));
CREATE INDEX fts_advice_title ON items USING GIN (to_tsvector('simple', title));
CREATE INDEX fts_advice_body ON items USING GIN (to_tsvector('simple', body));

First I query just by the item title and body:

EXPLAIN ANALYZE
  SELECT *
  FROM items i
    INNER JOIN categories c ON i.category_id = c.id
  WHERE to_tsvector('simple', i.title) @@ to_tsquery('simple', 'test')
     OR to_tsvector('simple', i.body) @@ to_tsquery('simple', 'test');

Result:

Hash Join  (cost=25.04..78.71 rows=82 width=79) (actual time=0.088..0.289 rows=1164 loops=1)
  Hash Cond: (i.category_id = c.id)
  ->  Bitmap Heap Scan on items i  (cost=16.33..68.98 rows=41 width=58) (actual time=0.043..0.097 rows=582 loops=1)
        Recheck Cond: ((to_tsvector('simple'::regconfig, title) @@ '''test'''::tsquery) OR (to_tsvector('simple'::regconfig, body) @@ '''test'''::tsquery))
        Heap Blocks: exact=18
        ->  BitmapOr  (cost=16.33..16.33 rows=41 width=0) (actual time=0.037..0.037 rows=0 loops=1)
              ->  Bitmap Index Scan on fts_advice_title  (cost=0.00..8.15 rows=21 width=0) (actual time=0.035..0.035 rows=582 loops=1)
                    Index Cond: (to_tsvector('simple'::regconfig, title) @@ '''test'''::tsquery)
              ->  Bitmap Index Scan on fts_advice_body  (cost=0.00..8.15 rows=21 width=0) (actual time=0.002..0.002 rows=0 loops=1)
                    Index Cond: (to_tsvector('simple'::regconfig, body) @@ '''test'''::tsquery)
  ->  Hash  (cost=4.98..4.98 rows=298 width=21) (actual time=0.042..0.042 rows=298 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 24kB
        ->  Seq Scan on categories c  (cost=0.00..4.98 rows=298 width=21) (actual time=0.005..0.020 rows=298 loops=1)
Planning Time: 0.115 ms
Execution Time: 0.338 ms

Super quick, all good, the query uses my indices.

But when I add a search clause for the category name, things change:

EXPLAIN ANALYZE
  SELECT *
  FROM items i
    INNER JOIN categories c ON i.category_id = c.id
  WHERE to_tsvector('simple', i.title) @@ to_tsquery('simple', 'test')
     OR to_tsvector('simple', i.body) @@ to_tsquery('simple', 'test')
     OR to_tsvector('simple', c.name) @@ to_tsquery('simple', 'test');

Result:

Hash Join  (cost=8.71..6441.76 rows=123 width=79) (actual time=0.112..12.488 rows=1164 loops=1)
  Hash Cond: (i.category_id = c.id)
  Join Filter: ((to_tsvector('simple'::regconfig, i.title) @@ '''test'''::tsquery) OR (to_tsvector('simple'::regconfig, i.body) @@ '''test'''::tsquery) OR (to_tsvector('simple'::regconfig, c.name) @@ '''test'''::tsquery))
  Rows Removed by Join Filter: 7098
  ->  Seq Scan on items i  (cost=0.00..71.31 rows=4131 width=58) (actual time=0.007..0.309 rows=4131 loops=1)
  ->  Hash  (cost=4.98..4.98 rows=298 width=21) (actual time=0.036..0.037 rows=298 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 24kB
        ->  Seq Scan on categories c  (cost=0.00..4.98 rows=298 width=21) (actual time=0.004..0.017 rows=298 loops=1)
Planning Time: 0.128 ms
Execution Time: 12.548 ms

Indices aren't used any more and it falls back to a sequential scan, which will only get slower and slower as the data size increases (I tested on a sample of just ~4k rows).

How can I make such a query and still make use of my indices?

Answer 1

PostgreSQL has no choice but to calculate the whole join and discard the rows that don't match the condition (see this article for an explanation). The classical solution for that is to write two queries and combine them with UNION:

SELECT *
FROM items i
    INNER JOIN categories c ON i.category_id = c.id
WHERE to_tsvector('simple', i.title) @@ to_tsquery('simple', 'test')
   OR to_tsvector('simple', i.body) @@ to_tsquery('simple', 'test')
UNION
SELECT *
FROM items i
    INNER JOIN categories c ON i.category_id = c.id
WHERE to_tsvector('simple', c.name) @@ to_tsquery('simple', 'test');

If all conditions (including the join conditions) are indexed and the query selects not too many rows, that will be fast