PostgreSQL full text search words delimiter

full-text-searchpostgresql

Please help me to understand how to change words delimiter when I use gin index. The problem is that "," ";" and " " signs are words delimiters but the "." isn't. So in phrase "Some 11.22.33.44 IP" I am able to find "Some" "11.22.33.44" "IP" words. When I am looking for "33" – unable to find it. Many thanks for the help.

Best Answer

The "natural" way to do this would be to change or write your own text search parser. While it is possible to do that, it is unreasonably difficult.

Do you want both '11.22.33.44' and '33' to show up as individual tokes, or just '33'? If the latter, then maybe just preprocess your data by replacing all '.' with ' ':

replace(doc,'.',' ')

Of course you should do the same replacement on the queries as well before passing them to plainto_tsquery.

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

a word or two belonging to a pre-set, almost 'constant', list of words.

Have you considered scanning the texts as they are input and flagging them (possibly with a many-to-many link to another table containing your list of keywords)?

PostgreSQL 9.2 – Full Text Search in XML Documents

If you don't want to write your own parser, the quick and dirty solution would be to replace < and > with some other punctuation, so that the existing parsers don't decide to discard them as html tags.

SELECT * 
FROM xmldocument 
WHERE to_tsvector(regexp_replace(content::text,'[<>]',' ','g')) @@ 'boo2';

As to why I don't just use ILIKE % etc. the reason is that I need to optimize by using a GIN index and I don't think it is possible or meaningful to build an index on simple VARCHAR values.

If you use the pg_trgm extension, you can build a gin index on VARCHAR values which will optimize ILIKE queries. How effective it is depends on the size of your indexed documents, and the size of your query. I'd recommend trying it and seeing how it does for you.

Version 1.2 of pg_trgm (to be included in PostgreSQL 9.6, but it is fairly easy to back-port into 9.4 and 9.5 if you are willing to compile some code) will be much more effective with large queries.

Best Answer

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

PostgreSQL 9.2 – Full Text Search in XML Documents

Related Question