Postgres 14 and pg_trgm index on primary key column make join operations super slow

Question

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)

References

• https://www.postgresql.org/docs/current/release-14.html
• https://www.postgresql.org/docs/11/pgtrgm.html