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.
You should use a phrase search:
WHERE to_tsvector('simple', title) @@ to_tsquery('simple', 'harry <-> potter')