Timescale Logo

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)

Timescale Logo

Subscribe to the Timescale Newsletter

By submitting, I acknowledge Timescaleā€™s Privacy Policy
2024 Ā© Timescale Inc. All rights reserved.