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.
WHERE to_tsvector('english', col) @@ to_tsquery('english', 'zzz')
is not equivalent tocol ~~ '%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 ...