List columns with indexes in PostgreSQL
Asked Answered
H

25

282

I would like to get the columns that an index is on in PostgreSQL.

In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column.

mysql> show indexes from foos;

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foos  |          0 | PRIMARY             |            1 | id          | A         |       19710 |     NULL | NULL   |      | BTREE      |         | 
| foos  |          0 | index_foos_on_email |            1 | email       | A         |       19710 |     NULL | NULL   | YES  | BTREE      |         | 
| foos  |          1 | index_foos_on_name  |            1 | name        | A         |       19710 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Does anything like this exist for PostgreSQL?

I've tried \d at the psql command prompt (with the -E option to show SQL) but it doesn't show the information I'm looking for.

Update: Thanks to everyone who added their answers. cope360 gave me exactly what I was looking for, but several people chimed in with very useful links. For future reference, check out the documentation for pg_index (via Milen A. Radev) and the very useful article Extracting META information from PostgreSQL (via Michał Niklas).

Hearken answered 4/2, 2010 at 23:39 Comment(2)
Just to clarify: You want your program to be able to figure out, at runtime, which columns are indexed, right? As opposed to you the programming knowing.Inappropriate
Yes, correct. Ideally I want a SQL statement that lists ONLY the columns that the index is on. But I know PostgreSQL is more complicated than MySQL and the index could be on a function, etc.Hearken
K
298

Create some test data...

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

List indexes and columns indexed:

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.relkind = 'r'
    and t.relname like 'test%'
order by
    t.relname,
    i.relname;

 table_name | index_name | column_name
------------+------------+-------------
 test       | pk_test    | a
 test       | pk_test    | b
 test2      | uk_test2   | b
 test2      | uk_test2   | c
 test3      | uk_test3ab | a
 test3      | uk_test3ab | b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c

Roll up the column names:

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
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.relkind = 'r'
    and t.relname like 'test%'
group by
    t.relname,
    i.relname
order by
    t.relname,
    i.relname;

 table_name | index_name | column_names
------------+------------+--------------
 test       | pk_test    | a, b
 test2      | uk_test2   | b, c
 test3      | uk_test3ab | a, b
 test3      | uk_test3b  | b
 test3      | uk_test3c  | c
