Build Faster Queries by Creating Indexes in PolarDB - PolarDB

Creates a named index on a specified table to speed up data retrieval.

CREATE INDEX name_idx ON emp (ename);

Syntax

CREATE [ UNIQUE ] INDEX name ON table
  ( { column | ( expression ) } )
  [ TABLESPACE tablespace ]

Description

CREATE INDEX builds a named index on the specified table. Indexes speed up data retrieval, but inappropriate use degrades performance.

Index key fields are specified as column names or expressions enclosed in parentheses. Specify multiple fields to create a multicolumn index (up to 32 fields).

An expression index computes a value from one or more columns. For example, an index on UPPER(col) lets the clause WHERE UPPER(col) = 'JIM' use the index instead of scanning the entire table.

PolarDB for PostgreSQL (Compatible with Oracle) supports the B-tree index method, which is an implementation of Lehman-Yao high-concurrency B-trees.

All functions and operators in an index definition must be immutable—their results must depend only on their arguments, not on external factors such as other tables or the current time. Mark any user-defined function used in an index expression as immutable when you create it.

Parameters

Parameter	Description
`UNIQUE`	Enforces uniqueness. The system checks for duplicate values when the index is created (if data exists) and on every subsequent insert or update. Duplicate values cause an error.
`name`	The name of the index. Cannot include a schema name; the index is always created in the same schema as its parent table.
`table`	The name of the table to index. Can be schema-qualified.
`column`	The name of a column in the table.
`expression`	An expression based on one or more columns, enclosed in parentheses. If the expression is a function call, the parentheses can be omitted.
`TABLESPACE tablespace`	The tablespace where the index is stored.

Examples

To create a B-tree index on the ename column in the emp table:

CREATE INDEX name_idx ON emp (ename);

To create the same index in a specific tablespace:

CREATE INDEX name_idx ON emp (ename) TABLESPACE index_tblspc;

To create a unique index that prevents duplicate values in the ename column:

CREATE UNIQUE INDEX name_idx ON emp (ename);

To create an expression index that supports case-insensitive searches on ename:

CREATE INDEX name_upper_idx ON emp ((UPPER(ename)));

To create a multicolumn index on deptno and ename:

CREATE INDEX dept_name_idx ON emp (deptno, ename);

Usage notes

Indexes are not used for IS NULL clauses by default.
When CREATE INDEX targets a partitioned table, the index propagates to all partitions.
Multicolumn indexes support up to 32 fields.
For privileged accounts, a tablespace specified in the statement is accepted but the index is stored in the pg_default tablespace; a message informs you of this behavior. Tablespace specification is not supported for regular accounts.