public inbox for [email protected]help / color / mirror / Atom feed
pattern matching indexing 4+ messages / 2 participants [nested] [flat]
* pattern matching indexing @ 2012-12-24 14:45 Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Peter Eisentraut @ 2012-12-24 14:45 UTC (permalink / raw) To: pgsql-docs 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? Also, there is no link from http://www.postgresql.org/docs/devel/static/functions-matching.html to either option. -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pattern matching indexing @ 2012-12-24 15:12 Tom Lane <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2012-12-24 15:12 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-docs Peter Eisentraut <[email protected]> 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pattern matching indexing @ 2012-12-27 13:34 Peter Eisentraut <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Peter Eisentraut @ 2012-12-27 13:34 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs On Mon, 2012-12-24 at 10:12 -0500, Tom Lane wrote: > Peter Eisentraut <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pattern matching indexing @ 2012-12-27 16:39 Tom Lane <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Tom Lane @ 2012-12-27 16:39 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-docs Peter Eisentraut <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-12-27 16:39 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-12-24 14:45 pattern matching indexing Peter Eisentraut <[email protected]> 2012-12-24 15:12 ` Tom Lane <[email protected]> 2012-12-27 13:34 ` Peter Eisentraut <[email protected]> 2012-12-27 16:39 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox