Почему PostgreSQL тормозит: индексы и корреляция данных

Содержание

"Хочешь ускорить запросы, построй индекс" – классический первый шаг по увеличению производительности в PostgreSQL. Вот только на практике можно встретить ситуацию, когда индексы в PostgreSQL есть, но тормоза никуда не делись. Не все индексы являются эффективными. Одна из возможных причин тормозов индексов – это отсутствие корреляции данных. Давайте сегодня поговорим о пенальти, которые дают расположение данных на производительность – почему это происходит и как это можно предотвратить.

Корреляция между физическим и логическим порядком значений

Корреляция между физическим и логическим порядком значений

Как можно идентифицировать равномерные и неравномерные данные

В системном представлении pg_stats для каждой таблицы есть поле correlation. В документации сказано, что это "статистическая корреляция между физическим порядком строк и логическим порядком значений столбца".

Давайте создадим таблицу:

Как мы видим, физическое распределение по полю corr происходит по возрастанию, а по полю uncorr – совсем рандомно:

Давайте договоримся называть данные, у которых correlation близка к 1 или -1 – равномерными данными. А данные, у которых корреляция близка к нулю – неравномерными.

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

Перед тем, как двигаться дальше, давайте вспомним о базовых вещах насчёт индексов:

Данные и индексы PostgreSQL хранит в блоках, и для того, чтобы прочитать или записать одну строку из блока, PostgreSQL прочитает или запишет блок полностью (с оговоркой).

Любое чтение и запись блока происходит через Буферный менеджер (Buffer Manager), который аллоцирует блоки в Буферном кэше (Buffer pool). Чтение или запись приводит к тому, что клиентскому запросу (бакенду), выполняющему запрос, необходимо высвободить место в буферном кэше. Если приходится вытеснять "грязную" (не синхронизированную с диском страницу) бакенду придется записать страницу на диск.

B-Tree хранит данные в отсортированном виде, где каждый элемент индекса указывает на ctid (физическое расположение данных). Значение ctid состоит из двух цифр: Block и Number. Тут Block – это номер блока на странице; например, блок 0 указывает на область данных 0-8kB, а блок 10 – на область данных 80kB-88kB. Number – это номер "строки" в блоке.

Индексы PostgreSQL не хранят видимости. Это означает, что данные, расположенные в ctid, могут быть логически удалены и должны быть отфильтрованы при выполнении запроса. Таким образом обращение к индексу почти всегда вызывает чтение данных в таблице (обратите внимание на Heap Fetches в explain, подробнее тут).

Равномерные и неравномерные индексы

Соседние значения в индексе, построенном на неравномерных данных, могут указывать на данные, которые расположены физически далеко, в разных блоках таблицы. В нашем случае, ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa’::uuid и ‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaab’::uuid с большой вероятностью (около 99.9%) будут разнесены хаотично по разным блокам таблицы.

Взглянем на содержимое индекса, выбираем для примера блок 21:

В равномерном же индексе соседние значения указывают на один и тот же блок физических данных. Кстати, для равномерных данных в PostgreSQL был придуман BRIN-индекс, который хранит равномерные данные более эффективно, чем B-Tree.

В неравномерном индексе мы видим, что "соседние" (отсортированные) строки указывают на большое количество разных блоков в таблице.

Один блок неравномерного индекса указывает на большое количество блоков с данными в таблице

Один блок неравномерного индекса указывает на большое количество блоков с данными в таблице

Проблемы неравномерного индекса

Давайте кратко сформулируем основные проблемы неравномерного индекса:

Низкий page hit (переиспользование страниц буферного кэша) самого индекса: нет "горячих частей", таких, как компактное хранение всех id текущего дня в ограниченном количестве блоков индекса.

Рандомное чтение heap для проверки видимости значений в индексе, если не используется bitmap heap scan.

Дорогое обслуживание индекса: такие операции, как reindex/vacuum, вызывают дополнительное чтение и запись при ограниченном количестве буферного кэша.

Неравномерный индекс на чтении

Частный пример неравномерных данных – это UUID v4, который мы использовали в примере в самом начале. Гораздо чаще в реальной жизни можно встретить пример неравномерных данных, реализованных на менее специфических типах данных: int, varchar. Проведём надуманный, но жизненный эксперимент:

Начнём читать данные – последние N-операций пользователя:

Как мы видим, в худшем случае придётся прочитать с диска почти столько страниц (совершить пропорциональное количество IOPS), сколько собираемся вернуть строчек:


Источник: temofeev.ru