Как получить список индексированных колонок в 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.

Полезные материалы

1. PostgreSQL: Documentation: 16: CREATE INDEX — Официальное руководство по созданию индексов.
2. Index Maintenance – PostgreSQL wiki — Обширные знания сообщества о поддержании индексов в исправном состоянии.
3. postgresql – How do I list all databases and tables using psql? – Database Administrators Stack Exchange — Лучшие практики сообщества по работе с PostgreSQL.