Как мы ускорили выполнение запросов PostgreSQL в 100 раз

Существует великое множество статей об оптимизации PostgreSQL — эта «кроличья нора» весьма глубока. Когда несколько лет назад я начал разрабатывать бэкэнд аналитического сервиса, у меня уже был опыт работы с другими СУБД, такими как MySQL и SQL Server. Тем не менее, раньше мне не приходилось так фокусироваться на производительности. В прошлых проектах, над которыми я работал, либо не было жестких требований к времени обработки (DS/ML), либо не требовалось обрабатывать много строк одновременно (обыкновенные веб-приложения). Однако в этот раз мои запросы:

• состояли из 3-10 JOIN-ов по коррелирующим запросам;

• yielded от 10 до 1 000 000 строк;

• должны были выполняться в течение времени, определенного UX-ом;

• не могли быть hinted — пока Cloud SQL, управляемый PostgreSQL в Google Cloud, не стал поддерживать pg_hint_plan в конце 2021 года;

• запрещали прямой доступ к серверному процессу, чтобы, например, хакнуть некоторые perf — потому что PostgreSQL был managed.

Получение целого миллиона строк в одном API endpoint сигнализирует о проблеме в алгоритме или архитектуре. Конечно, все можно переписать и перепроектировать, но за это нужно платить.

У нас не нашлось «заклинания», которое решило бы все проблемы с производительностью SQL. Тем не менее, я упомяну здесь несколько дельных предложений, которые помогли нам и, надеюсь, смогут помочь читателю. Разумеется, это не какие-то сакральные знания. Но когда мы начинали оптимизацию, я был бы рад их прочитать или услышать.

Тайное преимущество LEFT JOIN

Каждый, кто писал SQL-запросы, должен знать разницу между INNER JOIN и LEFT JOIN. В книгах часто упускается тот момент, как эти разновидности объединения влияют на планировщик запросов, если общие столбцы взаимосвязаны. Предположим, что у нас есть две таблицы:

pull_requests содержит записи о pull request’ах на GitHub. commits содержит записи о коммитах на GitHub. merge_commit_id в pull_requests ссылается на {0,1}-1 id в commits. Все id имеют очень высокую селективность. Учитывая INNER JOIN между таблицами,

SELECT pr.*, c.sha AS merge_commit_sha
FROM pull_requests pr
INNER JOIN commits c ON pr.merge_commit_id = c.id
WHERE pr.repository_id IN (...)

планировщик PostgreSQL, скорее всего, предскажет лишь малое количество результирующих строк и выдаст Nested Loop Join. Это произойдет, потому что PostgreSQL не знает, что наши идентификаторы коммитов являются коррелирующими, и перемножает их селективности в формуле оценки количества объединенных строк. Поэтому производительность нашего запроса стремительно падает, если мы обрабатываем более 10k строк. Давайте рассмотрим, как поведет себя LEFT JOIN:

SELECT pr.*, c.sha AS merge_commit_sha
FROM pull_requests pr
LEFT JOIN commits c ON pr.merge_commit_id = c.id
WHERE pr.repository_id IN (...)

Планировщик, скорее всего, предскажет такое же количество результирующих строк, как и отфильтрованных в pull_requests, и правильно запланирует Hash Left Join на ~10k. Чтобы избежать хэширования всех коммитов, мы можем воспользоваться знанием того, что PR и коммиты всегда находятся в одном и том же репозитории.

SELECT pr.*, c.sha AS merge_commit_sha
FROM pull_requests pr
LEFT JOIN commits c ON pr.merge_commit_id = c.id
AND pr.repository_id = c.repository_id
WHERE pr.repository_id IN (...)

Второе условие JOIN является искусственным и не влияет на результат. Однако PostgreSQL достаточно умна, чтобы хэшировать коммиты, предварительно отфильтрованные по тем же repository_id, что и pull_requests. Наш запрос должен выполняться быстрее, поскольку под рукой имеются все необходимые индексы.

Реальное преимущество этого подхода проявляется в «многоэтажных» JOIN-ах. PostgreSQL кэширует предварительно отфильтрованные хэш-таблицы, и их объединение обходится «недорого», в то время как кэширование вложенного цикла невозможно. В результате нам удалось добиться 10x-100x повышения производительности при переходе от INNER JOIN к LEFT JOIN. Важный момент: вы должны пост-фильтровать null'ы, если не уверены, что разные JOIN'ы возвращают эквивалентные результаты.

Хеширование VALUES

Рассмотрим типичный запрос "fat IN":

SELECT *
FROM pull_requests
WHERE repository_id IN (...более 9000 идентификаторов...)

Этот запрос обычно планируется как Index или Bitmap Scan. Мы можем переписать его, используя выражение VALUES:

SELECT *
FROM pull_requests
WHERE repository_id = ANY(VALUES (101), (102), ...)

PostgreSQL создает другой план с HashAggregate над Values Scan и, вероятно, Hash Join, если прогнозируемое количество строк достаточно велико. Влияет ли это на производительность? В отдельных случаях — да. Я заметил, что такой подход полезен в запросах с несколькими JOIN.

P.S.: не стоит вставлять более 9000 идентификаторов непосредственно в тело SQL-запроса.

WHERE repository_id = ANY($1::text::bigint[])WHERE repository_id = ANY(SELECT * FROM unnest($1::text::bigint[]))--где $1 - аргумент, переданный по бинарному протоколу$1 = '{...более 9000 идентификаторов....}'

Обратите внимание на двойное приведение $1::text::bigint[]. Прямое приведение к bigint[] может не сработать из-за неверного определения типа параметра внутри asyncpg (ожидался размерный итерируемый контейнер (а получен тип 'str')).

