1

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

1
  • 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.
    Commented Jul 2, 2022 at 14:46

1 Answer 1

1

I can confirm this is still going on in postgres 16.1.

We have the same structure where we want to do FTS search on customer_id column and have added a GiST trgm index on it, after upgrading to postgres 14, we saw a performance drop of a factor 10 in our bill job. We never figured out why and the performance was still adequate and investigation was never prioritized.

The explanation is the same as yours, the pkey index is not used anymore in favor of the much slower trgm index.

The solution is to drop the trgm index, and stop supporting FTS search on customer ids

As to why someone would want to do FTS search on a id column is not relevant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.