List columns with indexes in PostgreSQL

sql

index-inspection

postgresql-utilities

database-search

byNikita Barsukov·Feb 28, 2025

If your ultimate goal is to >!get all the indexed columns!<, this compact query is your new best friend:

-- Fetch that gold!
SELECT
    tbl.relname AS "table",
    idx.relname AS "index",
    col.attname AS "column"
FROM
    pg_index i
JOIN
    pg_attribute col ON col.attrelid = i.indrelid AND col.attnum = ANY(i.indkey)
JOIN
    pg_class idx ON idx.oid = i.indexrelid
JOIN
    pg_class tbl ON tbl.oid = i.indrelid
WHERE
    tbl.relkind = 'r'  -- Cuz we are rebels
ORDER BY
    "table",
    "index";

This darling of a query gives you all tables, indexes, and the corresponding indexed columns, well organized for your ease.

Precision search in PostgreSQL

Now you might be thinking, "I want to focus on specific tables or exclude certain ones." Worry not - you can impose conditional WHERE clauses to level up your targeted searches.

Here's how to dismiss system tables:

AND n.nspname NOT IN ('pg_catalog', 'information_schema')  -- Bye system tables!

Dealing with multi-column indexes

In the grand act of PostgreSQL, sometimes a single index lights up multiple columns. To put them together in the spotlight:

SELECT
    tbl.relname AS "table",
    idx.relname AS "index",
    STRING_AGG(col.attname, ', ') WITHIN GROUP (ORDER BY col.attnum) AS "columns"
FROM
    ...
-- Bring the whole crew together!

This combines indexed columns into a tidy list, perfect when dealing with multiple indexed columns, or as I call it, the ensemble cast!

Mastering advanced index inspection

The secret to going professional with index inspection, you ask? The psql command-line features, the VIP backstage passes to PostgreSQL's advanced utilities!

For example, \di+ does it all - from indexes, size, to even comments! \diS filters out system-wide indexes. And don't forget, these powerful \d commands are an exclusive feature of psql.

explain-codes / Sql / List columns with indexes in PostgreSQL

Linked

Postgresql Index Usage Analysis



How to find a table having a specific column in postgresql



Query to check index on a table



How do I force Postgres to use a particular index?



Get list of all tables in Oracle?

