top of page
Волнистый абстрактный фон

INDEX node for PostgreSQL

CerebroSQL

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

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



SIZE INDEX


Index list
 

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

SELECT pg_get_indexdef($$INDEXOID)

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         

bottom of page