"Kaare Rasmussen" <[EMAIL PROTECTED]> writes: > Hi > > The database is initialized with utf8, so in order for LIKE to use the index > on > a text field, I used text_pattern_ops when I created it. So far so good. > > It's in the documentation, but there's no explanation of why this index will > only work for LIKE searches. How come that I have to have two different > indexes > if I want to give Postgres the ability to choose index scan over seq scan on > LIKE and non-LIKE searches?
Because in non-C locales (which you're almost certainly using if you're using UTF8) the ordering which the normal text operations use can be quite complex. Just as an example most locales have spaces being entirely insignificant. So no range can reliably match a prefix LIKE pattern. The text_pattern_ops use simple character-by-character ordering which are useful for LIKE but not for regular < and > comparisons. They're just two different orderings. > Also, when I tried to create the index as a partial one (avoiding the 95% > entries with empty strings), Postgresql chooses to use seq scan. This sounds > counter intuitive to me. > > CREATE INDEX new_index ON a (b text_pattern_ops) WHERE b <> ''; > This is 8.2.6. 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. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
