CODEWITHSUNDEEP
postgresqloptimizationindexingquery-optimizationdatabase-performance
user3706888
user3706888

Reputation: 177

Postgres gin Index for two words search

Aurora Postgres 12.8

Wildcard searches are working perfectly using gin index as under on multiple tables having millions of rows each.

CREATE INDEX index_name  ON table_name USING gin  (column_name gin_trgm_ops);
select col1, col2 from table_name where column_name ~* ('wild_text');

Life was good till the new requirement of searching for 'two-letter words' arrived.

select col1, col2 from table_name where column_name ~* ('aa');

since 'gin_trgm_ops' creates "trigram index" so 1 and 2 character search bypass index, needless to explain what happens next.

Question: Is there any option to create a gin index that supports 1 and 2 character search, unlike 'gin_trgm_ops'? Or any other solution for my case?

Wishes

Upvotes: 2

Views: 936

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246393

No, there is no good solution. But that is not so much a question of missing features as a matter of principle: very short search strings tend to produce large result sets, and indexes cannot help a lot with that.

My recommendation is to enforce a lower length limit of 4 or even higher on the length of the search string.

Upvotes: 1

Related Questions