0

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?

4
  • Hi, and welcome to dba.se! Do you really require a BIGINT for categories? Just a thought.
    – Vérace
    Jan 17 at 19:42
  • What was the reasoning behind this design, rather than just storing categories directly as text in the items table?
    – jjanes
    Jan 17 at 21:41
  • This is an intentionally reduced example to increase the focus on my current problem. In our real application, categories are way more complex - they form a hierarchy etc.
    – Oromis
    Jan 18 at 16:19
  • About the BIGINT... We use Rails and Rails uses BIGINT for primary keys by default. Likely we will never require the full range for categories :)
    – Oromis
    Jan 18 at 16:20

1 Answer 1

2

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

4
  • Thanks for your answer! The UNION is a solution for this example, but then it will be harder to perform pagination and sorting on the entire result set (I guess wrapping the UNION'ed queries in an outer query could solve that).
    – Oromis
    Jan 18 at 16:22
  • Could you elaborate why Postgres "has no choice but to calculate the whole join and discard the rows that don't match the condition"? I'd love the understand the reason for this.
    – Oromis
    Jan 18 at 16:22
  • 1
    I have added a link. Jan 18 at 18:22
  • Thank you very much for the explanation link, I learned a lot from this as well: cybertec-postgresql.com/en/avoid-or-for-better-performance
    – Oromis
    Jan 19 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.