How to request index metadata in PostgreSQL

Question

I need to be able to query the PostgreSQL database to get information about the available indexes and their details.

In 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

The STATISTICS INFORMATION_SCHEMA table seems to be an extension of SQL Server. How can I make an equivalent in PostgreSQL?

EDIT: I specifically try to return a denormalized result set as follows

TableName, IndexName, UniqueFl, ColumnName

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

Thanks, John

Answer 1

, information_schema . . , , , .

.

Answer 2

?

, , , . , .

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

postgresql wiki .

Answer 3

, , :

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;

Answer 4

PostgreSQL :

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