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

score 7 · Answer 1 · answered Nov 09 '10 at 23:35

7

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.

answered Nov 09 '10 at 23:35

Andy Lester

91,102
13
100
152

Thanks, I've added an edit to show exactly what I want. It's more metadata than statistics. – Jon Nov 10 '10 at 12:25

score 6 · Accepted Answer · edited May 23 '17 at 12:25

6

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

edited May 23 '17 at 12:25

Community

1
1

answered Nov 10 '10 at 14:38

Gavin

6,180
3
25
25

Thanks - the linked question is exactly what I needed. – Jon Nov 10 '10 at 20:12

score 2 · Answer 3 · answered May 24 '13 at 13:08

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;

Find Bugs · Answer 4 · 2022-02-23T09:00:57.143

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.

score -1 · Answer 5 · answered Nov 09 '10 at 21:49

-1

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

answered Nov 09 '10 at 21:49

Frank Heikens

117,544
24
142
135

Thanks, I've checked the first link and can't find what I need. I don't think the second is what I'm after. – Jon Nov 09 '10 at 22:02

How to query the metadata of indexes in PostgreSQL

5 Answers5

Linked