Kalgoorlie answered 6/2, 2010 at 13:17 Comment(14)
For anyone trying to find indexes in a populated database: this query works great, but change the and t.relname like 'test%' line to the table(s) you want, or erase that line completely to find all indexes in your db.Reinhardt
Could someone explain what relkind='r' means?Trustworthy
@Qwery, see the documentation for pg_class r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table.Kalgoorlie
is there a way to also tell about uniqueness of the key?Javierjavler
Does this give the columns in the correct order? How?Sino
And you can also use SELECT DISTINCT instead to avoid duplicates (in case you have several schemas in a Postgres DB with the same structure and you don't want to see the same entry listed for each tenant)Hem
@Trustworthy — To expand upon cope360's answer, the documentation for pg_class can be found at postgresql.org/docs/current/static/catalog-pg-class.htmlAlvis
How easy would it be to add a column for the type of index it is?Algonkian
to see the index uniqueness also select ix.indisuniqueLarrabee
@Sino the ANY makes the columns come out in a funky order. other solutions address this with a sub select and adding creating a sequence from indkeyBooma
In case you are only interested in one schema (like the default "public" for instance), probably better to add : and t.relnamespace = (select oid from pg_namespace where nspname = 'public')Breastplate
This query is not exhaustive, an index on a materialized_view won't be shown. valentin's answer willRonald
@Kalgoorlie Multi-column indexes column order is wrong in this solutionTombola
@AndreKR, no it does not list correct order of the columns. To get the correct ordering i used ORDER BY array_position(ix.indkey, a.attnum)Folk
B
252

PostgreSQL (pg_indexes):

SELECT * FROM pg_indexes WHERE tablename = 'mytable';

MySQL (SHOW INDEX):

SHOW INDEX FROM mytable;
Bystreet answered 10/2, 2017 at 3:15 Comment(3)
This is the most straightforward answer, and the most interesting in terms of answering the question "Is my column indexed?" PostgreSQL: SELECT COUNT(indexname) AS indexcount FROM pg_indexes WHERE tablename='mytablename' AND indexdef LIKE '%mycolumnname%' ; and verify indexcount>0. mySQL: SHOW INDEX FROM mytablename WHERE Column_name='mycolumnname' ; and verify result set not empty.Atheism
Although this is a very useful answer in terms of retrieving quick information about indexes it does not answer the original question because the pg_indexes view does not provide column names. postgresql.org/docs/current/view-pg-indexes.htmlUnifilar
Iterating on the answer, to filter out un-necessary information: SELECT tablename, indexname, indexdef FROM pg_indexes WHERE tablename <> 'pg_%' ORDER BY tablename, indexname;Unreserved
N
152

\d table_name shows this information from psql, but if you want to get such information from database using SQL then have a look at Extracting META information from PostgreSQL.

I use such info in my utility to report some info from db schema to compare PostgreSQL databases in test and production environments.

Neurotomy answered 5/2, 2010 at 7:3 Comment(3)
Your link on extracting meta information from Postgres is exactly what I was looking for! Using the tips in this thread and some digging I got pretty close to the query he uses in that post, but it's nice to have it all laid out like that.Hearken
I'm using AWS RDS PostgreSQL 9.6.5 and \d table does not show any indexes, however \di does show all indexes.Brabble
@HendyIrawan it can apparently be affected by other settings. Like I wonder if you had "tuples only" mode on (toggled by \t). With "tuples only" on, I don't get indexes from \d, with "tuples only" off, I do. This is with psql (PostgreSQL) 9.6.15.Quicksilver
I
78

Just do: \d table_name

But I'm not sure what do you mean that the information about columns is not there.

For example:

# \d pg_class
       Table "pg_catalog.pg_class"
     Column      |   Type    | Modifiers
-----------------+-----------+-----------
 relname         | name      | not null
 relnamespace    | oid       | not null
 reltype         | oid       | not null
 reloftype       | oid       | not null
 relowner        | oid       | not null
 relam           | oid       | not null
 relfilenode     | oid       | not null
 reltablespace   | oid       | not null
 relpages        | integer   | not null
 reltuples       | real      | not null
 reltoastrelid   | oid       | not null
 reltoastidxid   | oid       | not null
 relhasindex     | boolean   | not null
 relisshared     | boolean   | not null
 relistemp       | boolean   | not null
 relkind         | "char"    | not null
 relnatts        | smallint  | not null
 relchecks       | smallint  | not null
 relhasoids      | boolean   | not null
 relhaspkey      | boolean   | not null
 relhasexclusion | boolean   | not null
 relhasrules     | boolean   | not null
 relhastriggers  | boolean   | not null
 relhassubclass  | boolean   | not null
 relfrozenxid    | xid       | not null
 relacl          | aclitem[] |
 reloptions      | text[]    |
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)

It clearly shows which columns given index is on this table.

Illness answered 4/2, 2010 at 23:57 Comment(2)
I was hoping for something that will let me do all the indexes on a table but you're right, \d index_name does have the information. So I can look up the indexes on a table, then look up the details. By not showing the columns I mean that I looked at the SQL generated by \d table name and it is not obvious to me where the column list is coming from. I think it's being parsed out of the index definition, which I would prefer not to do.Hearken
I'm using AWS RDS PostgreSQL 9.6.5 and \d table does not show any indexes, however \di does show all indexes.Brabble
B
48

# \di

The easies and shortest way is \di, which will list all the indexes in the current database.

$ \di
                      List of relations
 Schema |            Name             | Type  |  Owner   |     Table     
--------+-----------------------------+-------+----------+---------------
 public | part_delivery_index         | index | shipper  | part_delivery
 public | part_delivery_pkey          | index | shipper  | part_delivery
 public | shipment_by_mandator        | index | shipper  | shipment_info
 public | shipment_by_number_and_size | index | shipper  | shipment_info
 public | shipment_info_pkey          | index | shipper  | shipment_info
(5 rows)

\di is the "small brother" of the \d command which will list all relations of the current database. Thus \di certainly stands for "show me this databases indexes".

Typing \diS will list all indexes used systemwide, which means you get all the pg_catalog indexes as well.

$ \diS
                                      List of relations
   Schema   |                   Name                    | Type  |  Owner   |          Table
