From 7b8a63c3e922b8730e98a20a2c30c5460ddf2306 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 27 May 2008 00:13:09 +0000 Subject: Alter the xxx_pattern_ops opclasses to use the regular equality operator of the associated datatype as their equality member. This means that these opclasses can now support plain equality comparisons along with LIKE tests, thus avoiding the need for an extra index in some applications. This optimization was not possible when the pattern opclasses were first introduced, because we didn't insist that text equality meant bitwise equality; but we do now, so there is no semantic difference between regular and pattern equality operators. I removed the name_pattern_ops opclass altogether, since it's really useless: name's regular comparisons are just strcmp() and are unlikely to become something different. Instead teach indxpath.c that btree name_ops can be used for LIKE whether or not the locale is C. This might lead to a useful speedup in LIKE queries on the system catalogs in non-C locales. The ~=~ and ~<>~ operators are gone altogether. (It would have been nice to keep them for backward compatibility's sake, but since the pg_amop structure doesn't allow multiple equality operators per opclass, there's no way.) A not-immediately-obvious incompatibility is that the sort order within bpchar_pattern_ops indexes changes --- it had been identical to plain strcmp, but is now trailing-blank-insensitive. This will impact in-place upgrades, if those ever happen. Per discussions a couple months ago. --- doc/src/sgml/indices.sgml | 19 ++++++++++--------- 1 file changed, 10 insertions(+), 9 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 6fcb6968ffc..3de05419226 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1,4 +1,4 @@ - + Indexes @@ -906,11 +906,10 @@ CREATE INDEX name ON table The operator classes text_pattern_ops, - varchar_pattern_ops, - bpchar_pattern_ops, and - name_pattern_ops support B-tree indexes on - the types text, varchar, - char, and name, respectively. The + varchar_pattern_ops, and + bpchar_pattern_ops support B-tree indexes on + the types text, varchar, and + char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes @@ -923,10 +922,12 @@ CREATE INDEX name ON table CREATE INDEX test_index ON test_table (col varchar_pattern_ops); Note that you should also create an index with the default operator - class if you want queries involving ordinary comparisons to use an - index. Such queries cannot use the + class if you want queries involving ordinary <, + <=, >, or >= comparisons + to use an index. Such queries cannot use the xxx_pattern_ops - operator classes. It is allowed to create multiple + operator classes. (Ordinary equality comparisons can use these + operator classes, however.) It is allowed to create multiple indexes on the same column with different operator classes. If you do use the C locale, you do not need the xxx_pattern_ops -- cgit v1.2.3