pattern matching indexing

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

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

On Mon, 2012-12-24 at 10:12 -0500, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> 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 (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

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