4

We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE.

The column is of type text and it is used for ~~'%zzz%' queries. (Searching inside log messages). Query:

select * from tbl WHERE ( col ~~ '%zzz%')

Trying to create a btree index on the column

CREATE INDEX tbl_col_btree_idx ON tbl USING btree(col);

returns:

ERROR:  index row requires 8456 bytes, maximum size is 8191

Tried to follow the suggestion here: https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations/13452528#13452528 and create a gin/gist index.

The gist index creation attempt

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

also failed with maximum size problem.

The gin index creation attempt

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

failed with the error:

ERROR:  invalid multibyte character for locale. 
The server's LC_CTYPE locale is probably incompatible with the database encoding.

This seems just over-complicated for a simple ordinary need of running search queries over texts. I just want to improve the performance of the ~~'%zzz%' queries. Any "simple" way to do it?


UPDATE:

I've managed to find a workaround for now. Query performance improved by 3 times by:

  • Changing the query to:

    SELECT * FROM tbl WHERE to_tsvector('english', col) @@ to_tsquery('english', 'zzz');
    
  • And creating the following index (this time creating the index worked with the error below):

    CREATE INDEX text_ind ON tbl USING GIN (to_tsvector('english', col));
    

However when creating this index, it ignored some data with the error:

Word is too long to be indexed. Words longer than 2047 characters are ignored.

which is a little problematic because might make the searches miss some results.

NOTE: The mentioned workaround is not considered as the solution to the original problem; I am still looking for answers.

4
  • 2
    I would probably try to fix the problem indicated by: "The server's LC_CTYPE locale is probably incompatible with the database encoding." but I'm not sure how to change that in Windows
    – user1822
    Jan 9, 2019 at 9:15
  • I assume you are aware that WHERE to_tsvector('english', col) @@ to_tsquery('english', 'zzz') is not equivalent to col ~~ '%zzz%'. Like I explained in the referenced answer, text search is based on words (lexemes). Something like 'abczzz' is found by the the latter, but not the former filter expression. I would start by upgrading to the latest version of Postgres ... Jan 9, 2019 at 18:12
  • @a_horse_with_no_name I searched online a simple solution of changing the locale in windows to work with UTF8 and seems it requires to recreate the database and even then many report that the en-US.UTF8 isn't recognized. A simple way of fixing that error would be considered a solution here.
    – Guy L.
    Jan 9, 2019 at 21:38
  • @ErwinBrandstetter I guessed that for a search inside log messages it does seem fit, even though not totally equivalent. Upgrading the database means to upgrade it on all of our environments which is time consuming and will of course be done some day but right now I am searching for "drop-in" performance improvements without upgrading postgres\recreating database
    – Guy L.
    Jan 9, 2019 at 21:41

1 Answer 1

0

It is better to use trigram index: https://www.postgresql.org/docs/current/pgtrgm.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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