Postgres fulltextsearch with more than one word in one column

Question

I want to create a fulltext select query on a postgres database. For example I want to find all books with title "Harry Potter". If I just look up for Harry with the fulltext search it runs fast. e.g.

SELECT * FROM books WHERE to_tsvector('simple', cast(title AS text)) @@ plainto_tsquery('simple',cast('harry' AS text))

But if I try to combine a title like Harry Potter, my query is running out of time (like one minute)

SELECT * FROM books WHERE to_tsvector('simple', cast(title AS text)) @@ plainto_tsquery('simple',cast('harry' AS text)) AND to_tsvector('simple', cast(title AS text)) @@ plainto_tsquery('simple',cast('potter' AS text))

What is my mistake? I need this fulltextsearch also a combination of 1 or more words.

If there exists a better solution than to combine two words in one query like

 SELECT * FROM books WHERE to_tsvector('simple', cast(title AS text)) @@ plainto_tsquery('simple',cast('harry potter' AS text))

EDIT: Found something: The count of my Harry Potter is about 110.000 datas (actually like 1 mio data in books). If I look up for e.g. Ice Fire there are just 300 results in database and my query is running sooo fast. Maybe its a problem about the resultset getting back from the database? I actually using limit 100.

"If there exists a better solution than to combine two words in one query like": 'Better' in what way? Is combining them in one query still slow? In that case, post an EXPLAIN (ANALYZE, BUFFERS) of it. Or is it fast, but you just find it ugly for some reason? — jjanes
– jjanes, Commented Jan 14, 2020 at 15:31

Laurenz Albe · Accepted Answer · 2020-01-14 12:35:03Z

2

You should use a phrase search:

WHERE to_tsvector('simple', title) @@ to_tsquery('simple', 'harry <-> potter')

answered Jan 14, 2020 at 12:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dennis Over a year ago

Same high responsetime :/

Laurenz Albe Over a year ago

Then you need a GIN index on to_tsvector('simple', title).

Dennis Over a year ago

I added a GIN index. Is the query still the same?

SELECT * FROM books WHERE to_tsvector('simple', cast(title AS text)) @@ plainto_tsquery('simple',cast('harry potter' AS text))

?

Laurenz Albe Over a year ago

The indexed expression must be exactly the same as the expression left of the @@. Use EXPLAIN to find out if the index is used.

Dennis Over a year ago

GIN works finde, but still have another problem. Doing new Ticket I guess.

Collectives™ on Stack Overflow

Postgres fulltextsearch with more than one word in one column

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related