SHOW RANGES

On this page Carat arrow pointing down
Warning:
CockroachDB v19.2 is no longer supported. For more details, see the Release Support Policy.

The SHOW RANGES statement shows information about the ranges that comprise the data for a table, index, or entire database. This information is useful for verifying how SQL data maps to underlying ranges, and where the replicas for ranges are located.

Note:

To show range information for a specific row in a table or index, use the SHOW RANGE ... FOR ROW statement.

Synopsis

SHOW RANGES FROM TABLE table_name INDEX table_index_name DATABASE database_name

Required privileges

Only members of the admin role can run SHOW RANGES. By default, the root user belongs to the admin role.

Parameters

Parameter Description
table_name The name of the table you want range information about.
table_index_name The name of the index you want range information about.
database_name The name of the database you want range information about.

Response

The following fields are returned for each partition:

Field Description
table_name The name of the table.
start_key The start key for the range.
end_key The end key for the range.
range_id The range ID.
range_size_mb The size of the range.
lease_holder The node that contains the range's leaseholder.
lease_holder_locality The locality of the leaseholder.
replicas The nodes that contain the range replicas.
replica_localities The locality of the range.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo with the --geo-partitioned-replicas flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the Geo-Partitioned Replicas Topology applied to the movr database.

icon/buttons/copy
$ cockroach demo --geo-partitioned-replicas

Show ranges for a table (primary index)

icon/buttons/copy
> SELECT * FROM [SHOW RANGES FROM TABLE vehicles] WHERE "start_key" NOT LIKE '%Prefix%';
     start_key     |          end_key           | range_id | range_size_mb | lease_holder |  lease_holder_locality   | replicas |                                 replica_localities
+------------------+----------------------------+----------+---------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+
  /"new york"      | /"new york"/PrefixEnd      |       58 |      0.000304 |            2 | region=us-east1,az=c     | {1,2,5}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-west1,az=b"}
  /"washington dc" | /"washington dc"/PrefixEnd |      102 |      0.000173 |            2 | region=us-east1,az=c     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"boston"        | /"boston"/PrefixEnd        |       63 |      0.000288 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"seattle"       | /"seattle"/PrefixEnd       |       97 |      0.000295 |            4 | region=us-west1,az=a     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"los angeles"   | /"los angeles"/PrefixEnd   |       55 |      0.000156 |            5 | region=us-west1,az=b     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"san francisco" | /"san francisco"/PrefixEnd |       71 |      0.000309 |            6 | region=us-west1,az=c     | {1,5,6}  | {"region=us-east1,az=b","region=us-west1,az=b","region=us-west1,az=c"}
  /"amsterdam"     | /"amsterdam"/PrefixEnd     |       59 |      0.000305 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"paris"         | /"paris"/PrefixEnd         |       62 |      0.000299 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"rome"          | /"rome"/PrefixEnd          |       67 |      0.000168 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
(9 rows)

Show ranges for an index

icon/buttons/copy
> SELECT * FROM [SHOW RANGES FROM INDEX vehicles_auto_index_fk_city_ref_users] WHERE "start_key" NOT LIKE '%Prefix%';
     start_key     |          end_key           | range_id | range_size_mb | lease_holder |  lease_holder_locality   | replicas |                                 replica_localities
+------------------+----------------------------+----------+---------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+
  /"washington dc" | /"washington dc"/PrefixEnd |      188 |      0.000089 |            2 | region=us-east1,az=c     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"boston"        | /"boston"/PrefixEnd        |      141 |      0.000164 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"new york"      | /"new york"/PrefixEnd      |      168 |      0.000174 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  /"los angeles"   | /"los angeles"/PrefixEnd   |      165 |      0.000087 |            6 | region=us-west1,az=c     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"san francisco" | /"san francisco"/PrefixEnd |      174 |      0.000183 |            6 | region=us-west1,az=c     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"seattle"       | /"seattle"/PrefixEnd       |      186 |      0.000166 |            6 | region=us-west1,az=c     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  /"amsterdam"     | /"amsterdam"/PrefixEnd     |      137 |       0.00017 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"paris"         | /"paris"/PrefixEnd         |      170 |      0.000162 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  /"rome"          | /"rome"/PrefixEnd          |      172 |       0.00008 |            9 | region=europe-west1,az=d | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
(9 rows)

Show ranges for a database

icon/buttons/copy
> SELECT * FROM [SHOW RANGES FROM database movr] WHERE "start_key" NOT LIKE '%Prefix%';
          table_name         |    start_key     |          end_key           | range_id | range_size_mb | lease_holder |  lease_holder_locality   | replicas |                                 replica_localities
+----------------------------+------------------+----------------------------+----------+---------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+
  users                      | /"amsterdam"     | /"amsterdam"/PrefixEnd     |       47 |      0.000562 |            7 | region=europe-west1,az=b | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}
  users                      | /"boston"        | /"boston"/PrefixEnd        |       51 |      0.000665 |            3 | region=us-east1,az=d     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  users                      | /"chicago"       | /"los angeles"             |       83 |             0 |            4 | region=us-west1,az=a     | {2,4,8}  | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=c"}
  users                      | /"los angeles"   | /"los angeles"/PrefixEnd   |       45 |      0.000697 |            4 | region=us-west1,az=a     | {4,5,6}  | {"region=us-west1,az=a","region=us-west1,az=b","region=us-west1,az=c"}
  users                      | /"new york"      | /"new york"/PrefixEnd      |       48 |      0.000664 |            1 | region=us-east1,az=b     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
  users                      | /"paris"         | /"paris"/PrefixEnd         |       52 |      0.000628 |            8 | region=europe-west1,az=c | {7,8,9}  | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"}

  ...

  user_promo_codes           | /"washington dc" | /"washington dc"/PrefixEnd |      144 |             0 |            2 | region=us-east1,az=c     | {1,2,3}  | {"region=us-east1,az=b","region=us-east1,az=c","region=us-east1,az=d"}
(73 rows)

See also


Yes No
On this page

Yes No