How to List Indexes in PostgreSQL

psql

\di lists indexes across the search_path, while \d orders shows one table with its indexes, constraints, and triggers underneath the column list. Both are psql-only. \di+ adds the index size, which is the quickest way to spot an index that has grown larger than the table it serves.

\di
\d orders

Plain SQL (works from any client)

pg_indexes gives you indexname plus the complete CREATE INDEX statement in indexdef, which is what you want when recreating an index elsewhere or reviewing what a migration actually built. It carries no size information; use pg_relation_size for that, or check idx_scan in pg_stat_user_indexes to find indexes nothing is using.

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

Constraint-backed indexes look different

A primary key or unique constraint creates an index you cannot drop directly: DROP INDEX on it fails, because the constraint owns it. pg_indexes lists those alongside ordinary indexes with no hint about which is which, so join pg_constraint when you need to tell them apart.

SELECT i.indexname,
       c.conname AS owned_by_constraint
FROM pg_indexes i
LEFT JOIN pg_constraint c ON c.conname = i.indexname
WHERE i.tablename = 'orders';

Finding indexes nothing uses

Every index slows down writes and takes disk space, so the useful question is usually not which indexes exist but which ones earn their keep. pg_stat_user_indexes counts scans since the last statistics reset, and a long-lived database with idx_scan at zero is showing you dead weight. Check that the counter has been running long enough to be meaningful before dropping anything, and exclude the constraint-backed ones.

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Do more with PostgreSQL

• Export Postgres to Google Sheets on a schedule
• Postgres SQL editor in the browser