Re: Questions about indexes with text_pattern_ops

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Kaare Rasmussen" <kaare(at)jasonic(dot)dk>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Questions about indexes with text_pattern_ops
Date: 2008-02-25 15:00:37
Message-ID: 87r6f13wtm.fsf@oxford.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David BOURIAUD 2008-02-25 15:33:50 Re: One more option for pg_dump...
Previous Message Bernd Helmle 2008-02-25 14:46:41 Strange behavior with leap dates and centuries BC