Re: Allow ILIKE forward matching to use btree index

On Thu, 2025-01-16 at 14:53 +0900, Yugo NAGATA wrote:
> Instead of generating complete patterns considering every case-
> varying characters,
> two clauses considering only the first case-varying character are
> generated.

Did you consider other approaches that integrate more deeply into the
indexing infrastructure? This feels almost like a skip scan, which
Petter Geoghegan is working on. If you model the disjunctions as skips,
and provide the right API that the index AM can use, then there would
be no limit.

For example:

start scanning at '123FOO'
when you encounter '123FOP' skip to '123FOo'
continue scanning
when you encounter '123FOp' skip to '123FoO'
...

Also, I'm working on better Unicode support to handle multiple case
variants in patterns. For instance, the Greek letter Sigma has three
case variants (one upper and two lower). What's the right API so that
the index AM knows which case variants will sort first, so that the
skips don't go backward?

Regards,
Jeff Davis