Tuning Postgres Fulltext search

Question

The table is product:

             Table "public.product"
     Column      |           Type           |
-----------------+--------------------------+
 id              | uuid                     |
 attributes      | jsonb                    |

Note that attributes is a jsonb field. At the moment I have ~5k rows and I am querying it like this:

select id, to_tsvector(attributes::text) @@ to_tsquery('22222') from product;

This query already takes a couple of seconds to complete and I want to know if there is anything I can do to improve that time, namely indexes or improved query ?

For starting this query returns:

                  id                  | found 
--------------------------------------+-------
 a8230602-ff3f-4414-affc-3594abcfa617 | f
 da0c70d5-5108-42ea-941d-123589129feb | f
 24ac417a-466c-465c-b346-4fad7a9ad3d8 | f
 4bee6122-c5d7-4e0c-840e-e04c28888a9a | f
 ce5fe539-bcb2-4cec-9012-b2501df7012e | f

Which is undesirable, is there a way to return only the rows that have a match?

Answer 1

You need to move the condition to the WHERE clause:

SELECT *
FROM   product
WHERE  to_tsvector('english', attributes::text) @@ to_tsquery('22222');

And create a full text index on the expression:

CREATE INDEX textsearch_idx ON product
USING GIN (to_tsvector('english', attributes::text));

Index expression and the expression in the query must match.

Details in the manual.

Or you may be able to use a jsonb GIN index:

• What's the proper index for querying structures in arrays in Postgres jsonb?

But that's not going to work if you want to search keys and values at once.

You might be better off with a normalized table layout to begin with ...