I've just upgraded my postgres instance from v11 to v14. There was an interesting problem because we have a trigram index on order_id column.

This new feature makes our simple join query on that column very slow. For example:

SELECT count(*) from order_rows o1 join order o2 on o1.order_id = o2.order_id

To solve, this problem the existing trigram index must be dropped and we cannot use ILIKE queries on this column. I just wonder is there anyway to tell postgres what index (in this case btree index) to use when doing the join operations?

[Postgres 14] Allow GiST/GIN pg_trgm indexes to do equality lookups (Julien Rouhaud)


  • Why would a trigram index be used on an order_id column that presumably (you should update your post with your table and index definitions) holds no meaningful information in itself?
    – J.D.
    Jul 2 at 14:46


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.