From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
cc: pgsql-docs@postgresql.org
Subject: Re: pattern matching indexing
In-reply-to: <1356615259.13340.4.camel@vanquo.pezone.net>
References: <1356360300.5854.2.camel@vanquo.pezone.net>
	<4603.1356361965@sss.pgh.pa.us>
	<1356615259.13340.4.camel@vanquo.pezone.net>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
	message dated "Thu, 27 Dec 2012 08:34:19 -0500"
Date: Thu, 27 Dec 2012 11:39:30 -0500
Message-ID: <14129.1356626370@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

Peter Eisentraut <peter_e@gmx.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


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs