Re: LIKE indexing proposal

"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
> Wouldn't existing b-trees be sufficient, if they could be 'scanned' starting
> with the operator >= ? Thus a LIKE 'ABC%' could be done by stepping an (ascending)
> index fom x >= 'ABC' up to the first key that does not have 'ABC' as first
> characters ?

You've apparently forgotten all our previous history on that subject
:-(. The above does not work in the presence of special sort rules for
digraphs, etc. For example, that LIKE should certainly match ABCH ...
but there are locales in which "CH" sorts after "D" and would not be
found by an indexscan that runs from ABC to ABD.

Even with no digraphs, the optimization is broken by locales that treat
spaces as second-class citizens, prefer caseless to case-sensitive
comparisons, etc. It doesn't work in en_US locale, for example.

regards, tom lane