How to query the metadata of indexes in PostgreSQL

Question

I need to be able to query a PostgreSQL database to obtain information about the indexes present and their details.

On SQL Server, I can do the following to get a list of all tables/indexes/columns for all indexes:

select TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME
from INFORMATION_SCHEMA.STATISTICS
where TABLE_SCHEMA = 'my_schema'
order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX

It seems that the STATISTICS table of INFORMATION_SCHEMA is a SQL Server extension. How can I do the equivalent in PostgreSQL?

EDIT: I'm specifically trying to return a denormalized result set as follows

TableName, IndexName, UniqueFl, ColumnName

So I get a row back for each column in all indexes.

Thanks, Jon

Answer 1

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.

Answer 2

I don't think this is possible from the information_schema see this discussion. Indexes created other than from constraints won't be in the information schema.

However from the system tables you can see this question

Answer 3

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;