Indexes

Create a new index on a table

create index idx_employee_emp_name on employee using btree (emp_name asc);

// Create a new index on the emp_name column of the employee table (SQL)

This index specifies “btree” as the index method and uses “asc” to store the index key column data in ascending order.

View indexes of a table

\d employee
postgres=# \d employee;
                                    Table "public.employee"
   Column   |         Type          |                         Modifiers                         
------------+-----------------------+-----------------------------------------------------------
 emp_id     | integer               | not null default nextval('employee_emp_id_seq'::regclass)
 emp_name   | character varying(50) | not null
 emp_salary | numeric(9,2)          | not null
Indexes:
    "employee_pkey" PRIMARY KEY, btree (emp_id)
    "idx_employee_emp_name" btree (emp_name)

// List indexes of a table along with table definition (psql)

List all indexes

\di
                      List of relations
 Schema |         Name          | Type  |  Owner   |  Table   
--------+-----------------------+-------+----------+----------
 public | employee_pkey         | index | postgres | employee
 public | idx_employee_emp_name | index | postgres | employee
(2 rows)

// List all indexes from all tables (psql)

Database indexes in Timescale work the same as in regular PostgreSQL. When working with Timescale’s hypertables (which abstract partitioning, taking care of it automatically), Timescale will also create the indexes automatically. Try Timescale for free today.

Drop index from a table

drop index idx_employee_emp_name;

// Drop an existing index from a table (SQL)