[9.2] Querying indexes on PostgreSQL

/************************************************************************************************
-- Title : [9.2] Querying indexes on PostgreSQL
-- Reference : dbrang.tistory.com
-- Key word : PostgreSQL, index 조회 인덱스조회 인덱스 조회 인덱스 찾기 인덱스찾기
************************************************************************************************/

-- initiation

drop table ttt;

-- creation table

create table ttt ( a int not null primary key, b varchar(10), c int);

-- creation index

create index ix_ttt

on ttt(c,b);

-- querying indexes

select

    t.relname as table_name,

    i.relname as index_name,

    a.attname as column_name

from

    pg_class t,

    pg_class i,

    pg_index ix,

    pg_attribute a

where

    t.oid = ix.indrelid

    and i.oid = ix.indexrelid

    and a.attrelid = t.oid

    and a.attnum = ANY(ix.indkey)

    and t.relname = 'ttt'

order by

    t.relname,

    i.relname;