Two-column index efficiency

I’m wondering what is the most efficient column/index structure for the following use case:

  • I have a column (col_a) that roughly translates to the concept of shard (e.g. an int between 0-1023)

  • I have a column (col_b) that roughly translates to the concept of order (e.g. an int between 0-2^63)

If I create an index with (col_a, col_b), the query of WHERE (col_a = 1) ORDER BY col_b seems to be efficient. How can I create an index and/or additional derived column to make WHERE (col_a >= 16) AND (col_a <= 31) ORDER BY col_b efficient? (e.g. I could create partial indexes in Postgres, what’s the next best thing with CRDB?)

Hi @syntern,

There is no real alternative to partial indexing in CockroachDB right now. However, there is an open PR in GitHub for this feature: 45555. It is not planned to be merged in the next version (20.1), but is on the roadmap for a future release.

You are welcome to add your particular use case to that PR, so that the feature may handle your scenario.

Thanks for your feedback.

Regards,
Florence
Technical Support Engineer