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

CREATE TABLE test1 (
    id integer,
    content varchar
);

и приложение выполняет много подобных запросов:

SELECT content FROM test1 WHERE id = константа;

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

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

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

CREATE INDEX test1_id_index ON test1 (id);

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

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

Когда индекс создан, никакие дополнительные действия не требуются: система сама будет обновлять его при изменении данных в таблице и сама будет использовать его в запросах, где, по её мнению, это будет эффективнее, чем сканирование всей таблицы. Вам, возможно, придётся только периодически запускать команду ANALYZE для обновления статистических данных, на основе которых планировщик запросов принимает решения. Ð’ Главе 14 вы можете узнать, как определить, используется ли определённый индекс и при каких условиях планировщик может решить не использовать его.

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

Создание индекса для большой таблицы может занимать много времени. По умолчанию Postgres Pro позволяет параллельно с созданием индекса выполнять чтение (операторы SELECT) таблицы, но операции записи (INSERT, UPDATE и DELETE) блокируются до окончания построения индекса. Для производственной среды это ограничение часто бывает неприемлемым. Хотя есть возможность разрешить запись параллельно с созданием индексов, при этом нужно учитывать ряд оговорок — они описаны в подразделе Неблокирующее построение индексов.

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