How to list PostgreSQL indexes for a table

By Anil Gautam · Published and reviewed September 20, 2026

Check pg_indexes before adding an index to a PostgreSQL table. Read the definition as well as the name. An existing index may already cover the columns you need, depending on their order and any expression or filter.

Find the definitions

Replace public.orders with your table’s schema and name. The query only reads index metadata.

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

indexdef is a reconstructed CREATE INDEX statement. It helps you see uniqueness, indexed expressions and any WHERE predicate. See PostgreSQL's pg_indexes reference.

Read the index as a query requirement

Imagine an orders screen filtered by customer and sorted by creation time. An index on customer_id answers a different question from one on (customer_id, created_at). An expression index on a transformed value also differs from an index on the original column. Do not compare names alone: names are chosen by developers and can be misleading.

Write down the exact filter, ordering, selected columns and expected result size. Then inspect the plan for that query. PostgreSQL explains the considerations for multicolumn indexes; there is no universal rule that every additional column improves every query.

An existing index may legitimately be unused

A table scan can be a reasonable choice for a small table or a query returning much of it. Conversely, a selective query might be poorly estimated. Use EXPLAIN to investigate the plan and, on an appropriate environment, compare actual rows and timing. Avoid disabling planner choices to make a screenshot show an index scan.

Keep an investigation record

Save the original query, current index definitions and an initial plan. Change one thing in a development copy, rerun the same workload, and record the outcome. Check writes as well as reads before keeping a new index. Your evidence should explain which workload improved and at what cost, rather than merely proving that a new index exists.

In plainDB, use the structure and index views alongside the SQL results. These are inspection tools; adding or dropping a production index deserves its own reviewed migration and rollout plan.

Continue reading

Read EXPLAIN · Find tables and columns

For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.