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 if there is any way 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)
I'm not sure if this is really a bug, but its' super weird if the query planner favors the trigram index over the b-tree index for joining is not optimal to me. Thank you so much.
References