Re: Questions about indexes with text_pattern_ops

Gregory Stark <stark@enterprisedb.com> writes:
> Hm, for a simple = or <> I think it doesn't matter which operator class you
> use. For < or > it would produce different answers. Postgres isn't clever enough
> to notice that this is equivalent though so I think you would have to do
> something like (untested):

> CREATE INDEX new_index ON a (b text_pattern_ops) WHERE b ~<>~ '';

> That uses the same operator that the LIKE clause will use for the index range.

I'm intending to get rid of ~=~ and ~<>~ for 8.4; there's no longer any
reason why those slots in the pattern_ops classes can't be filled by the
plain = and <> operators.  (There *was* a reason when they were first
invented --- but now that texteq will only return true for exact bitwise
match, I think it's OK to assume these are equivalent.)

In the meantime, though, I think the only way that Kaare's query can use
that index is if he writesWHERE b LIKE 'whatever' AND b <> '';
(with whatever spelling of <> the index predicate has).  There is not
anything in the predicate proving machinery that knows enough about LIKE
to be able to show that "b LIKE 'whatever'" implies "b <> ''".
        regards, tom lane