Indexes Inspection pg_indexes · \d · pg_stat_user_indexes

LIST Indexes

No SHOW INDEXES in PostgreSQL — use the pg_indexes view, the psql \d shortcut, and pg_stat_user_indexes for usage stats. Together they tell you what exists and what's actually working.

Inspecting indexes

Once you've added a few indexes — manually, via constraints, or both — you'll want to know what's actually in the database. PostgreSQL doesn't have a SHOW INDEXES command; instead, you query system views or use psql's \d shortcut.

The two everyday tools:

pg_indexes — a view that lists every index along with its definition.
\d table_name in psql — shows everything about a table including its indexes.

The pg_indexes view

Column	What it stores
schemaname	Schema the index belongs to.
tablename	Table the index is built on.
indexname	Name of the index itself.
tablespace	Tablespace storing the index (often NULL = default).
indexdef	The full `CREATE INDEX` statement that defines it.

Example 1 — list every index in the public schema

SQL

SELECT tablename, indexname, indexdef
FROM   pg_indexes
WHERE  schemaname = 'public'
ORDER  BY tablename, indexname;

Example 2 — indexes on a specific table

SQL

SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'employees';

Example 3 — indexes on tables matching a pattern

SQL

-- All indexes on tables whose names start with "c"
SELECT tablename, indexname, indexdef
FROM   pg_indexes
WHERE  tablename LIKE 'c%';

The psql shortcut: \d

If you're using the psql command-line client, the \d meta-command is the fastest way to see a table's indexes alongside columns and constraints.

psql

\d employees

Output looks something like:

output

                                  Table "public.employees"
   Column     |          Type          | Nullable | Default
--------------+------------------------+----------+---------------------------------
 employee_id  | integer                | not null | nextval('employees_employee_id_seq'...)
 first_name   | character varying(20)  |          |
 last_name    | character varying(25)  | not null |
 email        | character varying(100) |          |
Indexes:
    "employees_pkey"      PRIMARY KEY, btree (employee_id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)
    "idx_emp_last_name"   btree (last_name)

Use \di to see only indexes (across all tables), or \di+ for size information added.

Beyond definitions — usage stats

pg_indexes tells you what indexes exist; pg_stat_user_indexes tells you which ones are doing work. Pair them when planning maintenance.

SQL

SELECT schemaname,
       relname        AS table_name,
       indexrelname   AS index_name,
       idx_scan       AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
ORDER  BY idx_scan DESC;

idx_scan is how many times the index has been used since stats were last reset.
idx_scan = 0 + sizable index = candidate for dropping.
The view also tracks idx_tup_read and idx_tup_fetch for deeper analysis.

💡 Stats reset on server restart. If you've recently restarted Postgres, give the workload a few hours (or days) to run before drawing conclusions about which indexes are unused.

Other useful queries

SQL — index sizes ranked

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
ORDER  BY pg_relation_size(indexrelid) DESC
LIMIT  20;

SQL — find duplicate indexes

-- Indexes whose definitions overlap in column lists
SELECT a.indexname AS idx_a, b.indexname AS idx_b, a.tablename
FROM   pg_indexes a
JOIN   pg_indexes b ON a.tablename = b.tablename
                  AND a.indexname < b.indexname
                  AND a.indexdef    = b.indexdef;

Recap

PostgreSQL has no SHOW INDEXES — query pg_indexes instead.
In psql, \d table shows indexes alongside everything else; \di lists indexes themselves.
pg_stat_user_indexes tells you which indexes are actually being used.
Combine size + usage to find drop candidates.