sql: support PostgreSQL operator classes

[alimi]

PostgreSQL defines operator classes to be used by default on different index types. However, it might be useful to use a different operator class on some indexes.

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).

https://www.postgresql.org/docs/9.5/indexes-opclass.html

ActiveRecord supports setting operator classes on indexes.

Jira issue: CRDB-4414

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping. We will get back to you soon. However, if we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and cc them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[awoods187]

@rafiss pointed out this came up today with https://github.com/graphile/postgraphile

[rafiss]

I was mistaken, the operator I saw from that tool actually refers to this: https://github.com/cockroachdb/cockroach/issues/53080

[timgraham]

A NotSupportedError points to this issue when Django tries to use the GIST_GEOMETRY_OPS_ND opclass on 3D geometry fields, e.g.:

CREATE INDEX "inspectapp_fields3d_poly_id" ON "inspectapp_fields3d" USING GIST ("poly" GIST_GEOMETRY_OPS_ND)

I imagine CockroachDB's indexing implementation may differ from PostGIS. Is that opclass something that could be implemented in CockroachDB or is it unnecessary? \cc @otan

[otan]

We don't support op classes, but we also don't accelerate searches in anything but 2D

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

[timgraham]

Still a valid feature request.