Re: [DOCS] pattern matching indexing

2012-12-27 Thread Peter Eisentraut 
On Mon, 2012-12-24 at 10:12 -0500, Tom Lane wrote:
> Peter Eisentraut  writes:
> > To index pattern matching, you can use the _pattern_ops operator
> > classes, or you can set the column collation in the index to "C".  The
> > latter option doesn't appear to be documented (unless you read very deep
> > between the lines).  Is that intentional?
> 
> 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");

I'm also wondering whether the latter wouldn't be a preferable
recommendation going forward.  I suppose it's also a matter of taste,
but such an index can also be used for other things (e.g. ORDER BY col
COLLATE "C"), and it uses less obscure and magic functionality.



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

Re: [DOCS] pattern matching indexing

2012-12-27 Thread Tom Lane 
Peter Eisentraut  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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs