http://postgresql.ru.net/manual/indexes.html

Глава 11. Индексы.
Оглавление

11.1. Введение [ok]
11.2. Типы индексов [ok]
11.3. Многоколоночные индексы [ok]

11.4. Индексы и ORDER BY
11.5. Объединение нескольких индексов
11.6. Уникальные индексы
11.7. Индексы выражений
11.8. Частичные индексы
11.9. Оператор классов и семейств операторов (? Operator Classes and Operator Families)
11.10. Изучение использования индексов
Индексы - распространенный способ повышения производительности базы данных. Индекс позволяет серверу базы данных искать и получать конкретные строк гораздо быстрее, чем без них. Но индексы также добавляют накладные расходы в базу данных, поэтому их следует использовать с умом.

11.1. Введение
Предположим, что у нас есть таблица:

CREATE TABLE test1 (
    ID integer,
    content VARCHAR
); 

Выполняем запрос вида:
 SELECT content FROM test1 WHERE ID = constant;

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

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

Следующая команда может быть использована для создания индекса по столбцу ID:
 CREATE INDEX test1_id_index ON test1 (ID);
имя Test1_id_index может быть выбрано любое, но оно должно позволять вам запомнить, для чего этот индекс.

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

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

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

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

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

11.2. Типы индексов
PostgreSQL предлагает несколько типов индексов: B-tree, HASH, GiST и GIN. Каждый тип индекса используется свой алгоритм, который лучше всего подходит для различных типов запросов. По умолчанию, команда CREATE INDEX создает B-tree индексы, которые соответствуют наиболее распространенным ситуациям.

B-tree может обрабатывать запросы равенства и интервала данных, которые должны быть отсортированы в каком-нибудь порядке. В частности, планировщик запросов PostgreSQL будет рассматривать вопрос об использовании индекса B-tree, когда индексированный столбец участвует в сравнении с помощью одного из этих операторов:
<
<=
=
>=
>
Такие комбинации этик операторов как BETWEEN и IN, также могут быть реализованы с помощью индекса B-tree. Кроме того, условие IS NULL на индекс столбца может быть использован индексом B-tree.
Оптимизатор может использовать B-tree индексы для запросов с участием операторов LIKE и ~, если шаблон является константой и закреплён в начале строки (т.е. начало строки шаблона - константа) - например, col LIKE 'foo%' или col ~ '^Foo ', но не col LIKE '%bar'. Однако, если база данных не использует локализацию "C", то необходимо создать индекс со специальным классом оператора для поддержки индексации запросов совпадения с шаблонами (см. раздел 11.9). Кроме того, можно использовать индексы B-tree для ILIKE и ~*, но только если шаблон начинается не с букв, т.е. символов, которые не изменяются при преобразовании регистра upper/lower.

Hash-индексы могут обрабатывать только простые сравнения равенства. Планировщик запросов рассматривает вопрос об использовании данного индекса, когда индексированный столбец участвует в сравнении с помощью оператора "="'. (Hash-индексы не поддерживают поиска по IS NULL.) Следующая команда используется для создания hash-индекса:

CREATE INDEX name ON TABLE UNING hash (COLUMN); 

GiST-индекс - представляет собой некую инфраструктуру, в которой могут быть реализованы много различных стратегий индексирования . Таким образом, конкретные операторы, с которыми могут быть использованы GiST-индексы, варьируются в зависимости от стратегии индексирования (класса оператора). В качестве примера стандартная поставка PostgreSQL включает в себя классы операторов GiST для нескольких двумерных геометрических типов данных, которые поддерживают индексирование запросов этих операторов:
<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&
(См. раздел 9.11 о действиях данных операторов.) Много других классов оператор GiST доступны в Contrib коллекции или в качестве отдельных проектов. Для получения дополнительной информации см. Главу 51 .

GIN-индексы - "перевёрнутые" индексы, которые могут обрабатывать значения, содержащие более одного ключа, например, массивов. Как и GiST, GIN поддерживает множество различных пользовательских стратегий индексирования и конкретных операторов, с которыми GIN-индекс может быть использован в зависимости от стратегии индексирования. В качестве примера, стандартная поставка PostgreSQL включает в себя классы операторов GIN для одномерных массивов , которые поддерживают индексирование запросов с помощью этих операторов:
<@
@>
=
&&
(См. раздел 9.17 о действиях данных операторов.) Много других классов операторов GIN доступны в Contrib коллекции или в качестве отдельных проектов. Для получения дополнительной информации см. Главу 52 .

11.3. Индексы по нескольким колонками
Индекс может быть определен на более чем одном столбце таблицы. Например, если у вас есть таблица такого вида:

CREATE TABLE test2 (
    major INT,
    minor INT,
    name VARCHAR
);

В настоящее время только B-tree, GiST и GIN индексы поддерживают несколько колонок. В одно многоколоночном индексе может быть указано до 32 столбцов. (Этот лимит может быть изменен при сборке PostgreSQL; см. файл pg_config_manual.h.)

Многоколоночные индексы B-tree могут быть использованы с запросами, условия которых включают в себя любые из столбцов подобного индекса, но индекс является наиболее эффективным, когда Существуют ограничения на ведущую (крайнюю слева) колонку. Точное правило гласит, что ограничения равенства на ведущих колонках, плюс любые ограничения на неравенства в первой колонке, которая не имеет ограничений равенства, будет использоваться для ограничения части индекса, которая сканируется. Ограничения на столбцы справа от этих столбцов проверяются в индексе, поэтому они экономят надлежащие просмотры таблицы, но они не уменьшают ту часть индекса, которая должна быть отсканирована. Например, если индекс (A, B, C) и условие запроса,  WHERE а = 5 AND В>= 42 AND с <77 индекс должен быть отсканирован с первого вхождения C = 5 и B = 42 вверх до последней записи с C = 5. Индекс записи с C >= 77 будет пропущен, но они все равно должны быть для сканирования. Этот показатель в принципе может быть использован для запросов, которые имеют ограничения на B и/или C без ограничения на A, но весь индекс должны быть отсканирован, поэтому в большинстве случаев проектировщик предпочел бы последовательное сканирование таблицы по сравнению с использованием индекса.

Многоколоночный GiST-индекс может быть использован с запросом, условия которого включают любые из столбцов индекса. Условия на дополнительных столбцах индекса вернут индекс, но состояние на первом столбец является наиболее важным для определения того, насколько индекс необходимо сканировать. Индекс GiST будет относительно неэффективным, если его первый столбец имеет всего лишь несколько различных значений, даже если в дополнительных столбцах много различных значений.

Многоколоночный GIN-индекс может быть использован с запросом, условия которого включают любые из столбцов индекса. В отличие от B-tree или GiST, эффективность поиска одинакова, независимо от того, какой столбец(цы) индекса используется в условиях запроса.

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

Многоколоночные индексы следует использовать с осторожностью. В большинстве случаев, индекс по одной колонке, является достаточным и экономит пространство и время. Использование индексов более чем в три колонки вряд ли будет полезно, если только использование таблицы крайне стилизовано (?). Смотрите также раздел 11.5 с некоторыми обсуждениями достоинств различных конфигураций индекса.