Включите исполнение JavaScript в браузере, чтобы запустить приложение.
20 янв 2025

Индексы в PostgreSQL

PostgreSQL помогает управлять реляционными базами данных (СУБД). Это надежный масштабируемый инструмент с открытым исходным кодом. В статье расскажем об индексах PostgreSQL, их типах, способах их создания и оптимизации использования для повышения производительности.

Индекс в PostgreSQL: что это такое?

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

Структура данных называется индексом, если она:

  • избыточна;
  • невидима для приложения;
  • разработана для ускорения отбора данных на основе определенных критериев.

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

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

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

Основные типы индексов в PostgreSQL

PostgreSQL поддерживает несколько видов индексов, каждый из которых адаптирован для конкретных случаев использования и типов данных. К ним относятся:

  • индексы в виде B-дерева — наиболее распространенный тип, который подходит для запросов на равенство и диапазон;
  • хэш-индексы — нужны для простого сравнения на равенство;
  • GiST (обобщенное дерево поиска) — гибкий формат, который поддерживает множество пользовательских типов данных и сложных запросов;
  • GIN (обобщенный инвертированный индекс) — предназначен для индексации составных значений, таких как массивы или полнотекстовый поиск;
  • BRING (индексы диапазона блоков) — эффективен для больших таблиц с естественной сортировкой данных;
  • SP-GiST (обобщенное дерево поиска с пространственным разделением) — оптимизирован для данных, которые могут быть разделены на непересекающиеся области. Также полезен для многомерных данных.

B-дерево. Чаще всего используется в PostgreSQL. Индексы хорошо подходят для широкого спектра запросов, включая запросы на равенство и диапазон. Они поддерживают отсортированные данные, что делает их эффективными для извлечения упорядоченной информации.

Пример создания B-дерева:

CREATE INDEX idx_employee_name ON employees (name)
sql

Использование:

SELECT * FROM employees WHERE name = 'Ivan Solopov'
sql

Для команд оператора сопоставления с шаблоном LIKE и ~ разработчик запроса тоже может использовать B-дерево. 

Пример:

column_name LIKE 'abb%'  

column_name ~ '^abb' 

column_name LKE 'abc%' 
sql

B-дерево — хороший вариант, если вы только начали использовать индекс для оптимизации базы данных.Если мы используем команду CREATE INDEX без описания какого-либо типа индекса, то PostgreSQL по умолчанию использует индексный тип B-tree. Это наиболее подходящий и распространенный тип запросов.

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

Синтаксис:

CREATE INDEX idx_hash ON books USING HASH (author)
sql


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

Создание индекса GiST для геометрических данных:

CREATE INDEX idx_location_geom ON locations USING gist (geom)
sql

Использование индекса GiST в запросе:

SELECT * FROM locations WHERE ST_DWithin(geom, ST_MakePoint(-77.0364, 38.8951)::geography, 1000)
sql

GIN. Этот тип применяют, если есть несколько значений range type, array, jsonb и hstore, хранящихся в одном столбце. 

Пример:

SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('database')
sql

BRIN. Подходят для больших таблиц, где данные упорядочены естественным образом или сгруппированы в кластеры. Хранят сводную информацию о диапазонах блоков. Это делает их очень компактными и подходящими для сценариев, где не требуется точная индексация.

Как работают индексы в PostgreSQL

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

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

Предположим, что таблица (с 5 000 000 записями) содержит схему:

id (Primary key) - Unsigned INT - 4 bytes

firstName - Char(50) - 50 bytes

lastName - Char(50) - 50 bytes

emailAddress - Char(100) - 100 bytes
sql

Сортированные и несортированные поля (без индексов):

For 5,000,000 records,

Record length R = 204 bytes (сумма всех полей)

Block size B = 1,024 bytes (размер блока по умолчанию)
sql

Коэффициент блокировки таблицы (количество записей в блоке) будет равен:

bfr = B/R = 1024/204 = 5 records per block
sql

Общее количество блоков, необходимых для хранения таблицы, равно:

