I would like to re-table my proposal from many moons ago to allow
pattern-matching operations to use indexes under any locale.

The idea is that since pattern matching works on a character-by-character
basis, we also need to interact with the index using operators that do a
comparison strictly on a character-by-character basis.  So we define a
separate set of comparison operators and operator classes for each
character type (text, varchar, bpchar, name), teach the optimizer to use
it for suitably anchored pattern-matching operations, and tell users to
create indexes using that special operator class if they want pattern
matching to use indexes.

Here are a couple of details to discuss:

I named the operators #<#, #>=#, etc.  If someone can think of better
names, let me know.

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, so that if some
change in the pattern matching operations would later force us to alter
the behavior of the operators away from being strictly memcmp(), we would
be free to change them.

Should there be a special case for the C locale?  Without extra code, if
you use the C locale and would like to use indexes for both equality and
pattern comparisons, you need two indexes with different operator classes.
That might seem wasteful, but then all locales would really work the same.

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.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 12 May 2003 19:35:07 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
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


Mon, 12 May 2003 13:45:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: LIKE indexing proposal 
Tom Lane writes:

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

The objection last time was that, according to the SQL standard, LIKE
patterns should use the locale-specific collation order for the fixed
parts (instead of character-by-character comparisons), and that under that
rule my proposed solution would fall down.

Time has shown that this objection is poor on four grounds:

1. LIKE does not work that way, and omitting optimizations now based on
possible future changes is a stupid approach.

2. If LIKE were to be changed that way, we can always take the
optimization out.

3. Even if LIKE were changed, certainly POSIX regexps would never be
changed, so you can still use the optimization there.

4. Nobody is seriously proposing to change LIKE that way.  And if someone
did, I would object, because the established collation rules make getting
consistent results from this approach impossible.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 13 May 2003 11:26:34 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: LIKE indexing proposal 