Можно создать частичный индекс по условию, который покрывает только записи, удовлетворяющие определенному условию WHERE.
Например, можно исключить из индекса по внешнему ключу (FK) значения NULL:
CREATE INDEX fk_not_null ON pgconf(fk_id)
WHERE fk_id IS NOT NULL;При этом работа индекса не ускоряется, так как СУБД автоматически оптимизирует выполнение запроса. Однако, это позволит уменьшить размер индекса, что на больших объемах данных также может положительно сказаться на производительности. Поиск в индексе размером 15 ГБ выполняется быстрее, чем в индексе на 200 ГБ.
Преимущества:
- Уменьшение размера индекса: Индекс включает только необходимые записи, что уменьшает его общий объём.
- Ускорение запросов: Частичный индекс может улучшить производительность запросов, особенно если он используется для выборки узко определённых данных.
- Оптимизация операций обновления: Поскольку индекс обновляется только для определённых строк, уменьшаются накладные расходы на запись и обновление данных.
Недостатки:
- Ограниченная применимость: Частичные индексы не всегда подходят, особенно для данных с высокой селективностью, где обычные индексы будут более эффективны.
- Сложность настройки: Необходимо тщательно выбирать условия
WHERE, чтобы получить максимальную пользу от частичного индекса. Неправильный выбор условий может привести к ухудшению производительности.
Когда использовать частичные индексы:
- Когда необходимо уменьшить размер индекса за счёт исключения ненужных записей, что позволяет сэкономить место на диске и ускорить операции поиска.
- Когда таблица содержит большое количество записей с одинаковыми значениями (низкая селективность), и вам нужно индексировать только те записи, которые имеют уникальные или более специфичные значения.
- В ситуациях, когда индекс нужен для работы с данными, которые соответствуют определённому условию, например, только “необработанные” записи.
Таблицы с колонкой статуса
Часто в приложениях есть таблицы, которые содержат колонку статуса (state). Обычно статус разделяет записи на “обработанные” и “необработанные”. Индекс нам часто нужен именно по необработанным данным. Создавая частичный индекс только по необработанным данным, мы можем ускорить выполнение запроса и уменьшить размер индекса.
Возьмем типичную табличку, в которой есть какие-то статусы мы хотим находить данные по этому статусу.
Часто появляется желание сделать индекс по полю статуса:
Но по факту мы индексируем поле, которое имеет небольшую селективность. Такой индекс не эффективный.
Хороший вариант в данном случае:
Почти идеальный:
Идеальный. Совмещаем и составной индекс и частичный.
Мета информация
Область:: 00 PostgreSQL
Родитель:: Индекс в PostgreSQL, Оптимизация SQL запросов в PostgreSQL
Источник::
Создана:: 2024-10-21
Автор::