Creating index on a big text column in postgres

Question

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.

Answer 1

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