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 |
|---|---|
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 |
Replication Controls for an index. |
PARTITION BY |
Partition, re-partition, or un-partition an index. |
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. |
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.
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.
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.0means that an index is not visible to the cost-based optimizer. This is equivalent toNOT VISIBLE.VISIBILITY 1.0means that an index is visible to the optimizer. This is equivalent toVISIBLE.- Any value between
0.0and1.0means that an index is visible to the specified fraction of queries. This is known as a partially visible index.Note: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,UNIQUEindexes to enforceUNIQUEconstraints. - Queries may still read from
NOT VISIBLEindexes to perform foreign key cascades and enforceFOREIGN KEYconstraints. - When defining a
UNIQUEconstraint, you cannot use theNOT VISIBLEsyntax to make the corresponding index not visible. Instead, useALTER INDEX ... NOT VISIBLEafter creating theUNIQUEconstraint.
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;
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)
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.
$ 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
ridestable. In the second-to-last column,visible, you can see that all indexes have the valuet(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
revenuecolumn, 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
ridestable to verify the newly created indexrides_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_idxindex 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 VISIBLEclause.ALTER INDEX rides_revenue_idx NOT VISIBLE;Display the table indexes and verify that the index visibility for
rides_revenue_idxisf(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 | 0Explain 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_idxis0.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.5Explain 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)
See also
CREATE INDEXCREATE TABLEALTER TABLEALTER PARTITIONSHOW JOBS- Online Schema Changes
- SQL Statements
Was this helpful?
