PostgreSQL | Как создать индекс?

PostgreSQL | Как создать индекс?

В PostgreSQL для создания индекса таблицы (и её столбцов) предусмотрен оператор CREATE INDEX.

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

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

 

Создание индекса, который не блокирует таблицу на вставку, удаление или изменение данных в PostgreSQL

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

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

 

Когда нужно создавать индекс?

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

В любой СУБД, работающей по стандарту SQL, есть понятие «ограничения«. То есть можно сделать так, чтобы значения в столбцах таблицы «А» могли быть только из диапазона значений столбца таблицы «Б«. Это классическое ограничение внешнего ключа, который ссылается на первичный ключ. Считайте это некоторой формой безопасного ввода данных или их обновления. Система сама будет за вас делать эти проверки.

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

 

Для каких ещё столбцов таблицы нужно создавать индекс в PostgreSQL?

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

Повторение данных не обязательно будет отсортированным и следовать друг за другом.

Чередование данных не обязательно будет в каком-то определённом месте таблицы. Как правило, чередование данных будет размазано по всей таблице.

Пример. Представьте себе столбец, в котором чередуются и повторяются имена людей: Вася, Петя, Маша, Дима, Петя, Маша, Петя, Вася, Коля, Маша, Петя, Петя, Дима и так далее. В этом случае при поиске записей содержащих определённое имя в первую очередь будет использоваться индекс (если он есть), а не последовательное сканирование всей таблицы по этому столбцу. Последовательное сканирование всей таблицы от начала и до конца будет ОЧЕНЬ дорогостоящим занятием — очень долгим. Лучше переплатить за мегабайты для индекса, чем жертвовать скоростью для работы приложения.

 

Какой параметр конфигурационного файла влияет на быстрое создание индекса в PostgreSQL?

Файл конфига PostgreSQL находится в директории «/etc/postgresql/15/main» (15 — это версия СУБД, у вас может быть другая), если у нас операционная система Debian (Linux).

В нём есть параметр «maintenance_work_mem«. Он задаёт максимальный объём памяти для операций обслуживания БД:

  1. VACUUM
  2. CREATE INDEX
  3. ALTER TABLE ADD FOREIGN KEY

Если это значение задаётся без единиц измерения, оно считается заданным в килобайтах. Значение по умолчанию — 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше параметра «work_mem«. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии.

Для таблиц в сотни миллионов записей это значение нужно увеличить. Расчёт тут простой. Одна ячейка с типом данных «bigint» занимает данных на 8 байт. Если у вас в базе 100 миллионов записей с этим типом данных, то это уже 800 миллионов байт — это 763 мегабайта. Это я не учитываю все мета-данные, которые дополнительно обслуживают этот объём.

800 миллионов байт в мегабайтах - PostgreSQL
800 миллионов байт в мегабайтах — PostgreSQL

Понятное дело, что с выделенной оперативной памятью в 64 мегабайта вы будете строить индекс дольше, чем с оперативной памятью в 1024 мегабайта. Если ваши железки сервера позволяют выдавать такой объём, то нужно его использовать. Если вы часто меняете конфигурацию БД и тестируете ускорения запросов индексами, то на больших данных вы постоянно будете создавать множество индексов, для оптимальной работы приложения. Чем быстрее вы создаёте индекс, тем быстрее проверяете свои гипотезы, тем быстрее получение качественного результата.

 

Большой файл индекса — это плохо!

Если мы строим индекс по всему столбцу, то в первые дни жизни нашей базы данных всё хорошо. Запросы на выборку выполняются быстро — замедлений не наблюдается.

И вот в один прекрасный день приложение начинает подтормаживать. Ответы становятся дольше.

Причиной торможения может стать большой размер файла индекса. Если физический сервер использует HDD диски хранения и размер файла индекса более 1 гигабайта, то чтение нужного места индекса будет занимать какое-то продолжительное время.

Как ускорить работу?

Если есть возможность, то нужно вместо одного большого файла индекса столбца попытаться сделать несколько небольших файлов индексов для этого же столбца. Проще всего это понять на внешних ключах. Представим, что в таблице «Б» 250 миллионов записей. В таблице «Б» есть внешний ключ на таблицу «А». Условимся, что уникальных повторяющихся внешних ключей около 10 миллионов в таблице «Б». Вместо того, чтобы хранить один огромный индекс столбца на 250 миллионов ячеек, можно раскидать его на 20 небольших индексов, где указываются диапазоны внешних ключей по 500 тысяч уникальных вариантов. Этот процесс ещё называют партицированием индекса (разделением на части).

Как это может выглядеть?

Вместо индекса:

CREATE INDEX CONCURRENTLY имя_индекса_по_столбцу ON таблица_Б (имя_столбца);

Сделать несколько подобных:

CREATE INDEX CONCURRENTLY имя_индекса_по_столбцу ON таблица_Б (имя_столбца)
WHERE имя_столбца >= 0 AND имя_столбца < 500000;

CREATE INDEX CONCURRENTLY имя_индекса_по_столбцу ON таблица_Б (имя_столбца)
WHERE имя_столбца >= 500000 AND имя_столбца < 1000000;

CREATE INDEX CONCURRENTLY имя_индекса_по_столбцу ON таблица_Б (имя_столбца)
WHERE имя_столбца >= 1000000 AND имя_столбца < 1500000;

... и так далее.

Такие индексы будут занимать существенно меньше места на диске (каждый по отдельности), но суммарно это может превысить один общий. Это не критично, так как нам важна скорость, а не объём.

Теперь встроенный оптимизатор запросов будет точно различать диапазоны столбца и выбирать правильный индекс, когда в запросе на выборку вы будете использовать искомое значение по этому столбцу.

За уменьшение размера индекса отвечает предложение WHERE, которое позволяет ускорить работу приложения. Вам главное не пропустить тот момент, когда внешние ключи выпадут из последнего диапазона индекса. Но это уже другая история.

 

Информационные ссылки

Официальный сайт WEB-оболочки pgAdminhttps://www.pgadmin.org

Официальный сайт СУБД PostgreSQLhttps://www.postgresql.org

Создание индекса — Команда CREATE INDEXhttps://postgrespro.ru/docs/postgresql/14/sql-createindex

Удаление индекса — Команда DROP INDEXhttps://postgrespro.ru/docs/postgresql/14/sql-dropindex

Представление pg_indexeshttps://postgrespro.ru/docs/postgresql/14/view-pg-indexes