ALTER INDEX - CockroachDB

The ALTER INDEX statement applies a schema change to an index.

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Required privileges

Refer to the respective subcommands.

Synopsis

Parameters

Parameter	Description
`index\_name`	The name of the index you want to change.
`IF EXISTS`	Alter the index only if an index `table\_index\_name` exists; if one does not exist, do not return an error.

Additional parameters are documented for the respective subcommands.

Subcommands

Subcommand	Description
`CONFIGURE ZONE`	Replication Controls for an index.
`PARTITION BY`	Partition, re-partition, or un-partition an index.
`RENAME TO`	Change the name of an index.
`SCATTER`	Make a best-effort attempt to redistribute replicas and leaseholders for the ranges of a table or index. Note that this statement does not return an error even if replicas are not moved.
`SPLIT AT`	Force a range split at the specified row in the index.
`UNSPLIT AT`	Remove a range split enforcement in the index.
`VISIBILITY`	Set the visibility of an index between a range of `0.0` and `1.0`.
`[NOT] VISIBLE`	Make an index visible or not visible to the cost-based optimizer.

`CONFIGURE ZONE`

ALTER INDEX... CONFIGURE ZONE is used to add, modify, reset, or remove replication zones for an index. To view details about existing replication zones, use SHOW ZONE CONFIGURATIONS. For more information about replication zones, see Replication Controls. You can use replication zones to control the number and location of replicas for specific sets of data, both when replicas are first added and when they are rebalanced to maintain cluster equilibrium. For examples, see Replication Controls. For instructions showing how to troubleshoot replication zones that may be misconfigured, see Troubleshoot Replication Zone Configurations.

Required privileges

The user must be a member of the admin role or have been granted CREATE or ZONECONFIG privileges. To configure system objects, the user must be a member of the admin role.

Parameters

Parameter	Description
`variable`	The name of the replication zone variable to change.
`value`	The value of the replication zone variable to change.
`DISCARD`	Remove a replication zone.

For usage, see Synopsis.

`PARTITION BY`

ALTER INDEX... PARTITION BY is used to partition, re-partition, or un-partition a secondary index. After defining partitions, CONFIGURE ZONE is used to control the replication and placement of partitions. Similar to indexes, partitions can improve query performance by limiting the numbers of rows that a query must scan. In the case of geo-partitioned data, partitioning can limit a query scan to data in a specific region. For examples, see Query partitions.

Most users should not need to use partitioning directly. Instead, they should use CockroachDB’s built-in multi-region capabilities, which automatically handle geo-partitioning and other low-level details.

The primary key required for partitioning is different from the conventional primary key: The unique identifier in the primary key must be prefixed with all columns you want to partition and subpartition the table on, in the order in which you want to nest your subpartitions. If the primary key in your existing table does not meet the requirements, you can change the primary key with ALTER TABLE... ALTER PRIMARY KEY. For examples, see Define partitions.

Required privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter	Description
`name\_list`	List of columns you want to define partitions on (in the order they are defined in the primary key).
`list\_partitions`	Name of list partition followed by the list of values to be included in the partition.
`range\_partitions`	Name of range partition followed by the range of values to be included in the partition.

For usage, see Synopsis.

`RENAME TO`

ALTER INDEX... RENAME TO changes the name of an index.

It is not possible to rename an index referenced by a view. For more details, see View Dependencies.

For examples, see Rename indexes.

Required privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter	Description
`index\_new\_name`	The `name` you want to use for the index, which must be unique to its table and follow these identifier rules.

For usage, see Synopsis.

`SCATTER`

ALTER INDEX... SCATTER runs a specified set of ranges for a table or index through the replication layer queue. If many ranges have been created recently, the replication queue may transfer some leases to other replicas to balance load across the cluster. Note that this statement makes a best-effort attempt to redistribute replicas and leaseholders for the ranges of an index. It does not return an error even if replicas are not moved.

