Indexes

Create a new index on a table

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

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

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)Copy

// 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)Copy

// List all indexes from all tables (psql)

Drop index from a table

drop index idx_employee_emp_name;
Copy

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