ALTER INDEX

On this page Carat arrow pointing down

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

Note:

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

ALTER INDEX table_name @ index_name PARTITION BY LIST ( name_list ) ( list_partitions RANGE ( name_list ) ( range_partitions ) NOTHING , SPLIT AT select_stmt WITH EXPIRATION a_expr UNSPLIT AT select_stmt ALL RENAME TO index_new_name CONFIGURE ZONE USING variable = COPY FROM PARENT value , variable = value COPY FROM PARENT DISCARD NOT VISIBLE INVISIBLE IF EXISTS table_name @ index_name PARTITION BY LIST ( name_list ) ( list_partitions RANGE ( name_list ) ( range_partitions ) NOTHING , RENAME TO index_new_name NOT VISIBLE INVISIBLE

Parameters

Parameter Description
table_name The name of the table with the index you want to change.
index_name The current name of the index you want to change.
IF EXISTS Alter the index only if an index 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 Configure replication zones for an index. (Enterprise-only.)
PARTITION BY Partition, re-partition, or un-partition an index. (Enterprise-only.)
RENAME TO Change the name of an index.
SPLIT AT Force a range split at the specified row in the index.
UNSPLIT AT Remove a range split enforcement in the index.
[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 Configure Replication Zones.

Note:

This is an enterprise-only feature. Request a 30-day trial license to try it out.

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 Configure replication zones.

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.

Note:

This is an enterprise-only feature. Request a 30-day trial license to try it out.

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.

Tip:

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.

Note:

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.

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 a size of 512 MiB. 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. 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.

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.

[NOT] VISIBLE

ALTER INDEX ... VISIBLE and ALTER INDEX ... NOT VISIBLE sets the visibility of an index. This determines whether the index is visible to the cost-based optimizer.

By default, indexes are visible. If NOT VISIBLE, the index will not be used in queries unless it is specifically selected with an index hint or the property is overridden with the optimizer_use_not_visible_indexes session variable.

This allows you to create an index and check for query plan changes without affecting production queries. For an example, see Set an index to be not visible.

Note the following considerations:

  • Primary indexes must be visible.
  • Indexes that are not visible are still used to enforce UNIQUE and FOREIGN KEY constraints.
  • Indexes that are not visible are still used for foreign key cascades.
  • When defining a unique constraint, the NOT VISIBLE syntax cannot be used to make the corresponding index not visible. Instead, use ALTER INDEX after creating the unique constraint.

For examples, see 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.

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

Create a replication zone for an index

Tip:

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

Note:

This is an enterprise-only feature. Request a 30-day trial license to try it out.

The secondary indexes on a table will automatically use the replication zone for the table. However, with an enterprise license, you can 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).

Tip:

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.

icon/buttons/copy
> ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE USING num_replicas = 5, gc.ttlseconds = 100000;
CONFIGURE ZONE 1
icon/buttons/copy
> 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

icon/buttons/copy
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

Note:

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.

icon/buttons/copy
ALTER INDEX vehicles@vehicles_auto_index_fk_city_ref_users CONFIGURE ZONE DISCARD;

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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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

icon/buttons/copy
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

icon/buttons/copy
ALTER INDEX students@name_idx PARTITION BY NOTHING;

Rename indexes

Rename an index

icon/buttons/copy
CREATE INDEX on users(name);
icon/buttons/copy
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)
icon/buttons/copy
ALTER INDEX users@name_idx RENAME TO users_name_idx;
icon/buttons/copy
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)

Split and unsplit indexes

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.

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

Split an index

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

icon/buttons/copy
CREATE INDEX revenue_idx ON rides(revenue);

Then split the table ranges by secondary index values:

icon/buttons/copy
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)
icon/buttons/copy
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:

icon/buttons/copy
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.

icon/buttons/copy
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.

icon/buttons/copy
cockroach demo movr --num-histories 250000 --num-promo-codes 250000 --num-rides 125000 --num-users 12500 --num-vehicles 3750 --nodes 3
  1. Show the indexes on the rides table. In the last column, visible, you can see that all indexes have the value t (true).

    icon/buttons/copy
    SHOW INDEXES FROM rides;
    
      table_name |                  index_name                   | non_unique | seq_in_index |  column_name  | direction | storing | implicit | visible
    -------------+-----------------------------------------------+------------+--------------+---------------+-----------+---------+----------+----------
      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_pkey                                    |     f      |           10 | revenue       | N/A       |    t    |    f     |    t
    (17 rows)
    
  2. Explain a query that filters on revenue. Since there is no index on the revenue column, the query performs a full scan.

    icon/buttons/copy
    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)
    
  3. Create the recommended index.

    icon/buttons/copy
    CREATE INDEX ON rides (revenue) STORING (vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time);
    
  4. Display the indexes on the rides table to verify the newly created index rides_revenue_idx.

    icon/buttons/copy
    SHOW INDEXES FROM rides;
    
      table_name |                  index_name                   | non_unique | seq_in_index |  column_name  | direction | storing | implicit | visible
    -------------+-----------------------------------------------+------------+--------------+---------------+-----------+---------+----------+----------
      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_revenue_idx                             |     t      |            1 | revenue       | ASC       |    f    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            2 | vehicle_city  | N/A       |    t    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            3 | rider_id      | N/A       |    t    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            4 | vehicle_id    | N/A       |    t    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            5 | start_address | N/A       |    t    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            6 | end_address   | N/A       |    t    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            7 | start_time    | N/A       |    t    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            8 | end_time      | N/A       |    t    |    f     |    t
      rides      | rides_revenue_idx                             |     t      |            9 | city          | ASC       |    f    |    t     |    t
      rides      | rides_revenue_idx                             |     t      |           10 | id            | ASC       |    f    |    t     |    t
    (27 rows)
    
  5. Explain the query behavior after creating the index. The query now uses the rides_revenue_idx index and scans many fewer rows.

    icon/buttons/copy
    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)
    
  6. Alter the index to be not visible to the optimizer, specifying the NOT VISIBLE clause.

    icon/buttons/copy
    ALTER INDEX rides_revenue_idx NOT VISIBLE;
    
  7. Display the table indexes and verify that the index visibility for rides_revenue_idx is f (false).

    icon/buttons/copy
    SHOW INDEXES FROM rides;
    
      table_name |                  index_name                   | non_unique | seq_in_index |  column_name  | direction | storing | implicit | visible
    -------------+-----------------------------------------------+------------+--------------+---------------+-----------+---------+----------+----------
      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_revenue_idx                             |     t      |            1 | revenue       | ASC       |    f    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            2 | vehicle_city  | N/A       |    t    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            3 | rider_id      | N/A       |    t    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            4 | vehicle_id    | N/A       |    t    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            5 | start_address | N/A       |    t    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            6 | end_address   | N/A       |    t    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            7 | start_time    | N/A       |    t    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            8 | end_time      | N/A       |    t    |    f     |    f
      rides      | rides_revenue_idx                             |     t      |            9 | city          | ASC       |    f    |    t     |    f
      rides      | rides_revenue_idx                             |     t      |           10 | id            | ASC       |    f    |    t     |    f
    (27 rows)
    
  8. 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.

    icon/buttons/copy
    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)
    

See also


Yes No
On this page

Yes No