Расширенная статистика

В продолжение предыдущего SQL, давайте добавим еще одно условие в WHERE:

SELECT *
FROM pull_requests
WHERE repository_id IN (...) AND merge_commit_id IN (...)

И repository_id, и merge_commit_id имеют высокую селективность. Эти два столбца являются для PostgreSQL «черным ящиком», поэтому он, скорее всего, существенно занизит полученное количество строк. Пессимистичный прогноз о количестве строк приводит к принятию ошибочных решений, таких как Nested Loop вместо Hash Join для LEFT JOIN commits, и производительность падает.

Существует приемлемое решение этой проблемы: расширенная статистика.

CREATE STATISTICS ids_correlation ON repository_id, merge_commit_id FROM pull_requests;

Благодаря ids_correlation, PostgreSQL выше 13 версии поймет, что repository_id и merge_commit_id коррелируют, и скорректирует оценку количества строк.

Расширенная статистика оказалась особенно полезной для корректировки прогнозов планировщика, когда мы использовали шардинг по ID клиентского счета. Вновь мы получили Hash Joins вместо Nested Loops и 10-100-кратное ускорение.

Тип первичного ключа имеет значение

Раньше у нас была немного другая схема, взгляните на нее:

Старая схема БД с типом varchar для идентификатора запроса.

GitHub присваивает так называемый идентификатор ноды каждому объекту API, например, pull request. Это непрозрачная строка: например, athenianco/api-spec#66 - это PR_kwDOFlTa5c4zPMUj. Мы решили использовать идентификаторы узлов в качестве первичных ключей. Все работало хорошо, пока GitHub не изменил формат ID ноды. Нам было сложно перестроиться, и в итоге мы перешли на глобальные целочисленные ID, сопоставленные с ID нод. Извлеките урок из нашей ошибки: не стоит полагаться на внешние ID, потому что вы их не контролируете.

Когда наши первичные ключи превратились в целые числа вместо строк, мы были в восторге от 2-5-кратного ускорения JOIN-ов по этим столбцам. Целые числа занимают меньше памяти, их быстрее сравнивать и хешировать. Нет ничего удивительного в том, что производительность так сильно возросла.

CLUSTER

Продолжим исследовать pull_requests.

SELECT *
FROM pull_requests
WHERE repository_id IN (...) AND number > 1000

Предположим, что у нас уже есть подходящий индекс:

CREATE INDEX pull_requests_repository_id ON pull_requests (repository_id, number).

Можем ли мы сделать что-нибудь еще, чтобы ускорить выполнение запроса? Я вижу два варианта:

• Поместить столбцы, упомянутые в SELECT *, в INCLUDE-часть покрывающего индекса. Получится сканирование только по индексу.

• Кластеризовать таблицу по pull_requests_repository_id.

Первый вариант хорош, если количество дополнительных столбцов невелико. Второй способ более продвинут. В нем используются знания о стандартных способах обращения к таблице. В частности, интересны PR, сгруппированные по хранилищам: мы, скорее всего, хотим получать только самые последние PR (моделируемые числом > 1000). Следовательно, мы объявляем индекс как CLUSTER:

CLUSTER pull_requests USING pull_requests_repository_id

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

Как и многие другие, мы столкнулись с препятствием при внедрении CLUSTER ... USING в прод. Эту команду необходимо выполнять регулярно, поскольку PostgreSQL не может автоматически поддерживать кластерное состояние. К сожалению, CLUSTER устанавливает эксклюзивную блокировку на таблицу, и ожидающие запросы чтения/записи блокируются. Спасением стал pg_repack — легкая альтернатива без блокировок.

Ускорение на проде составило около 2-5 раз, особенно CLUSTER помог с «холодными» запросами, которые должны были читать с диска буферы.

pg_hint_plan

Руководство PostgreSQL всегда выступало против SQL-хинтов. Ситуация напоминает запрет дженериков в Go — за исключением того, что 13 лет спустя дженерики в Go появились, а PostgreSQL не хочет добавлять хинты уже больше 36 лет. К счастью, хинты можно подключить с помощью pg_hint_plan, японского проекта на GitHub. Cloud SQL поддерживает pg_hint_plan с конца 2021 года.

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

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

Избыточные условия в WHERE

Мы можем ускорить Hash Joins, если добавим в запрос дополнительные ограничения. Они не меняют результат, но уменьшают количество чтений индекса.

Рассмотрим запрос из раздела о LEFT JOIN.

SELECT pr.*, c.sha AS merge_commit_sha
FROM pull_requests pr
LEFT JOIN commits c ON pr.merge_commit_id = c.id
AND pr.repository_id = c.repository_id
WHERE pr.repository_id IN (...)

Мы можем переписать его следующим образом:

SELECT pr.*, c.sha AS merge_commit_sha
FROM pull_requests pr
JOIN commits c ON pr.merge_commit_id = c.id
WHERE pr.repository_id IN (...) AND c.repository_id IN (...)

Как я уже отмечал ранее, планировщик, скорее всего, неправильно предскажет количество строк, поскольку он не знает, что repository_id коррелируют. Однако, у нас есть супер-оружие, и мы можем подправить этот прогноз.

/*+
Rows(pr c *100)
*/
SELECT pr.*, c.sha AS merge_commit_sha
FROM pull_requests pr
JOIN commits c ON pr.merge_commit_id = c.id
WHERE pr.repository_id IN (...) AND c.repository_id IN (...)

Фактический коэффициент умножения должен усредняться по нескольким типичным запросам. Описанный подход имеет свои плюсы и минусы.