Need to update to a newer CRDB version to support S2Cell index queries

[jctrouble]

There is some high latency (700+ms) when querying SCD subscriptions for updates.

This query (source):

SELECT
  id
FROM scd_subscriptions
WHERE
  cells && ARRAY[7698420173010632704,7698420138650894336,7698420104291155968]
FOR UPDATE

triggers a full table scan (analysis bundle):

root@:26257/defaultdb> EXPLAIN SELECT id FROM scd.scd_subscriptions WHERE cells && ARRAY[7698420173010632704,7698420138650894336,7698420104291155968] FOR UPDATE;
                                          info
-----------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • filter
  │ estimated row count: 4,450
  │ filter: cells && ARRAY[7698420173010632704,7698420138650894336,7698420104291155968]
  │
  └── • scan
        estimated row count: 13,351 (100% of the table; stats collected 1 hour ago)
        table: scd_subscriptions@primary
        spans: FULL SCAN
        locking strength: for update
(12 rows)


Time: 2ms total (execution 1ms / network 0ms)

Index support for the && operator wasn't added until Apr 9, 2022, but the current deployed CRDB version (v21.2.7) is from March 14, 2022.

In order to effectively perform these queries and use the inverted index scd_subscriptions@cell_idx, we need to update the CRDB version.

[jctrouble]

Lots of contention too:

[barroco]

Thank you @jctrouble for opening this issue and the detailed analysis. The fix you referenced has been released as part of version 22.2.0. We are planning to upgrade to 24.1.3 shortly.

[barroco]

Upgrade of CockroachDB to 24.1.3 addressed in #1075 and released as part of v0.17.0-rc1. See MIGRATION.md for upgrade instructions. ~Please feel free to reopen this issue if you think additional work is required.~ @jctrouble May I kindly ask you to confirm that the upgrade solves the issue ?

[BenjaminPelletier]

I believe this is resolved; marking closed in absence of response.