The use of indexes can enhance database performance by enabling the database server to find rows faster. You can create, drop, and list indexes, as well as use indexes on expressions.
You can create indexes in YSQL and YCQL using the
CREATE INDEX statement that has the following syntax:
CREATE INDEX index_name ON table_name(column_list);
column_list represents a column or a comma-separated list of several columns to be stored in the index. An index created for more than one column is called a composite index.
You can also create a functional index in YSQL, in which case you would replace any element of column_list with an expression. For more information, see Expression Indexes.
YSQL currently supports index access methods
lsm (log-structured merge-tree) and
ybgin. These indexes are based on YugabyteDB's DocDB storage and are similar in functionality to PostgreSQL's
gin indexes, respectively. The index access method can be specified with
USING <access_method_name> after table_name. By default,
lsm is chosen. For more information on
ybgin, see Generalized inverted index.
You can apply sort order on the indexed columns as
HASH (default option for the first column),
ASC (default option for the second and subsequent columns), as well as
DESC. For examples, see HASH and ASC examples in YSQL
List indexes and verify the query plan
YSQL inherits all the functionality of the PostgreSQL
pg_indexes view that allows you to retrieve a list of all indexes in the database as well as detailed information about every index.
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table_name';
For details, see pg_indexes in the PostgreSQL documentation.
For YCQL, you can use the DESCRIBE INDEX command to check the indexes as follows:
DESCRIBE INDEX <index name>
You can also use the
EXPLAIN statement to check if a query uses an index and determine the query plan before execution.
For information regarding the EXPLAIN statement, see:
You can remove one or more existing indexes using the
DROP INDEX statement in YSQL and YCQL with the following syntax:
DROP INDEX index_name1, index_name2, index_name3, ... ;
For additional information, see DROP INDEX YCQL API.
Example scenario using YSQL
SetupAny YugabyteDB cluster, including local single-node cluster or Sandbox cluster in YugabyteDB Managed. See Set up YugabyteDB universe.
Suppose you work with a database that includes the following table populated with data:
CREATE TABLE employees ( employee_no integer, name text, department text );
INSERT INTO employees VALUES (1221, 'John Smith', 'Marketing'), (1222, 'Bette Davis', 'Sales'), (1223, 'Lucille Ball', 'Operations');
The following example shows a query that finds employees working in Operations:
SELECT * FROM employees WHERE department = 'Operations';
To process the preceding query, the whole
employees table needs to be scanned. For large organizations, this might take a significant amount of time.
To speed up the process, you create an index for the department column, as follows:
CREATE INDEX index_employees_department ON employees(department);
The following example executes the query after the index has been applied to
department and uses the
EXPLAIN statement to prove that the index participated in the processing of the query:
EXPLAIN SELECT * FROM employees WHERE department = 'Operations';
The following is the output produced by the preceding example:
QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using index_employees_department on employees (cost=0.00..5.22 rows=10 width=68) Index Cond: (department = 'Operations'::text)
For additional information, see:
The following example shows how to remove
index_employees_department that was created in Create indexes:
DROP INDEX index_employees_department;