Re: LIKE indexing

Peter Eisentraut <peter_e@gmx.net> writes:
> Here's the patch for review.

A few gripes:

+    The optimizer can also use a B-Tree index for queries involving the
+    pattern matching operators <literal>LIKE</>,
+    <literal>ILIKE</literal>, <literal>~</literal>, and
+    <literal>~*</literal>, <emphasis>if</emphasis> the pattern is
+    anchored to the beginning of the string, e.g., <literal>col LIKE
+    'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not
+    <literal>col LIKE 'bar'</literal>.  However, if your server does

The "but not" part is wrong: col LIKE 'bar' works perfectly fine as
an indexable LIKE query.  Perhaps you meant "but not col LIKE '%foo'".

While it's okay to treat text and varchar alike, I object to treating
bpchar as equivalent to the other two.  Shouldn't the bpchar versions of
these functions strip trailing spaces before comparing?

Seems to me you should provide "$<>$" operators for completeness, even
though they're not essential for btree opclasses.  I think that these
operators may be useful for more than just this one purpose, so we
shouldn't set up artificial roadblocks.

I don't like the fact that you added expected-output rows to opr_sanity;
seems like tweaking the queries to allow $<$ etc as expected names would
be more appropriate.

            regards, tom lane