Let ILIKE utilize existing lower() expression indexes - Mailing list pgsql-hackers

Hi hackers,
ILIKE predicates currently cannot use expression indexes on lower(column), despite ILIKE being implemented by applying the same case-folding transformation before pattern matching. Two examples on the same table:

Table details:

```
postgres=# \d interactions
Table "public.interactions"
Column | Type | Collation | Nullable | Default
------------------+--------------------------------+-----------+----------+---------
...
country | text | | |
...

Indexes:
"idx_btree" btree (lower(country) text_pattern_ops)
"idx_trgm" gin (lower(country) gin_trgm_ops)
```

1. Query plan showing possible utilization of the `gin` index:

```sql
postgres=# explain analyze select * from interactions where lower(country) like lower('%ice%');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on interactions (cost=276.31..18436.31 rows=40000 width=98) (actual time=2.304..18.031 rows=3961.00 loops=1)
Recheck Cond: (lower(country) ~~ '%ice%'::text)
Heap Blocks: exact=3523
Buffers: shared hit=1730 read=1799
-> Bitmap Index Scan on idx_trgm (cost=0.00..266.31 rows=40000 width=0) (actual time=1.244..1.245 rows=3961.00 loops=1)
Index Cond: (lower(country) ~~ '%ice%'::text)
Index Searches: 1
Buffers: shared hit=6
Planning:
Buffers: shared hit=1
Planning Time: 0.189 ms
Execution Time: 18.357 ms
(12 rows)

postgres=# explain analyze select * from interactions where country ilike '%ice%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..24736.83 rows=9685 width=98) (actual time=1.116..227.046 rows=3961.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=10885 read=6675
-> Parallel Seq Scan on interactions (cost=0.00..22768.33 rows=4035 width=98) (actual time=0.380..212.257 rows=1320.33 loops=3)
Filter: (country ~~* '%ice%'::text)
Rows Removed by Filter: 332013
Buffers: shared hit=10885 read=6675
Planning Time: 0.255 ms
Execution Time: 227.270 ms
(10 rows)
```

2. Query plan showing possible utilization of the `btree` index:

```sql
postgres=# explain analyze select * from interactions where lower(country) like lower('ind%');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on interactions (cost=71.67..11098.90 rows=5000 width=98) (actual time=3.049..18.602 rows=7792.00 loops=1)
Filter: (lower(country) ~~ 'ind%'::text)
Heap Blocks: exact=6312
Buffers: shared hit=6321
-> Bitmap Index Scan on idx_btree (cost=0.00..70.42 rows=5000 width=0) (actual time=1.279..1.279 rows=7792.00 loops=1)
Index Cond: ((lower(country) ~>=~ 'ind'::text) AND (lower(country) ~<~ 'ine'::text))
Index Searches: 1
Buffers: shared hit=9
Planning:
Buffers: shared hit=1
Planning Time: 0.191 ms
Execution Time: 19.340 ms
(12 rows)

postgres=# explain analyze select * from interactions where country ilike 'ind%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..24736.83 rows=9685 width=98) (actual time=1.317..218.114 rows=7792.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=8804 read=8756
-> Parallel Seq Scan on interactions (cost=0.00..22768.33 rows=4035 width=98) (actual time=0.577..199.651 rows=2597.33 loops=3)
Filter: (country ~~* 'ind%'::text)
Rows Removed by Filter: 330736
Buffers: shared hit=8804 read=8756
Planning Time: 0.279 ms
Execution Time: 218.477 ms
(10 rows)
```

Same rows, ~8.5x difference, purely because the query used ILIKE instead of the LIKE-rewritten form.

Generic_Text_IC_like [1] computes ILIKE, for any non-C locale, by calling lower() on both operands and then doing a plain match:

```c
pat = DatumGetTextPP(DirectFunctionCall1Coll(lower, collation, PointerGetDatum(pat)));
...
return UTF8_MatchText(s, slen, p, plen, 0);
```

C locale has its own lazy per-character fold path (C_IMatchText), which is compiled with MATCH_LOWER defined, so each character comparison runs through GETCHAR = pg_ascii_tolower -- the same ASCII fold used by lower(), just applied per-byte during the scan instead of precomputed. So ILIKE and lower(column) LIKE lower(pattern) aren't approximately the same, they're the same computation, for any collation, today. The planner just never connects an ILIKE clause to a lower() expression index during index matching.

However, this is tied to the current implementation, and not a fixed contract. There's an open thread proposing to switch that non-C path from lower() to casefold() [2], to fix cases lower() folds wrong (Greek final sigma, Turkish dotless, etc.). If that lands, this would need to track it and match against casefold(column) indexes instead for affected locales.

Feedback wanted on:

1. Whether this is worth pursuing regardless of how the CASEFOLD() thread resolves.
2. Where it should live: texticlike_support isn't reachable for this -- match_opclause_to_indexcol() only calls a clause's support function once match_index_to_operand() already finds a structural match, and a bare country ILIKE 'ind%' clause never structurally matches a lower(country) index expression. So this needs some form of clause rewrite during index matching, done where IndexOptInfo is already visible, rather than a change to texticlike_support itself. Open to suggestions on where exactly that should live.
3. Whether to scope this to lower() only for now -- ILIKE's implementation never calls upper(), so an upper(column) index would need its own soundness argument rather than being a symmetric case.

Tested on PostgreSQL version: 18.4

References:

[1] https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/like.c

[2] https://www.postgresql.org/message-id/64d7949bad90545f981ac7513fb0b4954daca2c9.camel%40j-davis.com

Thanks,
Sivaprasad Murali