Топ-100
 
CerebroSQL

INDEX node for PostgreSQL

List of indexes in the selected PostgreSQL database schema. The list can be filtered by entering part of the table name in the " Like name object " field

PostgreSQL index list in schema.jpg

For each index, information is displayed on  table name, index size on disk and list of columns on which the index is built

Menu
  • Copy name - copy index name to clipboard

  • Copy full name - copy full index name to clipboard 

  • Reindex - generate a command to rebuild the index

  • Get DDL - generate an index creation command

  • Create code [Drop] - generate an index drop command

Get DDL

SELECT pg_get_indexdef($$INDEXOID)

SIZE INDEX

select pg_size_pretty(pg_relation_size($$INDEXNAME)) "Size"

Index list

select a."Name",
       a."TabName",
       a."Schema",
       a.oid,
       string_agg(pg_get_indexdef_, ',') "col_lict"
from (
SELECT c.relname as "Name", 
       c2.relname as "TabName",
       ns.nspname "Schema",
       c.oid, 
       pg_catalog.pg_get_indexdef(c.oid, (information_schema._pg_expandarray(i.indkey)).n, false) "pg_get_indexdef_" 
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
     LEFT JOIN pg_catalog.pg_namespace ns ON c2.relnamespace = ns.oid 
WHERE c.relkind IN ('i')
      AND n.nspname ='audit'
) a
group by a."Name",
          a."TabName",
          a."Schema",
          a.oid
order by 1