PostgreSQL - List Indexes

In PostgreSQL, indexes are crucial for optimizing database query performance. There may come a time when you need to list all the indexes in your database or for specific tables to review or manage them.

Here's how you can list indexes in PostgreSQL:

1. List All Indexes in a Database:

To list all the indexes for all tables in the current database, you can use the \di command in the psql command-line tool.

\di

2. List Indexes for a Specific Table:

If you want to list all indexes for a specific table, you can query the pg_indexes system catalog. For instance, to list all indexes for the table mytable, you would do:

SELECT indexname as index_name, 
       indexdef as index_definition 
FROM pg_indexes 
WHERE tablename = 'mytable';

3. Detailed Information about Indexes:

For more detailed information about indexes, such as size and other stats, you can join multiple system tables:

SELECT 
    t.relname AS table_name,
    i.relname AS index_name,
    array_to_string(array_agg(a.attname), ', ') AS columns,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM 
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE 
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname = 'mytable' -- remove this line to show all tables
GROUP BY 
    t.relname,
    i.relname,
    i.oid
ORDER BY 
    table_name,
    index_name;

This query will give you the table name, index name, columns included in the index, and the size of the index.

Note:

Remember, if you're not the owner of the table or a superuser, you might not see all the indexes depending on the permissions set by the database administrator. If you need to see all indexes regardless of the owner, you might need to ask for additional privileges or request the information from your database administrator.

How to view indexes in PostgreSQL:
- Retrieve a list of all indexes in the database.
```
\di
```
Show index details in PostgreSQL:
- View detailed information about a specific index.
```
\d+ index_name
```
PostgreSQL list indexes for a specific table:
- Display all indexes associated with a particular table.
```
\di table_name
```
Query to display all indexes in PostgreSQL:
- Write a query to retrieve all indexes.
```
SELECT indexname, indexdef FROM pg_indexes;
```

List unique indexes in PostgreSQL:

Identify unique indexes in the database.

SELECT indexname, indexdef FROM pg_indexes WHERE indexdef ILIKE '%UNIQUE%';

Get information on primary key indexes in PostgreSQL:
- Retrieve details about primary key indexes.
```
SELECT indexname, indexdef FROM pg_indexes WHERE indexdef ILIKE '%PRIMARY KEY%';
```
View index columns in PostgreSQL:
- Display the columns associated with each index.
```
SELECT indexname, indexdef FROM pg_indexes;
```
List indexes with their associated tables in PostgreSQL:
- Retrieve a list of indexes along with the tables they belong to.
```
SELECT schemaname, tablename, indexname FROM pg_indexes;
```
Find unused indexes in PostgreSQL:
- Identify indexes that have not been used.
```
SELECT indexname FROM pg_indexes
WHERE NOT indisused;
```
Identify fragmented indexes in PostgreSQL:
- Check for fragmentation in indexes.
```
SELECT indexname FROM pg_indexes
WHERE indexdef ILIKE '%TABLESPACE%';
```
List all indexes on a specific schema in PostgreSQL:
- View indexes for a specific schema.
```
SELECT indexname FROM pg_indexes
WHERE schemaname = 'your_schema';
```
Show partial indexes in PostgreSQL:
- Display information about partial indexes.
```
SELECT indexname FROM pg_indexes
WHERE indexdef ILIKE '%WHERE%';
```

Find duplicate indexes in PostgreSQL:

Identify duplicate indexes.

SELECT indexname, COUNT(*) AS count
FROM pg_indexes
GROUP BY indexname
HAVING COUNT(*) > 1;

List all indexes on a specific column in PostgreSQL:
- Retrieve indexes associated with a particular column.
```
SELECT indexname FROM pg_indexes
WHERE indexdef ILIKE '%column_name%';
```

Retrieve index statistics in PostgreSQL:

Obtain statistics about indexes.

SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_all_indexes;

Show index sizes in PostgreSQL:

Display the sizes of indexes.

SELECT indexname, pg_size_pretty(pg_total_relation_size(indexname)) AS size
FROM pg_indexes;

Find missing indexes in PostgreSQL:
- Identify queries that could benefit from additional indexes.
```
SELECT * FROM pg_missing_indexes();
```
List all indexes using pg_indexes in PostgreSQL:
- Utilize the pg_indexes system catalog table to list all indexes.
```
SELECT * FROM pg_indexes;
```