N = r/bfr = 5 000 000/5 = 1 000 000 блоков
bash

Если нужно выполнить поиск с помощью 'id', линейно придется пройти в среднем через (N+ 1)/2 = 500 000 блоков.

Схема индекса хранит только фактическое значение и указатель, поэтому она будет выглядеть так:

firstName - Char(50) - 50 bytes

record pointer - 4 bytes
sql

Если выполнить такие же вычисления, мы получим:

bfr = (B/R) = 1024/54 = 18

N = (r/bfr) = 5000000/18 = 277,778 blocks
sql

Теперь, когда мы ищем по имени, можно использовать индекс. Все сортируется и сохраняется в словарном порядке, например:

Анна
Алиса
Алина
Борис
Белла
Бронислав
Владимир
Виктория
Валерия

Теперь нужно выполнить бинарный поиск по строкам. Мы будем считывать в среднем по журналу (277778). Всего получается 19 блоков. Плюс еще 1 блок, используя указатель, чтобы получить фактическую запись. В общей сложности их 20.

Создание индексов: базовые и продвинутые команды

Можно ускорить выполнение с помощью команды SELECT, а также замедлить ввод данных с помощью UPDATE и INSERT. Индексы могут создаваться или удаляться без влияния на данные.

Для создания индекса используется инструкция CREATE INDEX. Она позволяет присвоить индексу имя, указать таблицу или столбцы, которые надо индексировать. Можно указать, в каком порядке будет располагаться index— в порядке возрастания или убывания.

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

Команда DROP используется для удаления текущего индекса. LIST помогает перечислить все индексы в базе данных PostgreSQL. Index on Expression используется для указания индекса, основанного на выражениях. Partial index применяется для отображения использования частичных индексов. Re-index помогает перестроить один или несколько индексов. Multicolumn Indexes подходят для отображения использования многоколоночных индексов для улучшения запросов с помощью нескольких условий в предложении WHERE.

Когда стоит использовать индексы и какие типы данных индексировать

Будут ли индексы полезны для конкретного запроса или нет, в основном зависит от того, о каком типе запроса идет речь. Неудивительно, что они наиболее полезны для коротких запросов. Рассмотрим несколько важных правил при работе с index.

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

Индексируйте столбцы, по которым выполняется фильтрация. Первичные ключи индексируются Postgres автоматически, а внешние ключи и другие столбцы — нет.

Рассмотрим простую таблицу users, содержащую всего два столбца:

CREATE TABLE users (

    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    email text NOT NULL

);

INSERT INTO users (email)

SELECT n || '@gmail.com' FROM generate_series(1, 100000) AS n;

ANALYZE users;
sql

Если мы хотим найти пользователя по адресу электронной почты, применяем запрос:

EXPLAIN (ANALYZE, BUFFERS) 

SELECT id, email FROM users WHERE email = '56789@gmail.com';

------------------------------------------------------------

Seq Scan on users  (cost=0.00..1887.00 rows=1 width=23) (actual time=21.498..34.505 rows=1 loops=1)

  Filter: (email = '56789@gmail.com'::text)

  Rows Removed by Filter: 99999

  Buffers: shared hit=637

Planning Time: 0.113 ms

Execution Time: 34.539 ms
sql

Теперь добавим индекс в столбец электронной почты:

CREATE INDEX users_email ON users(email);

EXPLAIN (ANALYZE, BUFFERS) 

SELECT id, email FROM users WHERE email = '56789@gmail.com';

------------------------------------------------------------

Index Scan using users_email on users  (cost=0.42..3.44 rows=1 width=23) (actual time=0.076..0.079 rows=1 loops=1)|

  Index Cond: (email = '56789@gmail.com'::text) 

Buffers: shared hit=4

Planning Time: 0.219 ms

Execution Time: 0.116 ms
sql

Из планов запросов видно, что использование индекса в 150 раз эффективнее (4 блока данных вместо 637) и в 100 раз быстрее (всего 0,335 мс вместо 34,652мс). Это существенная разница. Получается, что чем меньше доля отфильтрованных строк, тем меньше разница в индексе.

