On Tue, 26 Jul 2005, Joseph Shraibman wrote: > > > Stephan Szabo wrote: > > On Tue, 26 Jul 2005, Joseph Shraibman wrote: > > > > > >>I have this index: > >> > >>"directory_lower_username_seg_key" unique, btree (lower(username) > >>text_pattern_ops, seg) > >> > >>... but my query refuses to use that index. > > > > > > text_pattern_ops is an opclass for doing LIKE queries using the index, I > > don't believe it's used for equality comparisons. > > Seems like a bug to me. That operator can't do the operations equal, > less than, and greater than? It is for the operators ~<~, ~<=~, ~=~, ~>=~, ~>~ (for like optimization). The docs seem to say that it does a character by character comparison rather than one using the collation thus being better for pattern matching. I'd think letting it do <, <=, =, >=, > would have it giving the wrong results for such queries (well, in non-C locales). ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
