I'm currently building indexing for pattern matching in PostgreSQL database. After reading some articles, I decided to use pg_trgm extension. But according to this documentation we can use both GIN or GIST index.

I'm just curious which index works better with pg_trgm.

1 Answer 1


The answer depends on the query you want to optimize.

If the query looks like

FROM ...
WHERE col % 'string'

a GIN index usually works better.

If the query looks like

FROM ...
ORDER BY col <-> 'string'

you need to use a GiST index, because a GIN index cannot support that.

  • I'm going to use LIKE operator, so it's GIN, isn't it? Commented Jan 26, 2022 at 9:48
  • Typically yes. Why don't you run a test with a bigger data set? Commented Jan 26, 2022 at 9:53
  • currently I'm building big mock data. but get this error when inserting stackoverflow.com/questions/10399727/… Commented Jan 26, 2022 at 10:08

Your Answer

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

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