The SHOW RANGES
statement shows information about the ranges that comprise the data for a table, index, database, or the current catalog. This information is useful for verifying how SQL data maps to underlying ranges, and where the replicas for those ranges are located.
To show range information for a specific row in a table or index, use the SHOW RANGE ... FOR ROW
statement.
Synopsis
Required privileges
To use the SHOW RANGES
statement, a user must either be a member of the admin
role (the root
user belongs to the admin
role by default) or have the ZONECONFIG
privilege defined.
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. |
opt_show_ranges_options |
The options used to configure what fields appear in the response. |
Options
The following options are available to affect the output. Multiple options can be passed at once, separated by commas.
TABLES
: List tables contained per range.INDEXES
: List indexes contained per range.DETAILS
: Add range size, leaseholder and other details. Note that this incurs a large computational overhead because it needs to fetch data across nodes.KEYS
: Include binary start and end keys.
Response
The specific fields in the response vary depending on the values passed as options. The following fields may be returned:
Field | Description | Emitted for option(s) |
---|---|---|
start_key |
The start key for the range. | Always emitted. |
end_key |
The end key for the range. | Always emitted. |
raw_start_key |
The start key for the range, displayed as a hexadecimal byte value. | KEYS |
raw_end_key |
The end key for the range, displayed as a hexadecimal byte value. | KEYS |
range_id |
The internal range ID. | Always emitted. |
voting_replicas |
The nodes that contain the range's voting replicas (that is, the replicas that participate in Raft elections). | Always emitted. |
non_voting_replicas |
The nodes that contain the range's non-voting replicas. | Always emitted. |
replicas |
The nodes that contain the range's replicas. | Always emitted. |
replica_localities |
The localities of the range's replicas. | Always emitted. |
range_size |
The size of the range in bytes. | DETAILS |
range_size_mb |
The size of the range in MiB. | DETAILS |
lease_holder |
The node that contains the range's leaseholder. | DETAILS |
lease_holder_locality |
The locality of the range's leaseholder. | DETAILS |
learner_replicas |
The learner replicas of the range. A learner replica is a replica that has just been added to a range, and is thus in an interim state. It accepts messages but doesn't vote in Raft elections. This means it doesn't affect quorum and thus doesn't affect the stability of the range, even if it's very far behind. | Always emitted. |
split_enforced_until |
The time a range split is enforced until. This can be set using ALTER TABLE ... SPLIT AT using the WITH EXPIRATION clause. Example: 2262-04-11 23:47:16.854776 (this is a default value which means "never"). |
Always emitted. |
schema_name |
The name of the schema this range holds data for. | TABLES , INDEXES |
table_name |
The name of the table this range holds data for. | TABLES , INDEXES |
table_id |
The internal ID of the table this range holds data for. | TABLES , INDEXES |
table_start_key |
The start key of the first range that holds data for this table. | TABLES |
table_end_key |
The end key of the last range that holds data for this table. | TABLES |
raw_table_start_key |
The start key of the first range that holds data for this table, expressed as BYTES . |
TABLES , KEYS |
raw_table_end_key |
The end key of the last range that holds data for this table, expressed as BYTES . |
TABLES , KEYS |
index_name |
The name of the index this range holds data for. | INDEXES |
index_id |
The internal ID of the index this range holds data for. | INDEXES |
index_start_key |
The start key of the first range of index data. | INDEXES |
index_end_key |
The end key of the last range of index data. | INDEXES |
raw_index_start_key |
The start key of the first range of index data, expressed as BYTES . |
INDEXES , KEYS |
raw_index_end_key |
The end key of the last range of index data, expressed as BYTES . |
INDEXES , KEYS |
span_stats |
A JSON object containing span statistics. For more details, see Span Statistics. |
DETAILS |
Span Statistics
CockroachDB stores all user data in a sorted map of key-value pairs, also known as a keyspace. A span refers to an interval within this keyspace.
The SHOW RANGES
command emits span statistics when the DETAILS
option is specified. The statistics are included in a column named span_stats
, as a JSON
object.
The statistics are calculated for the identifier of each row. For example:
SHOW RANGES WITH DETAILS
will compute span statistics for each range.SHOW RANGES WITH TABLES, DETAILS
will compute span statistics for each table.
The span_stats
JSON
object has the following keys:
Key | Description |
---|---|
approximate_disk_bytes |
An approximation of the total on-disk size of the given object, across all replicas (after compression). |
key_count |
The number of non-system keys, including live and deleted keys, as well as new, uncommitted keys. |
key_bytes |
The total key size (in bytes) of the keys tracked in key_count . |
val_count |
The number of values, or versions, of the keys tracked in key_count , including deletion tombstones. A key will always have at least one value, but may have several historical values. |
val_bytes |
Total value size (in bytes) of the values tracked in val_count . |
sys_count |
The number of system keys, which are not included in key_count . |
sys_bytes |
The total size (in bytes) of the keys in sys_count and all of their values, or versions. |
live_count |
The number of live (committed and non-deleted) non-system keys. |
live_bytes |
Total size (in bytes) of the keys tracked in live_count and their most recent value. |
intent_count |
The number of intents (keys with new, uncommitted values). |
intent_bytes |
The total size (in bytes) of the keys tracked in intent_count and their uncommitted values. |
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 movr
database.
$ cockroach demo --geo-partitioned-replicas
Show ranges for a database
- Show ranges for a database (without options)
- Show ranges for a database (with tables, keys, details)
- Show ranges for a database (with tables)
- Show ranges for a database (with indexes)
- Show ranges for a database (with details)
- Show ranges for a database (with keys)
SHOW DATABASES;
database_name | owner | primary_region | secondary_region | regions | survival_goal
----------------+-------+----------------+------------------+---------+----------------
defaultdb | root | NULL | NULL | {} | NULL
movr | demo | NULL | NULL | {} | NULL
postgres | root | NULL | NULL | {} | NULL
system | node | NULL | NULL | {} | NULL
(4 rows)
Show ranges for a database (without options)
SHOW RANGES FROM DATABASE movr;
start_key | end_key | range_id | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
-----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
/Table/106 | /Table/106/1/"amsterdam" | 109 | {2,4,9} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=d"} | {2,9,4} | {} | {} | NULL
/Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 178 | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL
...
/Table/111/1/"washington dc"/PrefixEnd | /Max | 284 | {1,4,8} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=c"} | {1,8,4} | {} | {} | NULL
(175 rows)
Show ranges for a database (with tables, keys, details)
SHOW RANGES FROM DATABASE movr WITH TABLES, KEYS, DETAILS;
start_key | end_key | raw_start_key | raw_end_key | range_id | schema_name | table_name | table_id | table_start_key | table_end_key | raw_table_start_key | raw_table_end_key | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until | range_size | span_stats
-----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+-------------+----------------------------+----------+----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------------------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+----------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/Table/106 | /Table/106/1/"amsterdam" | \xf2 | \xf28912616d7374657264616d0001 | 109 | public | users | 106 | /Table/106 | /Table/106/1/"amsterdam" | \xf2 | \xf28912616d7374657264616d0001 | 0 | 2 | region=us-east1,az=c | {2,4,9} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=d"} | {2,9,4} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 44299, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 1025, "sys_count": 7, "val_bytes": 0, "val_count": 0}
/Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 178 | public | users | 106 | /Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 0.00010300000000000000000 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL | 103 | {"approximate_disk_bytes": 52021, "intent_bytes": 0, "intent_count": 0, "key_bytes": 54, "key_count": 1, "live_bytes": 103, "live_count": 1, "sys_bytes": 2190, "sys_count": 7, "val_bytes": 49, "val_count": 1}
...
/Table/111/1/"washington dc"/PrefixEnd | /Max | \xf66f891277617368696e67746f6e2064630002 | \xffff | 284 | public | user_promo_codes | 111 | /Table/111/1/"washington dc"/PrefixEnd | /Table/112 | \xf66f891277617368696e67746f6e2064630002 | \xf670 | 0 | 4 | region=us-west1,az=a | {1,4,8} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=c"} | {1,8,4} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 0, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 0, "sys_count": 0, "val_bytes": 0, "val_count": 0}
(178 rows)
Show ranges for a database (with tables)
SHOW RANGES FROM DATABASE movr WITH TABLES;
start_key | end_key | range_id | schema_name | table_name | table_id | table_start_key | table_end_key | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
-----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------+-------------+----------------------------+----------+----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
/Table/106 | /Table/106/1/"amsterdam" | 109 | public | users | 106 | /Table/106 | /Table/106/1/"amsterdam" | {2,4,9} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=d"} | {2,9,4} | {} | {} | NULL
/Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 178 | public | users | 106 | /Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL
...
/Table/111/1/"washington dc"/PrefixEnd | /Max | 284 | public | user_promo_codes | 111 | /Table/111/1/"washington dc"/PrefixEnd | /Table/112 | {1,4,8} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=c"} | {1,8,4} | {} | {} | NULL
(178 rows)
Show ranges for a database (with indexes)
SHOW RANGES FROM DATABASE movr WITH INDEXES;
start_key | end_key | range_id | schema_name | table_name | table_id | index_name | index_id | index_start_key | index_end_key | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
-----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------+-------------+----------------------------+----------+-----------------------------------------------+----------+----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
/Table/106 | /Table/106/1/"amsterdam" | 109 | public | users | 106 | users_pkey | 1 | /Table/106/1 | /Table/106/1/"amsterdam" | {2,4,9} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=d"} | {2,9,4} | {} | {} | NULL
/Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 178 | public | users | 106 | users_pkey | 1 | /Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL
...
/Table/111/1/"washington dc"/PrefixEnd | /Max | 284 | public | user_promo_codes | 111 | user_promo_codes_pkey | 1 | /Table/111/1/"washington dc"/PrefixEnd | /Table/111/2 | {1,4,8} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=c"} | {1,8,4} | {} | {} | NULL
(179 rows)
Show ranges for a database (with details)
SHOW RANGES FROM DATABASE movr WITH DETAILS;
start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until | range_size | span_stats
-----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------+----------------------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+----------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/Table/106 | /Table/106/1/"amsterdam" | 109 | 0 | 2 | region=us-east1,az=c | {2,4,9} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=d"} | {2,9,4} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 42328, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 1025, "sys_count": 7, "val_bytes": 0, "val_count": 0}
/Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 178 | 0.00010300000000000000000 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL | 103 | {"approximate_disk_bytes": 48533, "intent_bytes": 0, "intent_count": 0, "key_bytes": 54, "key_count": 1, "live_bytes": 103, "live_count": 1, "sys_bytes": 2190, "sys_count": 7, "val_bytes": 49, "val_count": 1}
...
/Table/111/1/"washington dc"/PrefixEnd | /Max | 284 | 0 | 4 | region=us-west1,az=a | {1,4,8} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=c"} | {1,8,4} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 0, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 1144, "sys_count": 7, "val_bytes": 0, "val_count": 0}
(175 rows)
Show ranges for a database (with keys)
SHOW RANGES FROM DATABASE movr WITH KEYS;
start_key | end_key | raw_start_key | raw_end_key | range_id | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
-----------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
/Table/106 | /Table/106/1/"amsterdam" | \xf2 | \xf28912616d7374657264616d0001 | 109 | {2,4,9} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=d"} | {2,9,4} | {} | {} | NULL
/Table/106/1/"amsterdam" | /Table/106/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 178 | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL
...
/Table/111/1/"washington dc"/PrefixEnd | /Max | \xf66f891277617368696e67746f6e2064630002 | \xffff | 284 | {1,4,8} | {"region=us-east1,az=b","region=us-west1,az=a","region=europe-west1,az=c"} | {1,8,4} | {} | {} | NULL
(175 rows)
Show ranges for a table
- Show ranges for a table (without options)
- Show ranges for a table (with indexes, keys, details)
- Show ranges for a table (with indexes)
- Show ranges for a table (with details)
- Show ranges for a table (with keys)
SHOW TABLES;
schema_name | table_name | type | owner | estimated_row_count | locality
--------------+----------------------------+-------+-------+---------------------+-----------
public | promo_codes | table | demo | 1000 | NULL
public | rides | table | demo | 500 | NULL
public | user_promo_codes | table | demo | 5 | NULL
public | users | table | demo | 50 | NULL
public | vehicle_location_histories | table | demo | 1000 | NULL
public | vehicles | table | demo | 15 | NULL
(6 rows)
Show ranges for a table (without options)
SHOW RANGES FROM TABLE movr.users;
start_key | end_key | range_id | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+----------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
…/<TableMin> | …/1/"amsterdam" | 178 | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL
…/1/"amsterdam" | …/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 181 | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL
...
…/1/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | 94 | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL
(27 rows)
Show ranges for a table (with indexes, keys, details)
SHOW RANGES FROM TABLE movr.users with INDEXES, KEYS, DETAILS;
start_key | end_key | raw_start_key | raw_end_key | range_id | index_name | index_id | index_start_key | index_end_key | raw_index_start_key | raw_index_end_key | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until | range_size | span_stats
--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+------------+----------+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+---------------------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+----------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
…/<TableMin> | …/1/"amsterdam" | \xf2 | \xf28912616d7374657264616d0001 | 178 | users_pkey | 1 | …/1 | …/1/"amsterdam" | \xf289 | \xf28912616d7374657264616d0001 | 0 | 2 | region=us-east1,az=c | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 42077, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 0, "sys_count": 0, "val_bytes": 0, "val_count": 0}
…/1/"amsterdam" | …/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 181 | users_pkey | 1 | …/1/"amsterdam" | …/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 0.00011300000000000000000 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL | 113 | {"approximate_disk_bytes": 40626, "intent_bytes": 0, "intent_count": 0, "key_bytes": 54, "key_count": 1, "live_bytes": 113, "live_count": 1, "sys_bytes": 1698, "sys_count": 7, "val_bytes": 59, "val_count": 1}
...
…/1/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | \xf2891277617368696e67746f6e2064630002 | \xf38912616d7374657264616d0001 | 94 | users_pkey | 1 | …/1/"washington dc"/PrefixEnd | …/2 | \xf2891277617368696e67746f6e2064630002 | \xf28a | 0 | 5 | region=us-west1,az=b | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 42077, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 0, "sys_count": 0, "val_bytes": 0, "val_count": 0}
(27 rows)
Show ranges for a table (with indexes)
SHOW RANGES FROM TABLE movr.users WITH INDEXES;
start_key | end_key | range_id | index_name | index_id | index_start_key | index_end_key | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+----------+------------+----------+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
…/<TableMin> | …/1/"amsterdam" | 178 | users_pkey | 1 | …/1 | …/1/"amsterdam" | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL
…/1/"amsterdam" | …/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 181 | users_pkey | 1 | …/1/"amsterdam" | …/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL
...
…/1/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | 94 | users_pkey | 1 | …/1/"washington dc"/PrefixEnd | …/2 | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL
(27 rows)
Show ranges for a table (with details)
SHOW RANGES FROM TABLE movr.users WITH DETAILS;
start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until | range_size | span_stats
--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+----------+---------------------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+----------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
…/<TableMin> | …/1/"amsterdam" | 178 | 0 | 2 | region=us-east1,az=c | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 44716, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 919, "sys_count": 5, "val_bytes": 0, "val_count": 0}
…/1/"amsterdam" | …/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 181 | 0.00011300000000000000000 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL | 113 | {"approximate_disk_bytes": 42734, "intent_bytes": 0, "intent_count": 0, "key_bytes": 54, "key_count": 1, "live_bytes": 113, "live_count": 1, "sys_bytes": 1698, "sys_count": 7, "val_bytes": 59, "val_count": 1}
...
…/1/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | 94 | 0 | 5 | region=us-west1,az=b | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 44716, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 794, "sys_count": 5, "val_bytes": 0, "val_count": 0}
(27 rows)
Show ranges for a table (with keys)
SHOW RANGES FROM TABLE movr.users WITH KEYS;
start_key | end_key | raw_start_key | raw_end_key | range_id | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
--------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
…/<TableMin> | …/1/"amsterdam" | \xf2 | \xf28912616d7374657264616d0001 | 178 | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL
…/1/"amsterdam" | …/1/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 181 | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,9,7} | {} | {} | NULL
...
…/1/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | \xf2891277617368696e67746f6e2064630002 | \xf38912616d7374657264616d0001 | 94 | {2,5,7} | {"region=us-east1,az=c","region=us-west1,az=b","region=europe-west1,az=b"} | {2,7,5} | {} | {} | NULL
(27 rows)
Show ranges for an index
- Show ranges for an index (without options)
- Show ranges for an index (with keys, details)
- Show ranges for an index (with details)
- Show ranges for an index (with keys)
SHOW INDEXES FROM movr.users;
table_name | index_name | non_unique | seq_in_index | column_name | definition | direction | storing | implicit | visible
-------------+------------+------------+--------------+-------------+-------------+-----------+---------+----------+----------
users | users_pkey | f | 1 | city | city | ASC | f | f | t
users | users_pkey | f | 2 | id | id | ASC | f | f | t
users | users_pkey | f | 3 | name | name | N/A | t | f | t
users | users_pkey | f | 4 | address | address | N/A | t | f | t
users | users_pkey | f | 5 | credit_card | credit_card | N/A | t | f | t
(5 rows)
Show ranges for an index (without options)
SHOW RANGES FROM INDEX movr.users_pkey;
start_key | end_key | range_id | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+----------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
…/TableMin | …/"amsterdam" | 72 | {3,5,8} | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=c"} | {3,5,8} | {} | {} | NULL
…/"amsterdam" | …/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 73 | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,7,9} | {} | {} | NULL
...
…/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | 96 | {2,4,7} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=b"} | {2,4,7} | {} | {} | NULL
(27 rows)
Show ranges for an index (with keys, details)
SHOW RANGES FROM INDEX movr.users_pkey WITH KEYS, DETAILS;
start_key | end_key | raw_start_key | raw_end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until | range_size | span_stats
------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+---------------------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+----------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
…/TableMin | …/"amsterdam" | \xf2 | \xf28912616d7374657264616d0001 | 72 | 0 | 3 | region=us-east1,az=d | {3,5,8} | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=c"} | {3,5,8} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 46895, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 0, "sys_count": 0, "val_bytes": 0, "val_count": 0}
…/"amsterdam" | …/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 73 | 0.00010500000000000000000 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,7,9} | {} | {} | NULL | 105 | {"approximate_disk_bytes": 44545, "intent_bytes": 0, "intent_count": 0, "key_bytes": 54, "key_count": 1, "live_bytes": 105, "live_count": 1, "sys_bytes": 2172, "sys_count": 7, "val_bytes": 51, "val_count": 1}
...
…/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | \xf2891277617368696e67746f6e2064630002 | \xf38912616d7374657264616d0001 | 96 | 0 | 2 | region=us-east1,az=c | {2,4,7} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=b"} | {2,4,7} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 49218, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 0, "sys_count": 0, "val_bytes": 0, "val_count": 0}
(27 rows)
Show ranges for an index (with details)
SHOW RANGES FROM INDEX movr.users_pkey WITH DETAILS;
start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until | range_size | span_stats
------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+----------+---------------------------+--------------+--------------------------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+----------------------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
…/TableMin | …/"amsterdam" | 72 | 0 | 3 | region=us-east1,az=d | {3,5,8} | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=c"} | {3,5,8} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 46895, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 0, "sys_count": 0, "val_bytes": 0, "val_count": 0}
…/"amsterdam" | …/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | 73 | 0.00010500000000000000000 | 8 | region=europe-west1,az=c | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,7,9} | {} | {} | NULL | 105 | {"approximate_disk_bytes": 44545, "intent_bytes": 0, "intent_count": 0, "key_bytes": 54, "key_count": 1, "live_bytes": 105, "live_count": 1, "sys_bytes": 2172, "sys_count": 7, "val_bytes": 51, "val_count": 1}
...
…/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | 96 | 0 | 2 | region=us-east1,az=c | {2,4,7} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=b"} | {2,4,7} | {} | {} | NULL | 0 | {"approximate_disk_bytes": 49218, "intent_bytes": 0, "intent_count": 0, "key_bytes": 0, "key_count": 0, "live_bytes": 0, "live_count": 0, "sys_bytes": 0, "sys_count": 0, "val_bytes": 0, "val_count": 0}
(27 rows)
Show ranges for an index (with keys)
SHOW RANGES FROM INDEX movr.users_pkey WITH KEYS;
start_key | end_key | raw_start_key | raw_end_key | range_id | replicas | replica_localities | voting_replicas | non_voting_replicas | learner_replicas | split_enforced_until
------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+----------+------------------------------------------------------------------------------------+-----------------+---------------------+------------------+-----------------------------
…/TableMin | …/"amsterdam" | \xf2 | \xf28912616d7374657264616d0001 | 72 | {3,5,8} | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=c"} | {3,5,8} | {} | {} | NULL
…/"amsterdam" | …/"amsterdam"/"\xb333333@\x00\x80\x00\x00\x00\x00\x00\x00#" | \xf28912616d7374657264616d0001 | \xf28912616d7374657264616d000112b333333333334000ff8000ff00ff00ff00ff00ff00ff230001 | 73 | {7,8,9} | {"region=europe-west1,az=b","region=europe-west1,az=c","region=europe-west1,az=d"} | {8,7,9} | {} | {} | NULL
...
…/"washington dc"/PrefixEnd | <after:/Table/107/1/"amsterdam"> | \xf2891277617368696e67746f6e2064630002 | \xf38912616d7374657264616d0001 | 96 | {2,4,7} | {"region=us-east1,az=c","region=us-west1,az=a","region=europe-west1,az=b"} | {2,4,7} | {} | {} | NULL
(27 rows)
Video Demo
For a deep dive demo on ranges and how data is stored within them, watch the following video: