"Tom Lane" <[EMAIL PROTECTED]> writes: > 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.)
The only question is whether we'll keep that forever. I thought it was a good idea at the time but I'm starting to wonder about the implications for multi-key indexes. > 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 <> ''". I was thinking that the inequalities that the LIKE index scan introduces would imply the inequality. I take it we generate those inequalities too late in the planning process to use them for other planning? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org