On this page 
Warning:
As of June 5, 2024, CockroachDB v22.2 is no longer supported. For more details, refer to the Release Support Policy.
The SHOW INDEX statement returns index information for a table or database.
Required privileges
The user must have any privilege on the target table or database.
Aliases
In CockroachDB, the following are aliases for SHOW INDEX:
SHOW INDEXESSHOW KEYS
Synopsis
Parameters
| Parameter | Description |
|---|---|
table_name |
The name of the table for which to show indexes. |
database_name |
The name of the database for which to show indexes. |
Response
The following fields are returned for each column in each index.
| Field | Description |
|---|---|
table_name |
The name of the table. |
index_name |
The name of the index. |
non_unique |
Whether values in the indexed column are unique. Possible values: true or false. |
seq_in_index |
The position of the column in the index, starting with 1. |
column_name |
The indexed column. |
direction |
How the column is sorted in the index. Possible values: ASC or DESC for indexed columns; N/A for stored columns. |
storing |
Whether the STORING clause was used to index the column during index creation. Possible values: true or false. |
implicit |
Whether the column is part of the index despite not being explicitly included during index creation. Possible values: true or falsePrimary key columns are the only columns implicitly included in secondary indexes. The inclusion of primary key columns improves performance when retrieving columns not in the index. |
visible |
Whether the index is visible to the cost-based optimizer. |
A column is in the primary key if the value of the index_name column is {tbl}_pkey and value of the storing column is false.
Example
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 to start a temporary, in-memory cluster with the movr dataset preloaded:
$ cockroach demo
Show indexes for a table
> CREATE INDEX ON users (name);
> SHOW INDEX FROM users;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+----------------+------------+--------------+-------------+-----------+---------+----------+----------
users | users_name_idx | t | 1 | name | ASC | f | f | t
users | users_name_idx | t | 2 | city | ASC | f | t | t
users | users_name_idx | t | 3 | id | ASC | f | t | t
users | users_pkey | f | 1 | city | ASC | f | f | t
users | users_pkey | f | 2 | id | ASC | f | f | t
users | users_pkey | f | 3 | name | N/A | t | f | t
users | users_pkey | f | 4 | address | N/A | t | f | t
users | users_pkey | f | 5 | credit_card | N/A | t | f | t
(8 rows)
In this example, the columns where the value of the index_name column is users_pkey and value of the storing column is false, and thus are in the primary key, are city and id.
Show indexes for a database
> SHOW INDEXES FROM DATABASE movr;
table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-----------------------------+-----------------------------------------------+------------+--------------+------------------+-----------+---------+----------+----------
promo_codes | promo_codes_pkey | f | 1 | code | ASC | f | f | t
promo_codes | promo_codes_pkey | f | 2 | description | N/A | t | f | t
promo_codes | promo_codes_pkey | f | 3 | creation_time | N/A | t | f | t
promo_codes | promo_codes_pkey | f | 4 | expiration_time | N/A | t | f | t
promo_codes | promo_codes_pkey | f | 5 | rules | N/A | t | f | t
rides | rides_auto_index_fk_city_ref_users | t | 1 | city | ASC | f | f | t
rides | rides_auto_index_fk_city_ref_users | t | 2 | rider_id | ASC | f | f | t
rides | rides_auto_index_fk_city_ref_users | t | 3 | id | ASC | f | t | t
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 1 | vehicle_city | ASC | f | f | t
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 2 | vehicle_id | ASC | f | f | t
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 3 | city | ASC | f | t | t
rides | rides_auto_index_fk_vehicle_city_ref_vehicles | t | 4 | id | ASC | f | t | t
rides | rides_pkey | f | 1 | city | ASC | f | f | t
rides | rides_pkey | f | 2 | id | ASC | f | f | t
rides | rides_pkey | f | 3 | vehicle_city | N/A | t | f | t
rides | rides_pkey | f | 4 | rider_id | N/A | t | f | t
rides | rides_pkey | f | 5 | vehicle_id | N/A | t | f | t
rides | rides_pkey | f | 6 | start_address | N/A | t | f | t
rides | rides_pkey | f | 7 | end_address | N/A | t | f | t
rides | rides_pkey | f | 8 | start_time | N/A | t | f | t
rides | rides_pkey | f | 9 | end_time | N/A | t | f | t
rides | rides_pkey | f | 10 | revenue | N/A | t | f | t
user_promo_codes | user_promo_codes_pkey | f | 1 | city | ASC | f | f | t
user_promo_codes | user_promo_codes_pkey | f | 2 | user_id | ASC | f | f | t
user_promo_codes | user_promo_codes_pkey | f | 3 | code | ASC | f | f | t
user_promo_codes | user_promo_codes_pkey | f | 4 | timestamp | N/A | t | f | t
user_promo_codes | user_promo_codes_pkey | f | 5 | usage_count | N/A | t | f | t
users | users_name_idx | t | 1 | name | ASC | f | f | t
users | users_name_idx | t | 2 | city | ASC | f | t | t
users | users_name_idx | t | 3 | id | ASC | f | t | t
users | users_pkey | f | 1 | city | ASC | f | f | t
users | users_pkey | f | 2 | id | ASC | f | f | t
users | users_pkey | f | 3 | name | N/A | t | f | t
users | users_pkey | f | 4 | address | N/A | t | f | t
users | users_pkey | f | 5 | credit_card | N/A | t | f | t
vehicle_location_histories | vehicle_location_histories_pkey | f | 1 | city | ASC | f | f | t
vehicle_location_histories | vehicle_location_histories_pkey | f | 2 | ride_id | ASC | f | f | t
vehicle_location_histories | vehicle_location_histories_pkey | f | 3 | timestamp | ASC | f | f | t
vehicle_location_histories | vehicle_location_histories_pkey | f | 4 | lat | N/A | t | f | t
vehicle_location_histories | vehicle_location_histories_pkey | f | 5 | long | N/A | t | f | t
vehicles | vehicles_auto_index_fk_city_ref_users | t | 1 | city | ASC | f | f | t
vehicles | vehicles_auto_index_fk_city_ref_users | t | 2 | owner_id | ASC | f | f | t
vehicles | vehicles_auto_index_fk_city_ref_users | t | 3 | id | ASC | f | t | t
vehicles | vehicles_pkey | f | 1 | city | ASC | f | f | t
vehicles | vehicles_pkey | f | 2 | id | ASC | f | f | t
vehicles | vehicles_pkey | f | 3 | type | N/A | t | f | t
vehicles | vehicles_pkey | f | 4 | owner_id | N/A | t | f | t
vehicles | vehicles_pkey | f | 5 | creation_time | N/A | t | f | t
vehicles | vehicles_pkey | f | 6 | status | N/A | t | f | t
vehicles | vehicles_pkey | f | 7 | current_location | N/A | t | f | t
vehicles | vehicles_pkey | f | 8 | ext | N/A | t | f | t
(51 rows)
See also
Was this helpful?