------------+-------------------------------------------+-------+----------+-------------------------
 pg_catalog | pg_aggregate_fnoid_index                  | index | postgres | pg_aggregate
 pg_catalog | pg_am_name_index                          | index | postgres | pg_am
 pg_catalog | pg_am_oid_index                           | index | postgres | pg_am
 pg_catalog | pg_amop_fam_strat_index                   | index | postgres | pg_amop
 pg_catalog | pg_amop_oid_index                         | index | postgres | pg_amop
 pg_catalog | pg_amop_opr_fam_index                     | index | postgres | pg_amop
 pg_catalog | pg_amproc_fam_proc_index                  | index | postgres | pg_amproc
 pg_catalog | pg_amproc_oid_index                       | index | postgres | pg_amproc
 pg_catalog | pg_attrdef_adrelid_adnum_index            | index | postgres | pg_attrdef
--More-- 

With both these commands you can add a + after it to get even more information like the size - the disk space - the index needs and a description if available.

$ \di+
                                 List of relations
 Schema |            Name             | Type  |  Owner   |     Table     | Size  | Description 
--------+-----------------------------+-------+----------+---------------+-------+-------------
 public | part_delivery_index         | index | shipper  | part_delivery | 16 kB | 
 public | part_delivery_pkey          | index | shipper  | part_delivery | 16 kB | 
 public | shipment_by_mandator        | index | shipper  | shipment_info | 19 MB | 
 public | shipment_by_number_and_size | index | shipper  | shipment_info | 19 MB | 
 public | shipment_info_pkey          | index | shipper  | shipment_info | 53 MB | 
(5 rows)

In psql you can easily find help about commands typing \?.

Bently answered 22/1, 2018 at 12:51 Comment(1)
But it doesn't show the column names on which the indexes are created. The composite primary keys index has many columns and those cannot be seen.Liebig
M
21

Combined with others code and created a view:

CREATE OR REPLACE VIEW view_index AS 
SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
WHERE c.relkind = 'i'
    and n.nspname not in ('pg_catalog', 'pg_toast')
    and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
     n.nspname
    ,t.relname
    ,c.relname;
Madore answered 23/10, 2012 at 16:40 Comment(0)
K
14

Some sample data...

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c), constraint uk_test3ab unique (a, b));

Use pg_get_indexdef function:

select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test'::regclass;

                    pg_get_indexdef
--------------------------------------------------------
 CREATE UNIQUE INDEX pk_test ON test USING btree (a, b)
(1 row)


select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test2'::regclass;
                     pg_get_indexdef
----------------------------------------------------------
 CREATE UNIQUE INDEX uk_test2 ON test2 USING btree (b, c)
(1 row)


select pg_get_indexdef(indexrelid) from pg_index where indrelid ='test3'::regclass;
                      pg_get_indexdef
------------------------------------------------------------
 CREATE UNIQUE INDEX uk_test3b ON test3 USING btree (b)
 CREATE UNIQUE INDEX uk_test3c ON test3 USING btree (c)
 CREATE UNIQUE INDEX uk_test3ab ON test3 USING btree (a, b)
(3 rows)
Kalgoorlie answered 6/2, 2010 at 0:9 Comment(0)
R
10

RESULT OF QUERY:

table |     column     |          type          | notnull |  index_name  | is_index | primarykey | uniquekey | default
-------+----------------+------------------------+---------+--------------+----------+-   -----------+-----------+---------
 nodes | dns_datacenter | character varying(255) | f       |              | f        | f          | f         |
 nodes | dns_name       | character varying(255) | f       | dns_name_idx | t        | f          | f         |
 nodes | id             | uuid                   | t       | nodes_pkey   | t        | t          | t         |
(3 rows)

QUERY:

SELECT  
c.relname AS table,
f.attname AS column,  
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
f.attnotnull AS notnull,  
i.relname as index_name,
CASE  
    WHEN i.oid<>0 THEN 't'  
    ELSE 'f'  
END AS is_index,  
CASE  
    WHEN p.contype = 'p' THEN 't'  
    ELSE 'f'  
END AS primarykey,  
CASE  
    WHEN p.contype = 'u' THEN 't' 
    WHEN p.contype = 'p' THEN 't' 
    ELSE 'f'
END AS uniquekey,
CASE
    WHEN f.atthasdef = 't' THEN d.adsrc
END AS default  FROM pg_attribute f  
JOIN pg_class c ON c.oid = f.attrelid  
JOIN pg_type t ON t.oid = f.atttypid  
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
LEFT JOIN pg_index AS ix ON f.attnum = ANY(ix.indkey) and c.oid = f.attrelid and c.oid = ix.indrelid 
LEFT JOIN pg_class AS i ON ix.indexrelid = i.oid 

