'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/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

postgresqlschemametadata


Solution 1:[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.

Solution 2:[2]

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;

Solution 3:[3]

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.

Solution 4:[4]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Andy Lester
Solution 2 xacinay
Solution 3
Solution 4 Frank Heikens

Related Questions