SCATTER has the potential to result in data movement proportional to the size of the table or index being scattered, thus taking additional time and resources to complete.

For examples, refer to Scatter indexes.

Required privileges

The user must have the INSERT privilege on the table or index.

Parameters

Parameter	Description
`table\_name`	The name of the table that you want to scatter.
`table\_index\_name`	The name of the index that you want to scatter.
`expr\_list`	A list of scalar expressions in the form of the primary key of the table or the specified index.

For usage, see Synopsis.

`SPLIT AT`

ALTER INDEX... SPLIT AT forces a range split at a specified row in the index. CockroachDB breaks data into ranges. By default, CockroachDB attempts to keep ranges below the default range size. To do this, the system will automatically split a range if it grows larger than this limit. For most use cases, this automatic range splitting is sufficient, and you should never need to worry about when or where the system decides to split ranges. However, there are reasons why you may want to perform manual splits on the ranges that store tables or indexes:

When a table only consists of a single range, all writes and reads to the table will be served by that range’s leaseholder. If a table only holds a small amount of data but is serving a large amount of traffic, load distribution can become unbalanced and a hot spot can occur. Splitting the table’s ranges manually can allow the load on the table to be more evenly distributed across multiple nodes. For tables consisting of more than a few ranges, load will naturally be distributed across multiple nodes and this will not be a concern.
When a table is created, it will only consist of a single range. If you know that a new table will immediately receive significant write traffic, you may want to preemptively split the table based on the expected distribution of writes before applying the load. This can help avoid reduced workload performance that results when automatic splits are unable to keep up with write traffic and a hot spot occurs.

For examples, see Split and unsplit indexes.

Required privileges

The user must have the INSERT privilege on the table or index.

Parameters

Parameter	Description
`select\_stmt`	A selection query that produces one or more rows at which to split the index.
`a\_expr`	The expiration of the split enforcement on the index. This can be a `DECIMAL`, `INTERVAL`, `TIMESTAMP`, or `TIMESTAMPZ`.

For usage, see Synopsis.

`UNSPLIT AT`

ALTER INDEX... UNSPLIT AT removes a split enforcement on a range split, at a specified row in the index. Removing a split enforcement from a table or index (“unsplitting”) allows CockroachDB to merge ranges as needed, to help improve your cluster’s performance. For more information, see Range Merges. For examples, see Split and unsplit indexes.

Required privileges

The user must have the INSERT privilege on the table or index.

Parameters

Parameter	Description
`select\_stmt`	A selection query that produces one or more rows at which to unsplit an index.
`ALL`	Remove all split enforcements for an index.

For usage, see Synopsis.

`VISIBILITY`

ALTER INDEX... VISIBILITY specifies the visibility of an index between a range of 0.0 and 1.0.

VISIBILITY 0.0 means that an index is not visible to the cost-based optimizer. This is equivalent to NOT VISIBLE.
VISIBILITY 1.0 means that an index is visible to the optimizer. This is equivalent to VISIBLE.
Any value between 0.0 and 1.0 means that an index is visible to the specified fraction of queries. This is known as a partially visible index.
For the purposes of index recommendations, partially visible indexes are treated as not visible. If a partially visible index can be used to improve a query plan, the optimizer will recommend making it fully visible. For an example, refer to Set an index as partially visible.

`[NOT] VISIBLE`

ALTER INDEX... VISIBLE and ALTER INDEX... NOT VISIBLE determines whether the index is visible to the cost-based optimizer. By default, indexes are visible. If an index is NOT VISIBLE, queries will not read from the index unless it is specifically selected with an index hint or the property is overridden with the optimizer_use_not_visible_indexes session variable. In order to keep NOT VISIBLE indexes up to date, queries will still write to the index as they insert and update data in the table. This allows you to create an index and check for query plan changes without affecting production queries. For an example, refer to Set an index to be not visible. Note the following considerations for index visibility:

