How to query the metadata of indexes in PostgreSQL

What metadata are you looking for?

There are all sorts of swell things you can find out, if you know what you're looking for. For example, here's a dump of index stats and metadata.

SELECT *, pg_size_pretty(pg_relation_size(indexrelname::text))
    FROM pg_stat_all_indexes 
    WHERE schemaname = 'public'

Digging through the postgresql wiki will turn up all sorts of good stuff.

The query i'm using to see the list of indexes and it's actual size:

SELECT relname AS name, 
reltuples as count, (c.relpages *  (8192 /1024) / 1024 ) as size_mb,
c.relfilenode::regclass, cast(c.oid::regclass as TEXT), c.relnatts, c.relkind
FROM pg_class  c, pg_namespace n 
WHERE 
n.nspname ='MyNamespace' 
and n.oid = c.relnamespace
and c.relkind = 'i'
ORDER BY c.relpages DESC;

Edit: PostgreSQL 10 was breaking the column order due to a wrong SELECT statement, fixed query:

SELECT
    i.relname AS index_name,
    (
        SELECT array_agg(attname)
        FROM (
            SELECT a.attname
            FROM (
                SELECT colnum, row_number() over() AS rownum
                FROM (
                    SELECT unnest(string_to_array(ix.indkey::text, ' ')) AS colnum
                ) AS c
            ) AS b, pg_attribute a
            WHERE a.attrelid = t.oid AND a.attnum = b.colnum::int
            ORDER BY b.rownum
        ) AS d
    ) AS column_names
FROM pg_catalog.pg_class t
JOIN pg_catalog.pg_index ix ON ix.indrelid = t.oid
JOIN pg_catalog.pg_class i ON i.oid = ix.indexrelid
WHERE
    t.relkind = 'r'
    AND t.relnamespace = to_regnamespace('public')
    AND t.relname = 'users'
;

Gah, I'm not proud of this one...

Original post:

In the whole catalog the only place I could find where the index columns can be read in index order is in the pg_index.indkey column.

Problem lies in the fact it's an int2vector type column, which cannot be converted to array. Only way to go is to string split the space-separated output of the value using string_to_array().

You need then to unnest the result and join with pg_attribute to get the column names.

Consider the following table:

test_db=# \d users  
                                          Table "public.users"
      Column       |           Type           | Collation | Nullable |              Default              
-------------------+--------------------------+-----------+----------+-----------------------------------
 id                | integer                  |           | not null | nextval('users_id_seq'::regclass)
 org_id            | integer                  |           |          | 
 name              | text                     |           |          | 
 username          | character varying(255)   |           |          | NULL::character varying
 email             | character varying(255)   |           | not null | 
 date              | timestamp with time zone |           |          | CURRENT_TIMESTAMP
 no_constraint_col | text                     |           |          | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
Foreign-key constraints:
    "users_org_id" FOREIGN KEY (org_id) REFERENCES org(id) ON DELETE CASCADE
Referenced by:
    TABLE "user_address" CONSTRAINT "user_address_user_id_fk" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Then let's add an additional index for demonstration purpose:

test_db=# create index on users (id, name, date);

Then, the following catalog query would do the trick:

SELECT
    i.relname AS index_name,
    (
        SELECT array_agg(a.attname)
        FROM (SELECT t.oid, unnest(string_to_array(ix.indkey::text, ' ')) AS colnum) AS b
        JOIN pg_attribute a ON
            a.attrelid = b.oid AND a.attnum = b.colnum::int 
    ) AS column_names
FROM pg_catalog.pg_class t
JOIN pg_catalog.pg_index ix ON ix.indrelid = t.oid
JOIN pg_catalog.pg_class i ON i.oid = ix.indexrelid
WHERE
    t.relkind = 'r'
    AND t.relnamespace = to_regnamespace('public')
    AND t.relname = 'users'
;

Which will output:

       index_name       |  column_names  
------------------------+----------------
 users_id_name_date_idx | {id,name,date}
 users_pkey             | {id}
 users_email_key        | {email}
(3 rows)

Took me a while to get it right.

PostgreSQL does not provide the “INFORMATION_SCHEMA.STATISTICS” view. But we can query some metadata like this:

select 
    t.relname as table_name,
    i.relname as index_name,
    m.amname as index_type,
    case ix.indisunique when 'f' then 'NO' else 'YES' end UNIQUENESS,
    case ix.indisprimary when 'f' then 'NO' else 'YES' end IS_PRIMARY,
    case ix.indisclustered when 'f' then 'NO' else 'YES' end IS_CLUSTERED,
    case ix.indisvalid when 'f' then 'NO' else 'YES' end IS_VALID,
    a.attname as column_name
from pg_namespace n,
    pg_am m,
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where n.oid=t.relnamespace
    and m.oid=i.relam
    and t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and n.nspname=?
    and t.relkind = 'r'
    and t.relname=?
    and i.relname not in (select conname from pg_constraint)
order by t.relname, i.relname, a.attnum;

As Primary Key/Unique Key/Check/Exclusion constraints may generate an index by the default, so we should filter the system-generated indexes.

Check these views in PostgreSQL about the stats:

http://www.postgresql.org/docs/current/static/information-schema.html http://www.postgresql.org/docs/current/static/monitoring-stats.html