postgresql - 在postgres中定义索引时定义一个运算符类的目的是什么？

Question

例如

CREATE INDEX my_index_name
    ON public.my_table USING btree
    (my_column  int8_ops)
    TABLESPACE pg_default;

对比

CREATE INDEX my_index_name
    ON public.my_table USING btree
    (my_column)
    TABLESPACE pg_default;

有什么区别？

Answer 1

尽管我不喜欢逐字引用，但我认为 the manual 中写的是什么最正确、最简洁地描述了它，我怀疑我能做得更好:

The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on the type int4 would use the int4_ops class; this operator class includes comparison functions for values of type int4. In practice the default operator class for the column's data type is usually sufficient. The main reason for having operator classes is that for some data types, there could be more than one meaningful index behavior. For example, we might want to sort a complex-number data type either by absolute value or by real part. We could do this by defining two operator classes for the data type and then selecting the proper class when making an index. The operator class determines the basic sort ordering (which can then be modified by adding sort options COLLATE, ASC/DESC and/or NULLS FIRST/NULLS LAST).

---

对于您的示例，如果 my_column 被定义为 int8 类型，那么将运算符类指定为 int8_ops 是没有实际意义的，因为那样会是该类型的默认运算符。