Primary indexes must be visible.
Queries may still read from NOT VISIBLE, UNIQUE indexes to enforce UNIQUE constraints.
Queries may still read from NOT VISIBLE indexes to perform foreign key cascades and enforce FOREIGN KEY constraints.
When defining a UNIQUE constraint, you cannot use the NOT VISIBLE syntax to make the corresponding index not visible. Instead, use ALTER INDEX... NOT VISIBLE after creating the UNIQUE constraint.

For examples, refer to Set index visibility.

Aliases

In CockroachDB, the following are aliases for NOT VISIBLE:

INVISIBLE

Examples

Configure replication zones

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

Create a replication zone for an index

The Cost-based Optimizer can take advantage of replication zones for secondary indexes when optimizing queries.

The secondary indexes on a table will automatically use the replication zone for the table. You can also add distinct replication zones for secondary indexes. To control replication for a specific secondary index, use the ALTER INDEX... CONFIGURE ZONE statement to define the relevant values (other values will be inherited from the parent zone).

To get the name of a secondary index, which you need for the CONFIGURE ZONE statement, use the SHOW INDEX or SHOW CREATE TABLE statements.

> ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;

CONFIGURE ZONE 1

> SHOW ZONE CONFIGURATION FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users;

                         target                        |                                 raw_config_sql
+------------------------------------------------------+---------------------------------------------------------------------------------+
  INDEX vehicles@vehicles_auto_index_fk_city_ref_users | ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING

                                                       |     range_min_bytes = 134217728,
                                                       |     range_max_bytes = 536870912,
                                                       |     gc.ttlseconds = 100000,
                                                       |     num_replicas = 5,
                                                       |     constraints = '[]',
                                                       |     lease_preferences = '[]'
(1 row)

Edit a replication zone

ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING range_min_bytes = 0, range_max_bytes = 90000, gc.ttlseconds = 89999, num_replicas = 4;

Remove a replication zone

When you discard a zone configuration, the objects it was applied to will then inherit a configuration from an object “the next level up”; e.g., if the object whose configuration is being discarded is a table, it will use its parent database’s configuration.You cannot DISCARD any zone configurations on multi-region tables, indexes, or partitions if the multi-region abstractions created the zone configuration.

ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE DISCARD;

Troubleshoot replication zones

For instructions showing how to troubleshoot replication zones that may be misconfigured, see Troubleshoot Replication Zone Configurations.

Define partitions

Define a list partition on an index

Suppose we have a table called students_by_list, a secondary index on the table called name_idx, and the primary key of the table is defined as (country, id). We can define partitions on the index by list:

ALTER INDEX students_by_list@name_idx PARTITION BY LIST (country) (
    PARTITION north_america VALUES IN ('CA','US'),
    PARTITION australia VALUES IN ('AU','NZ'),
    PARTITION DEFAULT VALUES IN (default)
  );

Define a range partition on an index

Suppose we have a table called students_by_range, with a secondary index called name_idx, and the primary key of the table is defined as (expected_graduation_date, id). We can define partitions on the index by range:

ALTER INDEX students_by_range@name_idx PARTITION BY RANGE (expected_graduation_date) (
    PARTITION graduated VALUES FROM (MINVALUE) TO ('2017-08-15'),
    PARTITION current VALUES FROM ('2017-08-15') TO (MAXVALUE)
  );

Define subpartitions on an index

Suppose we have a table named students, with a secondary index called name_idx, and the primary key is defined as (country, expected_graduation_date, id). We can define partitions and subpartitions on the index:

