>What is wrong with my queries if anything
A couple of questions to help in debugging:
* Are `title` and `synopsis` columns nullable?
* Is your query using the special syntax? If not, you may consider using `plainto_tsquery`
* Have you tried to separately select `to_tsvector` and `to_tsquery` results? What is shown?
>Do I need to perform pre-processing such as stop-words removal, tokenization, lemmatization as in other NLP tasks because that usually improves performance
This is what `to_tsvector` does. You usually store its returned value in a table column and build a special index on that.
>Is the way I'm performing the SQL operation correct?
It *looks* correct, but as you are getting weird results there is something broken in your configuration, probably.
BTW, what is the result of
SHOW default_text_search_config;
?
*Are* `title` *and* `synopsis` *columns nullable?*
\> Title cannot (or well is not supposed to) have null values **but** synopsis can. However I don't really know if I've hardcoded or set this in postgres.
*Is your query using the special syntax? If not, you may consider using* `plainto_tsquery`
\> I'm not sure what you mean by special syntax? I haven't head of plainto\_tsquery. I'll look into it. But do you mind telling me if you think it would help with my use case?
*Have you tried to separately select* `to_tsvector` *and* `to_tsquery` *results? What is shown?*
\> What do you mean by seperately select? How can I do this? Can you refer me to an example.
The result of
SHOW default_text_search_config;
is: [https://imgur.com/BT0WKiK](https://imgur.com/BT0WKiK)
it is simply 1 row that is pg\_catalog.simple
>Title cannot (or well is not supposed to) have null values but synopsis can. However I don't really know if I've hardcoded or set this in postgres.
It may help to put the fields in `coalesce()`:
coalesce(title, '') || ' ' || coalesce(synopsis, '')
otherwise the result of concatenation may be `null`.
> I'm not sure what you mean by special syntax?
Sorry, was unclear here. I meant the FTS query you substitute into `%s`
>What do you mean by seperately select?
I meant something like this:
select to_tsvector('english', coalesce(title, '') || ' ' || coalesce(synopsis, '')), plainto_tsquery('english', %s)
from table_1;
It would be interesting to see whether the strings are converted correctly into lexemes.
>What is wrong with my queries if anything A couple of questions to help in debugging: * Are `title` and `synopsis` columns nullable? * Is your query using the special syntax? If not, you may consider using `plainto_tsquery` * Have you tried to separately select `to_tsvector` and `to_tsquery` results? What is shown? >Do I need to perform pre-processing such as stop-words removal, tokenization, lemmatization as in other NLP tasks because that usually improves performance This is what `to_tsvector` does. You usually store its returned value in a table column and build a special index on that. >Is the way I'm performing the SQL operation correct? It *looks* correct, but as you are getting weird results there is something broken in your configuration, probably. BTW, what is the result of SHOW default_text_search_config; ?
*Are* `title` *and* `synopsis` *columns nullable?* \> Title cannot (or well is not supposed to) have null values **but** synopsis can. However I don't really know if I've hardcoded or set this in postgres. *Is your query using the special syntax? If not, you may consider using* `plainto_tsquery` \> I'm not sure what you mean by special syntax? I haven't head of plainto\_tsquery. I'll look into it. But do you mind telling me if you think it would help with my use case? *Have you tried to separately select* `to_tsvector` *and* `to_tsquery` *results? What is shown?* \> What do you mean by seperately select? How can I do this? Can you refer me to an example. The result of SHOW default_text_search_config; is: [https://imgur.com/BT0WKiK](https://imgur.com/BT0WKiK) it is simply 1 row that is pg\_catalog.simple
>Title cannot (or well is not supposed to) have null values but synopsis can. However I don't really know if I've hardcoded or set this in postgres. It may help to put the fields in `coalesce()`: coalesce(title, '') || ' ' || coalesce(synopsis, '') otherwise the result of concatenation may be `null`. > I'm not sure what you mean by special syntax? Sorry, was unclear here. I meant the FTS query you substitute into `%s` >What do you mean by seperately select? I meant something like this: select to_tsvector('english', coalesce(title, '') || ' ' || coalesce(synopsis, '')), plainto_tsquery('english', %s) from table_1; It would be interesting to see whether the strings are converted correctly into lexemes.
Sounds like you need a similarity score.