3 ms·

Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.

by mjewkes 5y ago

e12e 5y ago
Even if using ILIKE over the result of an imprecise query?
- deleted 5y ago
  [deleted]
- mjewkes 5y ago
  ILIKE itself is a linear scan. The only way to index them are trigram indicies, which are very inefficient (and sometimes not usable) if you're searching document-length content. Whether or not it works in your specific situation depends on your use case.
simonw 5y ago
Exact phrase searching works in PostgreSQL full-text search - here's an example: https://simonwillison.net/search/?q=%22nosql+database%22 https://simonwillison.net/search/?q=%22nosql+database%22 I'm using search_type=websearch https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00d4c95c88c8371cfc17453b0726c23/blog/views.py#L492-L494 https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00... That's using websearch_to_tsquery() which was added in PostgreSQL 11: https://www.postgresql.org/docs/11/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES https://www.postgresql.org/docs/11/textsearch-controls.html#...
- SahAssar 5y ago
  I had some issues with this recently as I couldn't get a FTS query to find something looking like a path or url in a query. As an example from your site: https://simonwillison.net/2020/Jan/6/sitemap-xml/ https://simonwillison.net/2020/Jan/6/sitemap-xml/ contains the exact text https://www.niche-museums.com/ https://www.niche-museums.com/, but I cannot find a way to search for that phrase exactly (trying https://simonwillison.net/search/?q=%22www.niche-museums.com%22 https://simonwillison.net/search/?q=%22www.niche-museums.com... works though). I tried both in my own psql setup and on your site, and it seems like exact phrase searching is limited to the language used, even if it would be an exact string match. Are there any workarounds for that?
- mjewkes 5y ago
  Try https://simonwillison.net/search/?q=%22your+own+benchmarks%22 https://simonwillison.net/search/?q=%22your+own+benchmarks%2... Looks like we get 37 results, of which 2 are true positives. Looks like "your" and "own" are both contained in the english.stop stopwords list. So you could fix this by removing stopwords from your dictionary. While disabling the stemmer is relatively easy (use the 'simple' language setting for your ts_query), altering the stopword dictionaries is more involved, and not easy to maintain or pass between developers/environments, and not at all easy to share between queries. And so the most common suggestion is to use ILIKE. Lucene has no problems with any of this.
rattray 5y ago
Pardon my ignorance – what is exact phrase matching and why doesn't it work with tsvector?
- mjewkes 5y ago
  Exact phrase matching is what google (sometimes? used to?) do for you if you put your search terms in double "full quotes". It returns only results that contain the exact multi word sequence in exactly the same order.

Exact phrase matching. This generally requires falling back to ILIKE, which is not performant.

e12e 5y ago

Even if using ILIKE over the result of an imprecise query?

deleted 5y ago
[deleted]
mjewkes 5y ago
ILIKE itself is a linear scan. The only way to index them are trigram indicies, which are very inefficient (and sometimes not usable) if you're searching document-length content. Whether or not it works in your specific situation depends on your use case.

simonw 5y ago

Exact phrase searching works in PostgreSQL full-text search - here's an example: https://simonwillison.net/search/?q=%22nosql+database%22 https://simonwillison.net/search/?q=%22nosql+database%22 I'm using search_type=websearch https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00d4c95c88c8371cfc17453b0726c23/blog/views.py#L492-L494 https://github.com/simonw/simonwillisonblog/blob/a5b53a24b00... That's using websearch_to_tsquery() which was added in PostgreSQL 11: https://www.postgresql.org/docs/11/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES https://www.postgresql.org/docs/11/textsearch-controls.html#...

SahAssar 5y ago
I had some issues with this recently as I couldn't get a FTS query to find something looking like a path or url in a query. As an example from your site: https://simonwillison.net/2020/Jan/6/sitemap-xml/ https://simonwillison.net/2020/Jan/6/sitemap-xml/ contains the exact text https://www.niche-museums.com/ https://www.niche-museums.com/, but I cannot find a way to search for that phrase exactly (trying https://simonwillison.net/search/?q=%22www.niche-museums.com%22 https://simonwillison.net/search/?q=%22www.niche-museums.com... works though). I tried both in my own psql setup and on your site, and it seems like exact phrase searching is limited to the language used, even if it would be an exact string match. Are there any workarounds for that?
mjewkes 5y ago
Try https://simonwillison.net/search/?q=%22your+own+benchmarks%22 https://simonwillison.net/search/?q=%22your+own+benchmarks%2... Looks like we get 37 results, of which 2 are true positives. Looks like "your" and "own" are both contained in the english.stop stopwords list. So you could fix this by removing stopwords from your dictionary. While disabling the stemmer is relatively easy (use the 'simple' language setting for your ts_query), altering the stopword dictionaries is more involved, and not easy to maintain or pass between developers/environments, and not at all easy to share between queries. And so the most common suggestion is to use ILIKE. Lucene has no problems with any of this.

rattray 5y ago

Pardon my ignorance – what is exact phrase matching and why doesn't it work with tsvector?

mjewkes 5y ago
Exact phrase matching is what google (sometimes? used to?) do for you if you put your search terms in double "full quotes". It returns only results that contain the exact multi word sequence in exactly the same order.