ALTER INDEX students@name_idx PARTITION BY LIST (country) (
    PARTITION australia VALUES IN ('AU','NZ') PARTITION BY RANGE (expected_graduation_date) (
      PARTITION graduated_au VALUES FROM (MINVALUE) TO ('2017-08-15'),
      PARTITION current_au VALUES FROM ('2017-08-15') TO (MAXVALUE)
    ),
    PARTITION north_america VALUES IN ('US','CA') PARTITION BY RANGE (expected_graduation_date) (
      PARTITION graduated_us VALUES FROM (MINVALUE) TO ('2017-08-15'),
      PARTITION current_us VALUES FROM ('2017-08-15') TO (MAXVALUE)
    )
  );

Repartition an index

ALTER INDEX students_by_range@name_idx PARTITION BY RANGE (expected_graduation_date) (
    PARTITION graduated VALUES FROM (MINVALUE) TO ('2018-08-15'),
    PARTITION current VALUES FROM ('2018-08-15') TO (MAXVALUE)
  );

Unpartition an index

ALTER INDEX students@name_idx PARTITION BY NOTHING;

Rename indexes

Rename an index

CREATE INDEX on users(name);

SHOW INDEXES FROM users;

  table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+------------+------------+--------------+-------------+-----------+---------+----------+----------
  users      | name_idx   |     t      |            1 | name        | DESC      |    f    |    f     |    t
  users      | name_idx   |     t      |            2 | city        | ASC       |    f    |    t     |    t
  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)

ALTER INDEX users@name_idx RENAME TO users_name_idx;

SHOW INDEXES FROM users;

  table_name |   index_name   | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+----------------+------------+--------------+-------------+-----------+---------+----------+----------
  users      | users_name_idx |     t      |            1 | name        | DESC      |    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)

Scatter indexes

Before scattering, you can view the current replica and leaseholder distribution for an index:

WITH range_details AS (SHOW RANGES FROM index rides@rides_pkey WITH DETAILS) SELECT range_id, lease_holder, replicas from range_details;

  range_id | lease_holder | replicas
-----------+--------------+-----------
       135 |            9 | {2,6,9}
       123 |            6 | {2,6,9}
       122 |            9 | {2,6,9}
       120 |            9 | {3,6,9}
       121 |            9 | {3,6,9}
       119 |            6 | {2,6,9}
        93 |            6 | {1,6,9}
        91 |            2 | {2,6,9}
        92 |            6 | {2,6,8}
(9 rows)

ALTER INDEX rides@rides_pkey SCATTER;

After scattering, recheck the leaseholder distribution:

WITH range_details AS (SHOW RANGES FROM index rides@rides_pkey WITH DETAILS) SELECT range_id, lease_holder, replicas from range_details;

  range_id | lease_holder | replicas
-----------+--------------+-----------
       135 |            9 | {1,6,9}
       123 |            5 | {2,5,9}
       122 |            5 | {2,5,9}
       120 |            6 | {3,6,9}
       121 |            3 | {3,6,9}
       119 |            5 | {3,5,9}
        93 |            5 | {1,5,9}
        91 |            1 | {1,5,9}
        92 |            5 | {2,5,8}
(9 rows)

Split and unsplit indexes

Setup

$ cockroach demo --geo-partitioned-replicas

Split an index

Add a new secondary index to the rides table, on the revenue column:

CREATE INDEX revenue_idx ON rides(revenue);

Then split the table ranges by secondary index values:

ALTER INDEX rides@revenue_idx SPLIT AT VALUES (25.00), (50.00), (75.00);

         key        | pretty |        split_enforced_until
--------------------+--------+--------------------------------------
  \277\214*2\000    | /25    | 2262-04-11 23:47:16.854776+00:00:00
  \277\214*d\000    | /5E+1  | 2262-04-11 23:47:16.854776+00:00:00
  \277\214*\226\000 | /75    | 2262-04-11 23:47:16.854776+00:00:00
(3 rows)

SHOW RANGES FROM INDEX rides@revenue_idx;

  start_key | end_key | range_id | range_size_mb | lease_holder | lease_holder_locality | replicas |                             replica_localities
