Re: pattern matching indexing

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On Mon, 2012-12-24 at 10:12 -0500, Tom Lane wrote:
>> It is stated at
>> http://www.postgresql.org/docs/devel/static/indexes-opclass.html
>> that you don't need the special pattern opclasses in C locale.
>> Feel free to rephrase or document elsewhere if you find that too
>> obscure.

> What it doesn't make very clear is that you can also override the locale
> in the index definition itself. So instead of the recommended

> CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

> you can write nearly equivalently

> CREATE INDEX test_index ON test_table (col COLLATE "C");

Ah, now I see what you're getting at. That's not equivalent though:
IIRC, an index defined in that way will not match to a plain old
WHERE col = 'constant' query, unless the prevailing locale is C anyway.
The pattern_ops index will match, because varchar_pattern_ops and
regular varchar_ops share the same equality operator.

> I'm also wondering whether the latter wouldn't be a preferable
> recommendation going forward.

Because of the above, it is most definitely not a preferable
recommendation. I don't mind if it's documented more explicitly, but
the pattern_ops approach is the one to recommend in most cases,
I believe.

regards, tom lane