I am wondering what is the best practice to use the right query in my case.
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.
So I am not sure, if I did it right and my table looks like this and has over 10.000 entries:
Column | Type | Modifiers -----------+---------------+----------- id | integer | not null doc | bytea | not null textdoc | text | not null
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. And 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.
So my actual query looks (simplified) like this:
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_trgmand it wasn't really faster. It was all 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 the text in the column
textdoc is over 2047 or a string in the
textdoc is over 2047 characters?