------------+---------+----------+---------------+--------------+-----------------------+----------+-----------------------------------------------------------------------------
  NULL      | /25     |      249 |      0.007464 |            3 | region=us-east1,az=d  | {3,5,7}  | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=b"}
  /25       | /5E+1   |      250 |      0.008995 |            3 | region=us-east1,az=d  | {3,5,7}  | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=b"}
  /5E+1     | /75     |      251 |      0.008212 |            3 | region=us-east1,az=d  | {3,5,7}  | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=b"}
  /75       | NULL    |      252 |      0.009267 |            3 | region=us-east1,az=d  | {3,5,7}  | {"region=us-east1,az=d","region=us-west1,az=b","region=europe-west1,az=b"}
(4 rows)

Set the expiration on a split enforcement

For an example, see ALTER TABLE.

Unsplit an index

Add a new secondary index to the rides table, on the revenue column, and then split the table ranges by secondary index values as described in Split an index. To remove the split enforcements, run the following:

ALTER INDEX rides@revenue_idx UNSPLIT AT VALUES (25.00), (50.00), (75.00);

         key        |      pretty
--------------------+-------------------
  \277\214*2\000    | /Table/55/4/25
  \277\214*d\000    | /Table/55/4/5E+1
  \277\214*\226\000 | /Table/55/4/75
(3 rows)

You can see the split’s expiration date in the split_enforced_until column. The crdb_internal.ranges table also contains information about ranges in your CockroachDB cluster, including the split_enforced_until column.

SELECT range_id, start_pretty, end_pretty, split_enforced_until FROM crdb_internal.ranges WHERE table_name='rides';

  range_id |                                        start_pretty                                         |                                         end_pretty                                          |        split_enforced_until
-----------+---------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+--------------------------------------
        39 | /Table/55                                                                                   | /Table/55/1/"amsterdam"/"\xc5\x1e\xb8Q\xeb\x85@\x00\x80\x00\x00\x00\x00\x00\x01\x81"        | NULL
        56 | /Table/55/1/"amsterdam"/"\xc5\x1e\xb8Q\xeb\x85@\x00\x80\x00\x00\x00\x00\x00\x01\x81"        | /Table/55/1/"boston"/"8Q\xeb\x85\x1e\xb8B\x00\x80\x00\x00\x00\x00\x00\x00n"                 | 2262-04-11 23:47:16.854776+00:00:00
        55 | /Table/55/1/"boston"/"8Q\xeb\x85\x1e\xb8B\x00\x80\x00\x00\x00\x00\x00\x00n"                 | /Table/55/1/"los angeles"/"\xa8\xf5\u008f\\(H\x00\x80\x00\x00\x00\x00\x00\x01J"             | 2262-04-11 23:47:16.854776+00:00:00
        53 | /Table/55/1/"los angeles"/"\xa8\xf5\u008f\\(H\x00\x80\x00\x00\x00\x00\x00\x01J"             | /Table/55/1/"new york"/"\x1c(\xf5\u008f\\I\x00\x80\x00\x00\x00\x00\x00\x007"                | 2262-04-11 23:47:16.854776+00:00:00
        66 | /Table/55/1/"new york"/"\x1c(\xf5\u008f\\I\x00\x80\x00\x00\x00\x00\x00\x007"                | /Table/55/1/"paris"/"\xe1G\xae\x14z\xe1H\x00\x80\x00\x00\x00\x00\x00\x01\xb8"               | 2262-04-11 23:47:16.854776+00:00:00
        52 | /Table/55/1/"paris"/"\xe1G\xae\x14z\xe1H\x00\x80\x00\x00\x00\x00\x00\x01\xb8"               | /Table/55/1/"san francisco"/"\x8c\xcc\xcc\xcc\xcc\xcc@\x00\x80\x00\x00\x00\x00\x00\x01\x13" | 2262-04-11 23:47:16.854776+00:00:00
        65 | /Table/55/1/"san francisco"/"\x8c\xcc\xcc\xcc\xcc\xcc@\x00\x80\x00\x00\x00\x00\x00\x01\x13" | /Table/55/1/"seattle"/"p\xa3\xd7\n=pD\x00\x80\x00\x00\x00\x00\x00\x00\xdc"                  | 2262-04-11 23:47:16.854776+00:00:00
        64 | /Table/55/1/"seattle"/"p\xa3\xd7\n=pD\x00\x80\x00\x00\x00\x00\x00\x00\xdc"                  | /Table/55/1/"washington dc"/"Tz\xe1G\xae\x14L\x00\x80\x00\x00\x00\x00\x00\x00\xa5"          | 2262-04-11 23:47:16.854776+00:00:00
        54 | /Table/55/1/"washington dc"/"Tz\xe1G\xae\x14L\x00\x80\x00\x00\x00\x00\x00\x00\xa5"          | /Table/55/4                                                                                 | 2262-04-11 23:47:16.854776+00:00:00
        68 | /Table/55/4                                                                                 | /Table/55/4/25                                                                              | 2021-04-08 16:27:45.201336+00:00:00
        69 | /Table/55/4/25                                                                              | /Table/55/4/5E+1                                                                            | NULL
        70 | /Table/55/4/5E+1                                                                            | /Table/55/4/75                                                                              | NULL
        71 | /Table/55/4/75                                                                              | /Table/56                                                                                   | NULL
