Translation from Contains to strpos leads to extremely slow queries

[madiganz]

The database I am working with has 10s of millions of rows and we see some major performance projects when querying with out API using contains queries. I noticed that Entity Framework is translating Contains to doing a strpos operation. I also came across a really old issue in Entity Framework and a comment from your team about the translation in npgsql - dotnet/efcore#474 (comment). I found out that I can use a trigram index for ILIKE and LIKE operations and that significantly improved performance. Queries that before would take 30+ seconds would have under 100ms. I understand that trigram indices increase the cost of writes, and I haven't fully tested that aspect yet, but based on initial research the benefit of using a trigram index greatly outweighs the cost. All this being said, the trigram index does not work when strpos is used in the generated query. I was wondering if there was anything in particular that we could do so that we can use LIKE operations instead of strpos.

[madiganz]

I'm closing this as it looks like this was fixed in v8