WHERE c.relkind = 'r'::char  
AND n.nspname = 'public'  -- Replace with Schema name 
--AND c.relname = 'nodes'  -- Replace with table name, or Comment this for get all tables
AND f.attnum > 0
ORDER BY c.relname,f.attname;
Reader answered 31/8, 2014 at 22:23 Comment(1)
nice one, however the "column" name for a column is a reserved word. IDEM for schema, should use column_nameInsignificant
P
8

This commands shows the view of tables variables, indexes and constraints too

=# \d table_name;

Example:

testannie=# \d dv.l_customer_account;
Pulchritude answered 9/6, 2016 at 7:42 Comment(0)
F
7

\d tablename shows the column names for me on version 8.3.8.

 "username_idx" UNIQUE, btree (username), tablespace "alldata1"
Fellowship answered 5/2, 2010 at 0:0 Comment(0)
I
5

The raw info is in pg_index.

Ingaingaberg answered 4/2, 2010 at 23:57 Comment(1)
Interesting. Specifically indkey: "This is an array of indnatts values that indicate which table columns this index indexes. For example a value of 1 3 would mean that the first and the third table columns make up the index key. A zero in this array indicates that the corresponding index attribute is an expression over the table columns, rather than a simple column reference"Hearken
T
5

When playing around with indexes the order of which columns are constructed in the index is as important as the columns themselves.

The following query lists all indexes for a given table and all their columns in a sorted fashion.

SELECT
  table_name,
  index_name,
  string_agg(column_name, ',')
FROM (
       SELECT
         t.relname AS table_name,
         i.relname AS index_name,
         a.attname AS column_name,
         (SELECT i
          FROM (SELECT
                  *,
                  row_number()
                  OVER () i
                FROM unnest(indkey) WITH ORDINALITY AS a(v)) a
          WHERE v = attnum)
       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.relkind = 'r'
         AND t.relname LIKE 'tablename'
       ORDER BY table_name, index_name, i
     ) raw
