PepperoniPizza PepperoniPizza - 1 year ago 75
JSON Question

Tuning Postgres Fulltext search

The table is product:

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

Note that attributes is a
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 Source

You need to move the condition to the WHERE clause:

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:

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 ...