Which Index Works Better with pg_trgm Extension?



  • 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 https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.42.8 we can use both GIN or GIST index.

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



  • The answer depends on the query you want to optimize.

    If the query looks like

    SELECT ...
    FROM ...
    WHERE col % 'string'
    

    a GIN index usually works better.

    If the query looks like

    SELECT ...
    FROM ...
    ORDER BY col  'string'
    LIMIT 1
    

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2