Как получить список индексированных колонок в PostgreSQL
Быстрый ответ
Для получения списка всех колонок с индексами примените следующий запрос:
SELECT
tbl.relname AS "table",
idx.relname AS "index",
col.attname AS "column"
FROM
pg_index i
JOIN
pg_attribute col ON col.attrelid = i.indrelid AND col.attnum = ANY(i.indkey)
JOIN
pg_class idx ON idx.oid = i.indexrelid
JOIN
pg_class tbl ON tbl.oid = i.indrelid
WHERE
tbl.relkind = 'r'
ORDER BY
"table",
"index";
Вы получите структурированный список таблиц, индексов и соответствующих колонок, организованных по индексам.
Тонкая настройка поиска в PostgreSQL
Если требуется осуществлять поиск только по определенным таблицам или исключить некоторые из них, добавьте условия в секцию WHERE
.
Чтобы исключить системные таблицы, примените следующую фильтрацию:
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
Визуализация
Представьте, что таблицы – это актёры (🏛), колонки – это их реплики, а индексы – это свет софитов (🔦).
Таблицы (🏛): [Актёр 1, Актёр 2⚡, Актёр 3, Актёр 4⚡, Актёр 5]
Софиты (🔦): Освещают Актёра 2 и Актёра 4
Колонки с индексами привлекают особое внимание:
🔦⚡ Актёр 2: Снаряжен для упрощения поиска!
🔦⚡ Актёр 4: Под светом для быстрого доступа!
Чтобы увидеть подробную информацию об индексах определенной таблицы, выполните:
SELECT * FROM pg_indexes WHERE tablename = 'your_table';
В результате вы увидите список колонок, освещенных "светом" индексов.
Работа с многоколоночными индексами
Если один индекс охватывает несколько колонок, чтобы объединить их, выполните следующий запрос:
SELECT
tbl.relname AS "table",
idx.relname AS "index",
STRING_AGG(col.attname, ', ') WITHIN GROUP (ORDER BY col.attnum) AS "columns"
FROM
...
Данный запрос создает упорядоченный список колонок с индексами, что необходимо при работе с многоколоночными индексами.
Овладевая продвинутым обзором индексов
Хотите освоить индексы на профессиональном уровне? Воспользуйтесь командной строкой psql
с ее продвинутыми возможностями для работы с PostgreSQL.
Команды \di+
и \diS
предоставляют полную информацию об индексах, включая размер и комментарии, при этом \diS
исключает системные индексы. Команды \d
доступны только в psql
.
Полезные материалы
- PostgreSQL: Documentation: 16: CREATE INDEX — Официальное руководство по созданию индексов.
- Index Maintenance – PostgreSQL wiki — Обширные знания сообщества о поддержании индексов в исправном состоянии.
- postgresql – How do I list all databases and tables using psql? – Database Administrators Stack Exchange — Лучшие практики сообщества по работе с PostgreSQL.