Node INDEXES for PostgreSQL
[EN]
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
For each index, information about the table name, the size of the index on disk, and a list of columns on which the index is built is displayed.
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
Menu
Copy name - copy the index name to the clipboard
Copy full name - copy the full index name to the clipboard
Reindex - generate a command to rebuild the index
Get DDL - generate an index creation command
Create code [Drop] - generate a command to delete an index
Get DDL
SELECT pg_get_indexdef($$INDEXOID)
SIZE INDEX
select pg_size_pretty(pg_relation_size($$INDEXNAME)) "Size"
[RU]
Список индексов в выбранной схеме базы данных PostgreSQL. Список может быть отфильтрован вводом части имени таблиц в поле "Like name object"
По каждому индексу выводится информация о имени таблицы, размеру индекса на диске и список колонок по которым построен индекс.
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
Меню
Copy name - скопировать имя индекса в буфер обмена
Copy full name - скопировать полное имя индекса в буфер обмена
Reindex - сформировать команду на перестройку индекса
Get DDL - сформировать команду создания индекса
Create code [Drop] - сформировать команду удаления индекса
Get DDL
SELECT pg_get_indexdef($$INDEXOID)
SIZE INDEX
select pg_size_pretty(pg_relation_size($$INDEXNAME)) "Size"