Re: LIKE indexing proposal

Peter Eisentraut <peter_e(at)gmx(dot)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