
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I would like to re-table my proposal from many moons ago to allow
> > pattern-matching operations to use indexes under any locale.

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 ?

Seems this would be of more general use, than an extra index for LIKE, no ?
Some upper bound would still be needed for a selectivity estimate, but for 
estimation purposes it would not really need to be watertight.

Andreas


Tue, 13 May 2003 11:29:01 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: LIKE indexing proposal 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.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


Tue, 13 May 2003 10:07:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: LIKE indexing proposal 