Cockroach: sql: support more index-type combinations

[knz]

The following combinations are not supported:

| Type | Regular index | Inverted index |
|--------|---------------|----------------|
| JSON | maybe #35706 | already supported |
| VARCHAR | already supported | not supported
| other types | already supported | (no issue yet) |

This is a meta issue filed for tracking and telemetry purposes.

To users who are inconvenience: please leave comments with an explanation of your use case.

[davidsbond]

Hello, my team are using CockroachDB in production and are finding it great so far. Our current use-case for wanting to index array fields is fairly simple. We have a store of personal information where each row has a TEXT[] type for storing phone numbers.

When we add new people to this table we want to check in advance if one of their phone numbers is already in use by someone else. So we have a query like:

SELECT id FROM person WHERE $1::TEXT = ANY(phone_numbers)

Without indexing it takes around 3 seconds per phone number we search where there are ~450k rows. In some instances, someone could be added who has many phone numbers, or many people who have many phone numbers, which we would individually check, leading to a linear increase in query time.

Perhaps we are approaching this problem incorrectly, but I suspect being able to index this field would yield faster queries.

[knz]

cc @awoods187 @andy-kimball for triage

[mkharibalaji]

Hi Team,
We are using it for tagging/labelling purpose inorder to send emails/push notifications for only those whoare tagged with a particular group and in our usecase it's primarily Customer segmentation for sending messages.Now the data has grown large ,we are really in need of an index on the simple array [INT/STRING] asap.

[andy-kimball]

cc @jordanlewis

[jordanlewis]

The two requests in this thread are for inverted indexes on arrays. Filed #43199 to track this.

[mzimmerman]

We use an internal unique id for a user. For transactions in the system can affect multiple users, so each transaction logged may have multiple users affected and we look which ones the transaction is applied to. (Usually less than 10). We have millions of users and billions of transactions to log. Not bring able to index on an array has kept me from trying cockroach.

[jordanlewis]

Arrays are now indexable with inverted indexes, and will be indexable with forward indexes in 20.2.

[awoods187]

I ran into this today for VARCHAR and updated the issue at the top to track

[jdabrowski]

Hi,

Arrays are now indexable with inverted indexes, and will be indexable with forward indexes in 20.2.

Is it still planned for 20.2? I'm getting

SQL State : 0A000 Error Code : 0 Message : ERROR: unimplemented: column verified_mobiles is of type varchar[] and thus is not indexable Hint: You have attempted to use a feature that is not yet implemented. See: https://go.crdb.dev/issue-v/35730/v20.2 Location : migrations/postgres/user/V1__user.sql (/file:/app.jar!/migrations/postgres/user/V1__user.sql) Line : 91 Statement : CREATE INDEX IF NOT EXISTS users_verified_mobiles_equals ON users(verified_mobiles)

against cockroachdb/cockroach-unstable:v20.2.0-rc.1

[RaduBerinde]

@jordanlewis I think you know most about inverted indexes on arrays, can you take a look at the question above>

[jordanlewis]

@RaduBerinde we do support inverted indexes on arrays.

@jdabrowski correctly points out that we do not support forward indexes on arrays, despite what I said above. We ran into some issues that prevented us from getting them into 20.2 after all.

See: #50662 #50656 #50659 #17154