Re: LIKE indexing

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