Case-insensitive query support without ILIKE operator

Is your proposal related to a problem?

We are using PostgreSQL v14.

We have a few decently large tables (~50-70 GB) where we have jobs that need to update multiple rows that match a case-insensitive lookup on a few columns. For a long time we used the ILIKE/_ilike query operator for the update and used GIN indexes on the columns to improve performance.

However, the development cost of making sure these GIN indexes are properly maintained is somewhat high. Because these are pretty actively used tables there are frequent insertions and updates, meaning we have had to spend a lot of development time tuning the autovacuum, tuning the gin_pending_list_limit and work_mem, etc.

Additionally, the table is now large enough (and the values in the queried columns similar enough to each other) that an _ilike lowercase equality lookup (_ilike: "XXX", without pattern matching) performs notably worse than if there were a BTREE index on LOWER(column_name) and we had done a pure SQL query of WHERE LOWER(column_name) = 'XXX'. However, we are unable to perform the latter query with Hasura/GraphQL because as far as I can tell there is no way in the schema to have Hasura generate a WHERE LOWER(column_name) = 'XXX' clause, which is the only way to get the Postgres query planner to actually use a LOWER(column_name) BTREE index.

Describe the solution you'd like

It's a pretty common for there to be a BTREE index on the lowercase value of a column and to have the query planner use that index by adding a WHERE LOWER(column_name) = 'XXX'. It would be great if there was a Hasura/GraphQL query operator that explicitly did this. Just as a clear example, if we had an operator like:

where: { column_name: { _lowereq: "XXX" } }

That would guarantee the generated SQL would be:

WHERE LOWER(column_name) = 'XXX'

Describe alternatives you've considered

The alternative as far as I can tell is creating custom view or SQL functions that use the LOWER(column_name) clause to get the query planner to use the index, but it is a pain and not very scalable to create a custom view or function every time we need to use a lowercase index on a column.

If the feature is approved, would you be willing to submit a PR?

I've never used Haskell nor contributed so unlikely I'd be useful, but certainly willing to try!

hasura / graphql-engine