YSQL Tutorial: Indexes

In this tutorial you’ll learn how to use YugaByte DB’s PostgreSQL-compatible YSQL interface to create and use Indexes using the Northwind sample database. For detailed information concerning Indexes, please refer to the official PostgreSQL documentation.

To start, make sure you have all the necessary prerequisites and have logged into the YSQL shell.

Note: These instructions last tested with YugaByte DB 2.0.1

Let's begin!

INDEXES

CREATE INDEX

In this exercise we will create an index on the orders table using the employee_id column.

CREATE INDEX "order_table_index" ON orders USING btree (employee_id);

DROP INDEX

In this exercise we will drop the index order_table_index.

DROP INDEX "order_table_index";

LIST INDEXES

In this exercise we will list all the indexes in the database.

SELECT tablename,
       indexname,
       indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename,
         indexname;

CREATE HASH INDEXES

In this exercise we will create a HASH index on the customers table using company_name.

CREATE INDEX "customers_hash_index" ON customers USING HASH (company_name);

CREATE UNIQUE INDEX on a single column

In this exercise we will create a unique index on the products table using product_id_.

CREATE UNIQUE INDEX "products_unique_index" ON products USING btree(product_id);

CREATE UNIQUE INDEX on multiple columns

In this exercise we will create a unique index on the products table using product_id_ and product_name.

CREATE UNIQUE INDEX "products_unique_index" ON products USING btree(product_id, product_name);

CREATE INDEX with expression

In this exercise we will create an index on the shippers table using company_name and the lower expression.

CREATE INDEX "shipper_expr_index" ON shippers USING btree (lower(company_name))

CREATE a partial INDEX

In this exercise we will create a partial index on the orders table using only employee's whose ID is equal to 4.

CREATE INDEX "order_partial_index" ON orders USING btree (employee_id)
WHERE employee_id=4;

CREATE a multicolumn INDEX with a WHERE clause

In this exercise we will create a multicolumn index on the products table where the category_id is greater than 2.

CREATE INDEX "products_index_multi_expr" ON products USING btree(product_id, product_name)
WHERE category_id > 2;