CREATE INDEX

Synopsis

Use the CREATE INDEX statement to create an index on the specified columns of the specified table. Indexes are primarily used to improve query performance.

Syntax

• Grammar
• Diagram

create_index ::= CREATE [ UNIQUE ] INDEX [ [ IF NOT EXISTS ] name ]  
                 ON [ ONLY ] table_name ( index_elem [ , ... ] )  
                 [ INCLUDE ( column_name [ , ... ] ) ]  
                 [ WHERE predicate ]

index_elem ::= { column_name | ( expression ) } 
               [ operator_class_name ] [ HASH | ASC | DESC ] 
               [ NULLS { FIRST | LAST } ]

Semantics

CONCURRENTLY, USING method, COLLATE, and TABLESPACE options are not yet supported.

UNIQUE

Enforce that duplicate values in a table are not allowed.

INCLUDE clause

Specify a list of columns which will be included in the index as non-key columns.

WHERE clause

A partial index is an index that is built on a subset of a table and includes only rows that satisfy the condition specified in the WHERE clause. It can be used to exclude NULL or common values from the index, or include just the rows of interest. This will speed up any writes to the table since rows containing the common column values don't need to be indexed. It will also reduce the size of the index, thereby improving the speed for read queries that use the index.

name

Specify the name of the index to be created.

table_name

Specify the name of the table to be indexed.

index_elem

column_name

Specify the name of a column of the table.

expression

Specify one or more columns of the table and must be surrounded by parentheses.

• HASH - Use hash of the column. This is the default option for the first column and is used to hash partition the index table.
• ASC — Sort in ascending order. This is the default option for second and subsequent columns of the index.
• DESC — Sort in descending order.
• NULLS FIRST - Specifies that nulls sort before non-nulls. This is the default when DESC is specified.
• NULLS LAST - Specifies that nulls sort after non-nulls. This is the default when DESC is not specified.

SPLIT INTO

For hash-sharded indexes, you can use the SPLIT INTO clause to specify the number of tablets to be created for the index. The hash range is then evenly split across those tablets. Presplitting indexes, using SPLIT INTO, distributes index workloads on a production cluster. For example, if you have 3 servers, splitting the index into 30 tablets can provide higher write throughput on the index. For an example, see Create an index specifying the number of tablets.

Examples

Unique index with HASH column ordering

Create a unique index with hash ordered columns.

yugabyte=# CREATE TABLE products(id int PRIMARY KEY,
                                 name text,
                                 code text);
yugabyte=# CREATE UNIQUE INDEX ON products(code);
yugabyte=# \d products
              Table "public.products"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           |          |
 code   | text    |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, lsm (id HASH)
    "products_code_idx" UNIQUE, lsm (code HASH)

ASC ordered index

Create an index with ascending ordered key.

yugabyte=# CREATE INDEX products_name ON products(name ASC);
yugabyte=# \d products_name
   Index "public.products_name"
 Column | Type | Key? | Definition
--------+------+------+------------
 name   | text | yes  | name
lsm, for table "public.products

INCLUDE columns

Create an index with ascending ordered key and include other columns as non-key columns

yugabyte=# CREATE INDEX products_name_code ON products(name) INCLUDE (code);
yugabyte=# \d products_name_code;
 Index "public.products_name_code"
 Column | Type | Key? | Definition
--------+------+------+------------
 name   | text | yes  | name
 code   | text | no   | code
lsm, for table "public.products"

Create an index specifying the number of tablets

To specify the number of tablets for an index, you can use the CREATE INDEX statement with the SPLIT INTO clause.

CREATE TABLE employees (id int PRIMARY KEY, first_name TEXT, last_name TEXT) SPLIT INTO 10 TABLETS;
CREATE INDEX ON employees(first_name, last_name) SPLIT INTO 10 TABLETS;

Partial indexes

Consider an application maintaining shipments information. It has a shipments table with a column for delivery_status. If the application needs to access in-flight shipments frequently, then it can use a partial index to exclude rows whose shipment status is delivered.

yugabyte=# create table shipments(id int, delivery_status text, address text, delivery_date date);
yugabyte=# create index shipment_delivery on shipments(delivery_status, address, delivery_date) where delivery_status != 'delivered';