1

I am wondering what is the best practice to use the right query in my case.

The problem is I tried the LIKE and 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.

Required information:

Database: "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?

  • Increase the work_mem to a bigger value and see if notice dissappear. for example... set work_mem to '128MB';... If still appearing try bigger values. If work or not please let me know and how big you need it! – Luciano Andress Martini Oct 3 '18 at 15:56
  • That is not an ERROR, that is a NOTICE. Why is your converter generating "words" of over 2047 characters, and are you OK with ignoring them? – jjanes Oct 3 '18 at 17:13
  • 1
    Please read dba.stackexchange.com/tags/postgresql-performance/info and edit your question accordingly. Simplifying the query is great, but once done you have to show the actual simplified query and the EXPLAIN ANALYZE of it. – jjanes Oct 3 '18 at 17:25
  • @jjanes Ty, I updated it, didn't know that. I am not sure how I can find the word of over 2047 characters. It seems a bit weird for me. On a quick overview the strings(words) of a document are looking good. – Spedo De La Rossa Oct 4 '18 at 6:53
0

The NOTICE means that textdoc contains at least one word within it that is more than 2047 characters. The definition of a word depends on what configuration you use for your ts_config. Words are generally strings of characters separated by spaces or certain punctuation. Hyphenated words will generally be emitted as each separate word, plus the entire hyphenated phrase as an extra "word", at least with the ts_configs that I am familiar with. You can use the ts_debug function to find out how it actually breaks up the string emitted by your specific converter using your chosen ts_config.

select alias, description, token from mytable cross join ts_debug('english',textdoc) 
    where length(token)>2047;

(On second thought, ts_lexize may be better than ts_debug, but I am not as familiar with it.)


Regarding the ILIKE query, your query is returning 2/3 of the rows in the table. Any index, whether pg_trgm-based or tsvector-based, is unlikely to help you in that situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.