Effect of using operators in to_tsquery()

Question

I am trying to use text search of postgresql, I have created a ts_vector column using existing two columns of the table and index that column using GIN index. Now when i am querying the i have noticed a weird behaviour, score of the query reducing when i am using operators like "&" or "|" in the to_tsquery function.

For 1 million records :-

1. explain select title, body from xyz ff where search_vector @@ to_tsquery('english', 'apps'); --> score is around 300
2. explain select title, body from xyz ff where search_vector @@ to_tsquery('english', 'apps & apps'); --> score is around 30

This behaviour i am not able to understand, the difference in bith the queries is using a operator, if i change it to "apps & tenant" then also score is around 30 but for single word search the score is high. Can someone please explain this behaviour and the reason behind it.

Thanks in advance.

Answer 1

When you use operators & or | you are telling to to_tsquery function to get a rule that have the two words in your row. For example if you put `to_tsquery('english', 'apps & tenant', your result needs to contain the two words in the query (not in the same order). So, the query will search for the two words.

Another thing that can influence your score is the weights on the search.

In this link you can see a better understand about the operators.

Answer 2

By "score", should I assume you mean the row estimates reported by the EXPLAIN?

The row estimates for this type of thing are often not very good. Why do you care? Are you getting the wrong plan? If so, please explain in more detail what plan you are getting and what plan you think you should be getting, and why you think that.

Are you doing academic research? In that case, you absolutely should not be using 9.5. No one who even cares about performance should be using 9.5, much less anyone doing academic research.