(13 rows)

The table is still split into ranges at 25.00, 50.00, and 75.00, but the split_enforced_until column is now NULL for all ranges in the table. The split is no longer enforced, and CockroachDB can merge the data in the table as needed.

Set index visibility

Set an index to be not visible

Start the MovR database on a 3-node CockroachDB demo cluster with a larger data set.

cockroach demo movr --num-histories 250000 --num-promo-codes 250000 --num-rides 125000 --num-users 12500 --num-vehicles 3750 --nodes 3

Show the indexes on the rides table. In the second-to-last column, visible, you can see that all indexes have the value t (true).

SHOW INDEXES FROM rides;

  table_name |                  index_name                   | non_unique | seq_in_index |  column_name  |  definition   | direction | storing | implicit | visible | visibility
-------------+-----------------------------------------------+------------+--------------+---------------+---------------+-----------+---------+----------+---------+-------------
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            1 | city          | city          | ASC       |    f    |    f     |    t    |          1
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            2 | rider_id      | rider_id      | ASC       |    f    |    f     |    t    |          1
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            3 | id            | id            | ASC       |    f    |    t     |    t    |          1
  rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |     t      |            1 | vehicle_city  | vehicle_city  | ASC       |    f    |    f     |    t    |          1
  ...
  rides      | rides_pkey                                    |     f      |           10 | revenue       | revenue       | N/A       |    t    |    f     |    t    |          1
(17 rows)

Explain a query that filters on revenue. Since there is no index on the revenue column, the query performs a full scan.

EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

                                                                       info
---------------------------------------------------------------------------------------------------------------------------------------------------
  distribution: full
  vectorized: true

  • sort
  │ estimated row count: 12,417
  │ order: +revenue
  │
  └── • filter
      │ estimated row count: 12,417
      │ filter: revenue > 90
      │
      └── • scan
            estimated row count: 125,000 (100% of the table; stats collected 4 minutes ago)
            table: rides@rides_pkey
            spans: FULL SCAN

  index recommendations: 1
  1. type: index creation
     SQL command: CREATE INDEX ON rides (revenue) STORING (vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time);
(19 rows)

Create the recommended index.

CREATE INDEX ON rides (revenue) STORING (vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time);

Display the indexes on the rides table to verify the newly created index rides_revenue_idx.

SHOW INDEXES FROM rides;

  table_name |                  index_name                   | non_unique | seq_in_index |  column_name  |  definition   | direction | storing | implicit | visible | visibility
