Индексирование

Индексирование (index) — это специальная структура, которая хранит ссылки на данные в БД в определенном порядке. Используется для ускорения работы запросов.

Плюсы:

1. Ускорение запросов(select, join, order by, ...)
2. Уменьшает нагрузку на частые выборки данных

Минусы:

1. Занимает место на диске
2. Замедляет работу DML (insert, update, delete), так как при манипулировании данными индексы тоже должны обновляться.

Общий вид:

create index ВИД Имя on ИДТ ТИП(ИДС)

Пример:

create index ind632 on gr632(фамилия)

Виды:

1. Одинарный - индекс на один столбец Общий вид:

create index ВИД Имя on ИДТ ТИП(ИДС)

Пример:

create index ind632 on gr632(фамилия)

2. Составной - создание индекса на несколько столбцов(1 таблицы)

Общий вид:

create index ВИД Имя on ИДТ ТИП(ИДС,ИДС,ИДС)

Пример:

create index ind632 on gr632(фамилия,имя)

3. Уникальный индекс — индекс, который создаётся уникальным (то есть в столбец нельзя добавить строки с одинаковыми значениями).

Общий вид:

create unique index Имя on ИДТ(ИДС)

Пример:

create unique index gr632inq on gr632(возраст)

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

Общий вид:

create index Имя on ИДТ(АГР-Ф(ИДС))

Пример:

create index ind632vir on gr632(фамилия)

Типы index:

1. B-tree (default)

• Самый частый.
• Хорошо работает с операторами «больше», «меньше», равенством и between.

create index индф on ИДТ(ИДС)

2. Hash

• Индекс по хэшу
• Подходит только для равенства.
• Работает в равенствах быстрее, чем B-tree.

Общий вид:

create index ИНДФ on ИДТ using hash(ИДС)

Пример:

create table gr632hash on gr632 using hash(id)

3. GIN — используется для поиска по массивам или полнотекстового поиска, лучше работает по множеству значений.

1. По массивам

create index ИНДФ on ИДТ using GIN(ИДС)

Пример:

create index gr632_GIN_1 on gr632 using GIN("Дата")

2. По полнотекстовому

create index ИНДФ on ИДТ using GIN(to_tsvector('знач', ИДС))

Пример:

create index gr632_GIN_1 on gr632 using GIN(to_tsvector('Иванов', Фамилия))

4. GiST

• Более гибкий, чем GIN.
• Используется чаще всего для геоданных.

Общий вид: create index ИНДФ on ИДТ using GIst(ИДС)

Пример:

create index gr632_GIN_1 on gr632 using Gist('Иванов', Фамилия)

4. Brin

Хранит в себе не ссылку на каждую строку, а диапазоны значений.

Общий вид:

create index ИНДФ on ИДТ using brin(ИДС)

Пример:

create index gr632_GIN_1 on gr632 using brin('Иванов', Фамилия)

Индексы хранятся в спец. таблицы pg_indexes

Для того чтобы посмотреть есть ли на таблице индексы делается запрос

select * from pg_indexes where tablename = ''

Для проверки работы индекса используется explain explain — не выполняет запрос, но выводит план выполнения запроса analyze — позволяет увидеть реальное время

create index p_index on p (название);
explain analyse select название from p;