How to query the metadata of indexes in PostgreSQL

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开发者_C百科/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

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.

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

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;

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