-------------+-----------------------------------------------+------------+--------------+---------------+---------------+-----------+---------+----------+---------+-------------
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            1 | city          | city          | ASC       |    f    |    f     |    t    |          1
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            2 | rider_id      | rider_id      | ASC       |    f    |    f     |    t    |          1
  ...
  rides      | rides_revenue_idx                             |     t      |            1 | revenue       | revenue       | ASC       |    f    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            2 | vehicle_city  | vehicle_city  | N/A       |    t    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            3 | rider_id      | rider_id      | N/A       |    t    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            4 | vehicle_id    | vehicle_id    | N/A       |    t    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            5 | start_address | start_address | N/A       |    t    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            6 | end_address   | end_address   | N/A       |    t    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            7 | start_time    | start_time    | N/A       |    t    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            8 | end_time      | end_time      | N/A       |    t    |    f     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |            9 | city          | city          | ASC       |    f    |    t     |    t    |          1
  rides      | rides_revenue_idx                             |     t      |           10 | id            | id            | ASC       |    f    |    t     |    t    |          1
(27 rows)

Explain the query behavior after creating the index. The query now uses the rides_revenue_idx index and scans many fewer rows.

EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

                                        info
-------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 11,600 (9.3% of the table; stats collected 38 seconds ago)
    table: rides@rides_revenue_idx
    spans: (/90 - ]
(7 rows)

Alter the index to be not visible to the optimizer, specifying the NOT VISIBLE clause.
ALTER INDEX rides_revenue_idx NOT VISIBLE;

Display the table indexes and verify that the index visibility for rides_revenue_idx is f (false).

SHOW INDEXES FROM rides;

  table_name |                  index_name                   | non_unique | seq_in_index |  column_name  |  definition   | direction | storing | implicit | visible | visibility
-------------+-----------------------------------------------+------------+--------------+---------------+---------------+-----------+---------+----------+---------+-------------
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            1 | city          | city          | ASC       |    f    |    f     |    t    |          1
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            2 | rider_id      | rider_id      | ASC       |    f    |    f     |    t    |          1
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            3 | id            | id            | ASC       |    f    |    t     |    t    |          1
  ...
  rides      | rides_revenue_idx                             |     t      |            1 | revenue       | revenue       | ASC       |    f    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            2 | vehicle_city  | vehicle_city  | N/A       |    t    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            3 | rider_id      | rider_id      | N/A       |    t    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            4 | vehicle_id    | vehicle_id    | N/A       |    t    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            5 | start_address | start_address | N/A       |    t    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            6 | end_address   | end_address   | N/A       |    t    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            7 | start_time    | start_time    | N/A       |    t    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            8 | end_time      | end_time      | N/A       |    t    |    f     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |            9 | city          | city          | ASC       |    f    |    t     |    f    |          0
  rides      | rides_revenue_idx                             |     t      |           10 | id            | id            | ASC       |    f    |    t     |    f    |          0

Explain the query behavior after making the index not visible to the optimizer. With the index not visible, the optimizer reverts to full scan and recommends that you make the index visible.

EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

                                                                 info
--------------------------------------------------------------------------------------------------------------------------------------
  distribution: full
  vectorized: true

  • sort
  │ estimated row count: 12,655
  │ order: +revenue
  │
  └── • filter
      │ estimated row count: 12,655
      │ filter: revenue > 90
      │
      └── • scan
            estimated row count: 125,000 (100% of the table; stats collected 4 minutes ago; using stats forecast for 10 seconds ago)
            table: rides@rides_pkey
            spans: FULL SCAN

  index recommendations: 1
  1. type: index alteration
     SQL command: ALTER INDEX rides@rides_revenue_idx VISIBLE;
(19 rows)

Set an index as partially visible

Using the rides_revenue_idx created in the preceding example:

Set the visibility of the index to 0.5.

