Re: LIKE indexing proposal

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.

Do you have better answers to the objections that were raised the last
time?

In particular, I wonder whether it's worth putting effort into this at
all, rather than trying to make the contrib full-text-indexing support
mainstream-quality.


> Since character-by-character comparison is essentially binary comparison,
> I named the operator classes, text_binary_ops, etc.  Another idea is to
> name them text_like_ops or text_pattern_ops or whatever,

text_binary_ops seems a bit of a contradiction in terms :-(.  I'd prefer
either of the other choices.

> Should there be a special case for the C locale?

Yes, if only on backwards-compatibility grounds.  Database setups that
worked well before should not get arbitrarily broken.

> I'm unclear on how the selectivity estimation should work.  The system
> doesn't collect statistics based on the new #<#-style operators, so the
> estimates are based on the normal comparison, which might have little to
> do with reality.

Not sure that this really matters; the selectivity estimation for LIKE
is so crude that I doubt it would get significantly worse.  But one
could conceive of storing statistics computed both ways in pg_statistic.
        regards, tom lane