Postgres gin Index for two words search

Question

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

Laurenz Albe · Accepted Answer · 2022-02-16 11:36:56Z

1

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.

answered Feb 16, 2022 at 11:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres gin Index for two words search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related