ALTER INDEX rides_revenue_idx VISIBILITY 0.5;

Display the table indexes and verify that the index visibility for rides_revenue_idx is 0.5.

SHOW INDEXES FROM rides;

  table_name |                  index_name                   | non_unique | seq_in_index |  column_name  |  definition   | direction | storing | implicit | visible | visibility
-------------+-----------------------------------------------+------------+--------------+---------------+---------------+-----------+---------+----------+---------+-------------
  rides      | rides_auto_index_fk_city_ref_users            |     t      |            1 | city          | city          | ASC       |    f    |    f     |    t    |          1
  ...
  rides      | rides_revenue_idx                             |     t      |            1 | revenue       | revenue       | ASC       |    f    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            2 | vehicle_city  | vehicle_city  | N/A       |    t    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            3 | rider_id      | rider_id      | N/A       |    t    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            4 | vehicle_id    | vehicle_id    | N/A       |    t    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            5 | start_address | start_address | N/A       |    t    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            6 | end_address   | end_address   | N/A       |    t    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            7 | start_time    | start_time    | N/A       |    t    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            8 | end_time      | end_time      | N/A       |    t    |    f     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |            9 | city          | city          | ASC       |    f    |    t     |    f    |        0.5
  rides      | rides_revenue_idx                             |     t      |           10 | id            | id            | ASC       |    f    |    t     |    f    |        0.5

Explain the query behavior after making the index partially visible to the optimizer. For the purposes of index recommendations, a partially visible index is treated as not visible. The optimizer recommends that you make this index fully visible.

EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

                                        info
-------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 12,413 (9.9% of the table; stats collected 36 seconds ago)
    table: rides@rides_revenue_idx
    spans: (/90 - ]

  index recommendations: 1
  1. type: index alteration
     SQL command: ALTER INDEX movr.public.rides@rides_revenue_idx VISIBLE;
(11 rows)

​Required privileges

​Synopsis

​Parameters

​Subcommands

​CONFIGURE ZONE

​Required privileges

​Parameters

​PARTITION BY

​Required privileges

​Parameters

​RENAME TO

​Required privileges

​Parameters

​SCATTER

​Required privileges

​Parameters

​SPLIT AT

​Required privileges

​Parameters

​UNSPLIT AT

​Required privileges

​Parameters

​VISIBILITY

​[NOT] VISIBLE

​Aliases

​Examples

​Configure replication zones

​Setup

​Create a replication zone for an index

​Edit a replication zone

​Remove a replication zone

​Troubleshoot replication zones

​Define partitions

​Define a list partition on an index

​Define a range partition on an index

​Define subpartitions on an index

​Repartition an index

​Unpartition an index

​Rename indexes

​Rename an index

​Scatter indexes

​Split and unsplit indexes

​Setup

​Split an index

​Set the expiration on a split enforcement

​Unsplit an index

​Set index visibility

​Set an index to be not visible

​Set an index as partially visible

​See also

Required privileges

Synopsis

Parameters

Subcommands

`CONFIGURE ZONE`

Required privileges

Parameters

`PARTITION BY`

Required privileges

Parameters

`RENAME TO`

Required privileges

Parameters

`SCATTER`

Required privileges

Parameters

`SPLIT AT`

Required privileges

Parameters

`UNSPLIT AT`

Required privileges

Parameters

`VISIBILITY`

`[NOT] VISIBLE`

Aliases

Examples

Configure replication zones

Setup

Create a replication zone for an index

Edit a replication zone

Remove a replication zone

Troubleshoot replication zones

Define partitions

Define a list partition on an index

Define a range partition on an index

Define subpartitions on an index

Repartition an index

Unpartition an index

Rename indexes

Rename an index

Scatter indexes

Split and unsplit indexes

Setup

Split an index

Set the expiration on a split enforcement

Unsplit an index

Set index visibility

Set an index to be not visible

Set an index as partially visible

See also