CREATE INDEX

Synopsis

This command creates an index on the specified column(s) 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 } ]

create_index

index_elem

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.

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.

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"