Indexes built on a PostgreSQL table

The node displays a list of indexes built on the columns of the selected PostgreSQL table.

PostgreSQL Table - index list.jpg

  • Copy name - copy the index name to the clipboard

  • Copy full name - copy the full name of the index to the clipboard

  • Reindex - generate command code for index rebuilding

  • Get DDL - generate command code to create an index

  • Create code [Drop] - generate a command code for deleting an index

Child nodes
  • Column - the names of the columns on which the index is built

  • Primary - flag indicating that the index is the primary key of the table

  • Unique - a pointer to whether the index is unique

  • Clustered - if true, the table was last clustered at this index

  • Valid - if true, the index can be used in queries.

  • Condeferrable - is the constraint deferrable?

  • Condeferred - is the constraint deferred by default?

  • Replident - if true, this index is selected as the "replica ID"

select relname, oid,  indisprimary, indisunique,
       indisclustered, indisvalid, pg_get_indexdef, 
       pg_get_constraintdef, contype,               
       condeferrable, condeferred, indisreplident, 
       string_agg(pg_get_indexdef_, ',') "col_lict"
  from (    
SELECT c2.relname, 
       pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) "pg_get_indexdef", 
       pg_catalog.pg_get_constraintdef(con.oid, false), 
       pg_catalog.pg_get_indexdef(c2.oid, (information_schema._pg_expandarray(i.indkey)).n, false) "pg_get_indexdef_" 
  FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i  
    LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = $$TABLE_OID AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname) a
group by relname, oid,  indisprimary, indisunique, 
  indisclustered, indisvalid, pg_get_indexdef, pg_get_constraintdef, 
  contype, condeferrable, condeferred, indisreplident, reltablespace