GROUP BY table_name, index_name
Ternary answered 29/7, 2016 at 12:10 Comment(3)
Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer.Tallage
the i for the ordinality is very slick. It ensures the columns are stated in the correct order.Booma
This was the only answer that worked for me. The column order is critical. (If you don't believe me, look for all people with a first name Frank in a phonebook.)Cultus
L
5

The accepted answer by @cope360 is good, but I wanted something a little more like Oracle's DBA_IND_COLUMNS, ALL_IND_COLUMNS, and USER_IND_COLUMNS (e.g., reports the table/index schema and the position of the index in a multicolumn index), so I adapted the accepted answer into this:

with
 ind_cols as (
select
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name,
    1 + array_position(ix.indkey, a.attnum) as column_position
from
     pg_catalog.pg_class t
join pg_catalog.pg_attribute a on t.oid    =      a.attrelid 
join pg_catalog.pg_index ix    on t.oid    =     ix.indrelid
join pg_catalog.pg_class i     on a.attnum = any(ix.indkey)
                              and i.oid    =     ix.indexrelid
join pg_catalog.pg_namespace n on n.oid    =      t.relnamespace
where t.relkind = 'r'
order by
    t.relname,
    i.relname,
    array_position(ix.indkey, a.attnum)
)
select * 
from ind_cols
where schema_name = 'test'
  and table_name  = 'indextest'
order by schema_name, table_name
;

This gives an output like:

 schema_name | table_name | index_name | column_name | column_position 
-------------+------------+------------+-------------+-----------------
 test        | indextest  | testind1   | singleindex |               1
 test        | indextest  | testind2   | firstoftwo  |               1
 test        | indextest  | testind2   | secondoftwo |               2
(3 rows)
Leveret answered 5/2, 2019 at 21:30 Comment(0)
B
3

If you want to preserve column order in the index, here's a (very ugly) way to do that:

select table_name,
    index_name,
    array_agg(column_name)
from (
    select
        t.relname as table_name,
        i.relname as index_name,
        a.attname as column_name,
        unnest(ix.indkey) as unn,
        a.attnum
    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.relkind = 'r'
        and t.relnamespace = <oid of the schema you're interested in>
    order by
        t.relname,
        i.relname,
        generate_subscripts(ix.indkey,1)) sb
where unn = attnum
group by table_name, index_name

column order is stored in the pg_index.indkey column, so I ordered by the subscripts from that array.

Bucaramanga answered 1/8, 2014 at 18:47 Comment(0)
J
1

How about a simple solution:

SELECT 
  t.relname table_name,
  ix.relname index_name,
  indisunique,
  indisprimary, 
  regexp_replace(pg_get_indexdef(indexrelid), '.*\((.*)\)', '\1') columns
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_class ix ON ix.oid = i.indexrelid
WHERE t.relname LIKE 'test%'

`

Juneberry answered 10/12, 2015 at 1:20 Comment(2)
Love this solution. Unfortunately it fails with indexes that have where clauses. (or other parenthesis)Booma
I changed to not skip parens at the beginning, and not capture parens in the middle, and drop everything after that. '^[^\)]*\(([^\)]*)\).*$'Booma
D
1

Please try the query below to drill down to required index's

Query as below -- i have tried this personally and use it frequently.

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' 
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner",
 c2.relname as "Table"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
      AND c2.relname like '%agg_transaction%' --table name
      AND nspname = 'edjus' -- schema name 
ORDER BY 1,2;
Deidredeific answered 22/8, 2016 at 12:43 Comment(0)
G
1

Similar to the accepted answer but having left join on pg_attribute as normal join or query with pg_attribute doesnt give indices which are like :
create unique index unique_user_name_index on users (lower(name))

select 
    row_number() over (order by c.relname),
    c.relname as index, 
    t.relname as table, 
    array_to_string(array_agg(a.attname), ', ') as column_names 
from pg_class c
join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%' 
join pg_class t on t.oid = i.indrelid
left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey) 
group by t.relname, c.relname order by c.relname;
Girandole answered 17/10, 2016 at 13:45 Comment(1)
good note, but how to get information about that "lower(column_name")"Hundley
C
1

Here's a function that wraps cope360's answer:

CREATE OR REPLACE FUNCTION getIndices(_table_name varchar)
  RETURNS TABLE(table_name varchar, index_name varchar, column_name varchar) AS $$
  BEGIN
    RETURN QUERY
    select
    t.relname::varchar as table_name,
    i.relname::varchar as index_name,
    a.attname::varchar 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.relkind = 'r'
    and t.relname = _table_name
order by
    t.relname,
    i.relname;
  END;
  $$ LANGUAGE plpgsql;

Usage:

select * from getIndices('<my_table>')
Calamint answered 12/11, 2016 at 9:45 Comment(1)
Didn't list parts of my indexes that use functions (e.g. "upper(field_name)").Ledet
L
1

I don't think this version exists on this thread yet: it provides both the list of column names along with the ddl for the index.

CREATE OR REPLACE VIEW V_TABLE_INDEXES AS

SELECT
     n.nspname  as "schema"
    ,t.relname  as "table"
    ,c.relname  as "index"
    ,i.indisunique AS "is_unique"
    ,array_to_string(array_agg(a.attname), ', ') as "columns"
    ,pg_get_indexdef(i.indexrelid) as "ddl"
FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid        = c.relnamespace
    JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
    JOIN pg_catalog.pg_class t ON i.indrelid   = t.oid
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE c.relkind = 'i'
      and n.nspname not in ('pg_catalog', 'pg_toast')
      and pg_catalog.pg_table_is_visible(c.oid)
GROUP BY
    n.nspname
    ,t.relname
    ,c.relname
    ,i.indisunique
    ,i.indexrelid
ORDER BY
    n.nspname
    ,t.relname
    ,c.relname;

I found that indexes using functions don't link to column names, so occasionally you find an index listing e.g. one column name when in fact is uses 3.

Example:

CREATE INDEX ui1 ON table1 (coalesce(col1,''),coalesce(col2,''),col3)

The query returns only 'col3' as a column on the index, but the DDL shows the full set of columns used in the index.

Lourielouse answered 2/2, 2017 at 19:47 Comment(0)
V
1

A little bit modified answer of @cope360:

create table test (a int, b int, c int, constraint pk_test primary key(c, a, b));
select i.relname as index_name,
       ix.indisunique as is_unique,
       a.attname as column_name,
from pg_class c
       inner join pg_index ix on c.oid=ix.indrelid
       inner join pg_class i on ix.indexrelid=i.oid
       inner join pg_attribute a on a.attrelid=c.oid and a.attnum=any(ix.indkey)
where c.oid='public.test'::regclass::oid
order by array_position(ix.indkey, a.attnum) asc;

This will show the index columns in correct order:

index_name      is_unique  column_name
pk_test         true       c
pk_test         true       a
pk_test         true       b
Vignette answered 11/11, 2018 at 19:41 Comment(1)
Using "left join pg_attribute" will also show indexes on computed columns, of course with a NULL column_name.Aletheaalethia
C
1
select t.relname as table_name, 
       i.relname as index_name, 
       array_position(ix.indkey,a.attnum) pos, 
       a.attname as column_name
from pg_class t
join pg_index ix on t.oid = ix.indrelid
join pg_class i on i.oid = ix.indexrelid
join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(ix.indkey)
where t.relkind = 'r'
and t.relname like 'orders'
order by t.relname, i.relname, array_position(ix.indkey,a.attnum)
Currajong answered 31/1, 2019 at 16:6 Comment(0)
J
0

@cope360 's excellent answer, converted to use join syntax.

select t.relname as table_name
     , i.relname as index_name
     , array_to_string(array_agg(a.attname), ', ') as column_names
from pg_class t
join pg_index ix
on t.oid = ix.indrelid
join pg_class i
on i.oid = ix.indexrelid
join pg_attribute a
on a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
where t.relkind = 'r'
and t.relname like 'test%'
group by t.relname
       , i.relname
order by t.relname
       , i.relname
;
Jasen answered 11/11, 2016 at 18:0 Comment(0)
H
0

Extend to good answer of @Cope360. To get for certain table ( incase their is same table name but different schema ), just using table OID.

select
     t.relname as table_name
    ,i.relname as index_name
    ,a.attname as column_name
    ,a.attrelid tableid

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.relkind = 'r'
    -- and t.relname like 'tbassettype'
    and a.attrelid = '"dbLegal".tbassettype'::regclass
order by
    t.relname,
    i.relname;

Explain : I have table name 'tbassettype' in both schema 'dbAsset' and 'dbLegal'. To get only table on dbLegal, just let a.attrelid = its OID.

Hectare answered 9/10, 2018 at 3:36 Comment(0)
S
0

Maybe you want to get the actual CREATE INDEX statements so that you can drop them and recreate later (which was my case) as part of an administrative process. In this case you can use pg_dump to only dump the post-data section and then grep 'CREATE INDEX' out of it.

PGPASSWORD=<pwd> pg_dump --host <host> --port <port> -U <user> -d <database> --section=post-data > post-data.sql
grep 'CREATE INDEX' postdata.sql > create_index.sql

This may be helpful because some indexes may be hard to recreate, such as those who use specific opclasses (e.g gin_trgm_ops) or something else that I usually have a hard time reconstructing.

Sabin answered 16/6, 2021 at 2:31 Comment(0)
C
0

Query fields and ensure order.most like mysql. @Cope360

select 
    c.tablename as table_name, 
    b.relname as index_name,
    generate_subscripts(a.indkey, 1) as seq_column,
    pg_get_indexdef(a.indexrelid, generate_subscripts(a.indkey, 1) + 1, TRUE) as colunm_name
from 
    pg_index a 
    inner join pg_class b on a.indexrelid = b.oid 
    inner join pg_indexes c on b.relname = c.indexname
    inner join pg_namespace d on b.relnamespace = d.oid and c.schemaname = d.nspname
where  c.schemaname = current_schema()
    and c.tablename like 'test%'
order by table_name,index_name,seq_column

Fields merged into arrays.

select 
    c.tablename as table_name, 
    b.relname as index_name,
    array(
        select pg_get_indexdef(a.indexrelid, columncount + 1, true)
        from generate_subscripts(a.indkey, 1) as columncount order by columncount 
        ) as column_names
from 
pg_index a 
    inner join pg_class b on a.indexrelid = b.oid 
    inner join pg_indexes c on b.relname = c.indexname
    inner join pg_namespace d on b.relnamespace = d.oid and c.schemaname = d.nspname
where  c.schemaname = current_schema()
    and c.tablename like 'test%'
order by table_name,index_name
Circumvent answered 27/10, 2023 at 9:16 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.