PostgreSQL: индексы

Предположим, у нас есть таблица, подобная этой:

CREATE TABLE table1 (
    id integer,
    content varchar
);

и приложение выдает множество запросов в форме:

SELECT content FROM table1 WHERE id = constant;

Без предварительной подготовки системе придется сканировать всю таблицу table1, строка за строкой, чтобы найти все совпадающие записи. Если в table1 много строк и только несколько строк (возможно, ноль или одна), которые могут быть возвращены таким запросом, это явно неэффективный метод. Но если система получила указание поддерживать индекс в столбце id, она может использовать более эффективный метод поиска совпадающих строк. Например, ему может потребоваться пройти всего несколько уровней в глубину дерева поиска.

Аналогичный подход используется в большинстве научно-популярных книг: термины и концепции, которые часто ищут читатели, собраны в алфавитном указателе в конце книги. Заинтересованный читатель может относительно быстро просмотреть указатель и перейти к соответствующей странице(-ам), вместо того, чтобы читать всю книгу, чтобы найти интересующий материал. Подобно тому, как задача автора - предугадать элементы, которые читатели могут найти, так и задача программиста баз данных - предвидеть, какие индексы будут полезны.

Следующая команда может использоваться для создания индекса столбца id, как уже говорилось:

CREATE INDEX table1_id_index ON table1 (id);

Имя table1_id_index можно выбрать произвольно, но вы должны выбрать что-то, что позволит вам позже вспомнить, для чего был нужен индекс.

Чтобы удалить индекс, используйте команду DROP INDEX. Индексы можно добавлять и удалять из таблиц в любое время.

После создания индекса дальнейшее вмешательство не требуется: система обновит индекс при изменении таблицы и будет использовать индекс в запросах, если сочтет, что это будет более эффективно, чем последовательное сканирование таблицы. Но вам, возможно, придется регулярно запускать команду ANALYZE, чтобы обновлять статистику, чтобы планировщик запросов мог принимать обоснованные решения.

Индексы также могут использоваться командами UPDATE и DELETE с условиями поиска. Более того, индексы можно использовать при join поиске. Таким образом, индекс, определенный для столбца, который является частью условия join, также может значительно ускорить запросы с join.

Создание индекса для большой таблицы может занять много времени. По умолчанию PostgreSQL разрешает чтение (операторы SELECT) в таблице параллельно с созданием индекса, но записи (INSERT, UPDATE, DELETE) блокируются до завершения построения индекса. В производственной среде это часто недопустимо. Можно разрешить запись, происходящую параллельно с созданием индекса, но есть несколько предостережений.

После создания индекса система должна поддерживать его синхронизацию с таблицей. Это увеличивает накладные расходы на операции по манипулированию данными. Поэтому индексы, которые редко или никогда не используются в запросах, должны быть удалены.

---

Читайте также:

• Изоляция в базах данных
• Индекс базы данных
• Индекс базы данных: методы индексирования, приложения и ограничения