The problem is I tried the
ILIKE and it was faster then
pg_trgm and the
postgres full text search, because they often run in a timeout or throw an error:
NOTICE: word is too long to be indexed DETAIL: Words longer than 2047 characters are ignored.
I am not sure, if I did it right and my table looks like this and has over 10.000 entries:
So I have the document (PDF or DOCX) and I give it to the converter which makes a huge string out of the documents and put it into the
textdoc. A document can have 150 pages or 40.000 words(strings). So in each textdoc can be a huge amount of words. When I have over 10.000 entries in the database and each of this entry has a huge amount of words in
textdoc. How can I speed this up?
My query looks (simplified):
SELECT * FROM mytable WHERE textdoc ILIKE '%word from input mask%'
When I am searching the web, the standard message is don't use
ILIKE. I used
pg_trg. It wasn't really faster, with about three minutes for a query.
"PostgreSQL 10.4 on x86_64-pc-linux-gnu, (Red Hat), 64-bit"
Analyze (hope I did it right)... I run:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM mytable WHERE textdoc ILIKE '%word from input mask%'
Here is the output: https://explain.depesz.com/s/wqLh
Another question is, the notice of the 2047 characters: Does this mean, that a text in the column
textdoc is over 2047 or a string in the
textdoc is over 2047 characters?