Gregory Stark <[EMAIL PROTECTED]> 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 writes WHERE 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq