Indexes in PostgreSQL are objects used to improve the speed of accessing data. They are created based on either database columns or partial data.
Their function is to create a pointer to the corresponding row in the appropriate table.
Show the list of indexes in Postgres using psql cli
The most straightforward method to list all indexes (including their names, types and tables) in a PostgreSQL database is using the \di
command in the psql command-line interface.
\di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+-----------
public | idx_employee_id | index | postgres | employees
public | idx_order_date | index | postgres | orders
(2 rows)
How to list Postgres Indexes using SQL query
For more detailed information on indexes, such as the tables they belong to and their definitions, you can utilize SQL queries to list indexes.
Listing all indexes:
SELECT indexname, tablename, indexdef FROM pg_indexes;
indexname | tablename | indexdef
---------------+-----------+---------------------------------------------------------------
idx_employee_id | employees | CREATE INDEX idx_employee_id ON public.employees USING btree (id)
idx_order_date | orders | CREATE INDEX idx_order_date ON public.orders USING btree (order_date)
(2 rows)
Filtering indexes by table:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table_name';
Replace your_table_name
with the actual table name to list indexes associated with a specific table.
Related Useful Commands
PostgreSQL also offers several commands related to index management:
Creating an index in PostgreSQL:
CREATE INDEX index_name ON table_name (column_name);
Dropping an index in PostgreSQL:
DROP INDEX index_name;
Reindexing a database/table/index in PostgreSQL:
REINDEX {DATABASE | TABLE | INDEX} name;