Timescale Logo


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 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
    "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

                      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)

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, you acknowledge Timescale’s Privacy Policy
2023 © Timescale Inc. All rights reserved.