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
order_id
column that presumably (you should update your post with your table and index definitions) holds no meaningful information in itself?