Re: Allow ILIKE forward matching to use btree index

On Thu, 16 Jan 2025 09:41:35 -0800
Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> 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'
> ...

That seems true there is similarity between ILIKE search and skip scan,
although, on my understand, skip scan is for multi-column indexes
rather than text. I have no concrete idea how the infrastructure for skip
scan to improve ILIKE, anyway.

> 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?

That seems true there is similarity between ILIKE search and skip scan,
although, on my understand, skip scan is for multi-column indexes
rather than text. I have no concrete idea how the infrastructure for skip
scan to improve ILIKE, anyway.

> 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?

I don't have idea for handling letters with multiple case variants in my patch,
either. I overlooked such pattern at all. So, I'll withdraw the proposal, for now.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>