Индексируйте только те данные, которые нужны для поиска. Если у вас есть часть таблицы, которую вы редко просматриваете и почти всегда отфильтровываете, от ее индексации мало пользы. Пример — таблица, содержащая данные, которые были автоматически удалены. В запросах обычно указывается, что WHERE deleted_at IS NULL.

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

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

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

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

Ниже приведен пример сканирования индекса с использованием нашей тестовой таблицы:

db1=# EXPLAIN (ANALYZE)

      SELECT * FROM person

      WHERE age = 20

      AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;

                                                            QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------

Index Scan using idx_person_age_date_active on person  (cost=0.56..8.58 rows=1 width=126) (actual time=0.039..0.040 rows=1 loops=1)

  Index Cond: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone))

Planning Time: 0.190 ms

Execution Time: 0.064 ms

(4 rows)
sql

Поддержка уникальности данных с помощью индексов

UNIQUE index используется для поддержки уникальности значений в одном или нескольких столбцах во всех строках таблицы. Это поможет сохранить целостность данных и предотвратить дублирование записей.

Синтаксис:

CREATE UNIQUE INDEX index_name

ON table_name(column_name, [...]);
sql

Пример UNIQUE index для столбца первичного ключа и столбца с уникальным ограничением:

CREATE TABLE employees (

    employee_id SERIAL PRIMARY KEY,

    first_name VARCHAR(255) NOT NULL,

    last_name VARCHAR(255) NOT NULL,

    email VARCHAR(255) UNIQUE

);
sql

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

SELECT 

    tablename, 

    indexname, 

    indexdef 

FROM 

    pg_indexes 

WHERE 

    tablename = 'employees';
sql

Индексы для полнотекстового поиска

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

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

Синтаксический анализ и нормализация запросов. Поисковые запросы также преобразуются в формат tsquery, который представляет собой логическую комбинацию поисковых запросов. Этот процесс аналогичен нормализации текста. Он включает в себя разбиение на элементы и добавление логических операторов, таких как AND, OR и NOT.

Индексация. Чтобы ускорить операции полнотекстового поиска, PostgreSQL позволяет создавать индексы для столбцов — tsvector. Эти tsvector indexes хранят предварительно обработанные текстовые данные, обеспечивая быстрый и эффективный поиск.

Поиск. PostgreSQL предоставляет различные функции и операторы для выполнения полнотекстового поиска в текстовых данных с использованием значений 'tsvector' и 'tsquery'. Эти функции могут использоваться в SQL-запросах для фильтрации и ранжирования результатов на основе их релевантности.

Проблемы индексации больших объемов данных

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

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

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

В PostgreSQL есть несколько инструментов для обнаружения неиспользуемых индексов.

  • Запрос индексов pg_stat_user_indexes. Системное представление pg_stat_user_indexes содержит статистику использования индексов в текущей базе данных. Запросив это представление, вы можете определить индексы с низким или нулевым количеством использований. Такой запрос возвращает индексы, которые не проверялись с момента последнего сброса статистики. Однако низкий уровень использования не обязательно означает, что индекс не используется. Поэтому иногда нужен более тщательный анализ.
  • Анализ планов запросов. Используйте функции EXPLAIN и EXPLAIN ANALYZE. Если индекс не используется в планах выполнения запросов, он может быть удален.
EXPLAIN SELECT * FROM table_name WHERE indexed_column = 'value'
sql
  • Инструменты мониторинга. Сторонние инструменты мониторинга pg_stat_statements, pg_qual stats или pg_activity могут предоставить информацию о закономерностях использования индекса с течением времени. Эти инструменты предоставляют более широкие возможности анализа, чем встроенные представления PostgreSQL.

Удаление index:

1. Оцените влияние

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

2. Создайте резервную копию

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

3. Удалите неиспользуемые индексы

Удалите идентифицированные неиспользуемые индексы с помощью инструкции DROP INDEX:

DROP INDEX index_name
sql

4. Следите за производительностью

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

Заключение

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