Re: Questions about indexes with text_pattern_ops

"Kaare Rasmussen" <kaare(at)jasonic(dot)dk> 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