Производительность запросов в PostgreSQL – шаг за шагом

Илья Космодемьянский (PostgreSQL-Consulting.com)

Для начала сразу пару слов о том, о чем пойдет речь. Во-первых, что такое оптимизация запросов? Люди редко формулируют и, бывает так, что часто недооценивают понимание того, что они делают. Можно пытаться ускорить какой-то конкретный запрос, но это не обязательно будет оптимизацией. Мы немного на эту тему потеоретизируем, потом поговорим о том, с какого конца к этому вопросу подходить, когда начинать оптимизировать, как это делать, и как понять, что какой-то запрос или набор запросов никак нельзя оптимизировать – такие случаи тоже бывают, и тогда нужно просто переделывать. Как ни странно, я почти не буду приводить примеров того, как запросы оптимизировать, потому что даже 100 примеров не приблизят нас к разгадке.

Обычно самое узкое место – это непонимание, с какой стороны подойти к этому делу. Все говорят: «Читай EXPLAIN, проверяйте запросы EXPLAIN'ом», но мне часто задают вопросы о том, на что там смотреть и что делать после того, как на EXPLAIN уже посмотрели? Вот об этом я постараюсь рассказать подробнее.

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

Основная проблема с базами данных (БД) в том, что: или в БД лежит что-то ненужное, или не лежит чего-то нужного, или же имеет место какой-то неправильный подход к ее эксплуатации. В принципе, мы можем поставить какой-то супер-пупер RAID-контроллер с огромным кэшем, супер-дорогие SSD диски, но если мы не будем включать голову, то результат будет плачевным.

Что значит «оптимизировать запросы»? Как правило, у вас такой задачи не возникает. Проблема обычно заключается в том, что «все плохо!». Мы пишем наш замечательный проект, все работает, все довольны, но в какой-то момент заказали чуть больше рекламы, пришло чуть больше пользователей, и все упало. Потому что, когда разрабатывают проект быстро, обычно используют «рельсы», «джанго» и т.п. и пишут «в лоб» – важно быстрее дать продукт. Это на самом деле правильно, т.к. никому не нужен идеально «вылизанный» проект, который не работает. Дальше нужно понять, что происходит. Эти медленные запросы, которые мы хотим оптимизировать, – это просто такой интерфейс, который выдает наружу, что вот это тормозит, но причина запросто может быть в чем-то другом. Может быть плохое «железо», может быть ненастроенная база, и в этот момент начинать с оптимизации запросов, в принципе, не стоит. Сначала надо посмотреть, что происходит с БД.

Бывают просто грубые ошибки в настройке БД и до того, как они будут исправлены, оптимизировать запросы бесполезно, потому что bottleneck в другом месте. Например, если речь о Postgres’е, у вас может быть отключен autovacuum. Почему-то люди это иногда делают (этого ни в коем случае делать нельзя!), но когда он отключен, у вас очень большая фрагментация таблицы. Легко может быть таблица на 100 тыс. записей размером с таблицу в 1 млрд. записей. Естественно, любые запросы к ней будут медленнее, чем вы ожидаете. Поэтому сначала нужно БД настроить, проверить, что все хорошо работает.

Еще одна частая ошибка, когда 1000 worker ‘ов Postgres’а работают, потому что очень много подключений от приложения, и нет никакого Connection Broker’а. Надо понимать, что если у вас 500 connection’ов, то у вас должно быть 500 ядер на сервере, на котором вы работаете. В противном случае эти connection’ы будут друг другу мешать и будут все время проводить в ожидании. Когда вы эти глупости исправили (их может быть довольно много, но основных – 5-10 штук – правильные настройки памяти, диска, autovacuum...), вы можете переходить к оптимизации запросов. И только тогда. Не надо пытаться оптимизировать то, что еще вы только делаете.

Каков алгоритм, т.е. как подходить к оптимизации запросов? Во-первых, надо проверить настройки, во-вторых, каким-то способом отобрать те запросы, которые вы будете оптимизировать (это важный момент!), и, собственно, оптимизировать их. После того, как вы самые медленные запросы «вылечили», они стали быстрее, у вас немедленно появляются новые медленные запросы, потому что старый топ уступил им место. В результате вы постепенно, шаг за шагом повторяя этот алгоритм, избавляетесь от медленных запросов. Все просто.

Очень важный момент – знать, какие запросы оптимизировать? Если вы будете оптимизировать все подряд, то есть шанс не угадать, какие из запросов являются самыми проблемными. Вы просто потратите много времени и с большой вероятностью не доберетесь до нужных задач. Поэтому запросы нужно оптимизировать по мере поступления проблем. Посмотрите, где работать перестало, стало медленно и плохо – вот этот кусок и оптимизируйте. Если у вас есть что-то, что редко используется, не надо этого трогать, не тратьте время.

По отобранному топу берутся запросы, и смотрится, что с ними можно сделать. Для этого, особенно в версии 9.4, правильный способ – использовать EXTENSION pg_stat_statements, который все это делает «на лету» в онлайне, и можно на все это посмотреть.

Давайте разберемся с этим детально.

По адресу внизу страницы – наши pg-utils , которые доступны в свободном доступе, можно скачать и воспользоваться. В папке лежит некая обвязка вокруг стандартного контриба pg_stat_statements, который позволяет генерить вот такие отчеты за сутки работы на БД, и мы можем посмотреть, что там происходило. Видим, соответственно, некий топ запросов, каким-то образом ранжированный.

Нам важно знать, что есть первая позиция, вторая (обычно их еще десяток), и мы видим, что по каким-то параметрам у нас некий запрос выходит на первое место – он занимает, например, 24% нагрузки базы. Это довольно много и это надо как-то оптимизировать. Вы смотрите на запрос и думаете: «А сколько он денег проекту приносит?». Если он приносит проекту очень много денег, то пусть даже половину нагрузки отъедает, а если он денег не приносит, а отъедает половину ресурсов – это плохо, с этим надо что-то сделать. Таким образом, вы смотрите на топ запросов за предыдущий день и размышляете, что с этим делать.

Хорошей практикой у нас считается, когда есть команда разработчиков, которая делает что-то на проекте, и раз в сутки по cron'у такой отчет приходит всем DBA, всем разработчикам, всем админам.

Что такое медленный запрос? Во-первых, это запрос, который имеется в топе (его надо оптимизировать каким-то образом в любом случае). Но чисто по времени – это всегда некий вопрос. Даже запрос, который будет работать доли миллисекунды, все равно может быть медленным. Например, если этих запросов очень много, и много мелких запросов в итоге подъедают очень большой процент ресурсов базы.

Таким образом, время запроса – вещь относительная, и тут нужно смотреть, насколько часто этот запрос работает. Если это отдача чего-то на Главной странице, и этот запрос занимает 1 секунду, надо понимать, что у вас будет эта секунда и еще плюс оверхед от всего того, что нужно, чтобы сформировать эту страницу. Это значит, что пользователь увидит результат гарантированно медленнее, чем через секунду, и для онлайнового высоконагруженного веба это неприемлемые результаты. Если же запрос у вас для какой-то аналитики гоняется ночью, присылается кому-то асинхронный отчет, то, вероятно, он может себе позволить работать медленно. То есть, всегда надо знать свои данные и всегда думать, сколько времени допустимо, чтобы этот запрос работал. Опять же важен характер нагрузки на базе. Например, у вас есть длинный тяжелый запрос, вы его гоняете в пиковое время, а это запрос для какой-то аналитики, для менеджеров и т.п.

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

Не забываем о том, сколько этот вопрос приносит денег и имеет ли он право занимать много ресурсов БД. Если вы сделали прикольную фичу, которая не зарабатывает для проекта ничего, а этот запрос съедает 50% ресурсов, то значит, вы написали плохой запрос и вам нужно переделать эту идею и даже иногда объяснить менеджеру, почему эта технически очень сложная штука просто съедает ресурсы. Все говорят, мол, хочу, чтобы Золотая Рыбка была у меня на посылках, тем не менее, сервер – он железный, он имеет некие лимиты, и резиново растягивать его нельзя. Люди, которые делают облака, скажут вам, что можно, но я как зануда-админ, скажу, что нельзя.

Где, вообще, могут быть проблемы при исполнении одного конкретного запроса? Во-первых, это может быть передача данных от клиента, и это совершенно не так смешно, как кажется. Следующий слайд демонстрирует, где там может быть «зарыта собака»:

Кто писал на Ruby, использовал всякие хитрые ORM'ы, знает, что вот по этому запросу можно опознать Django. Это фирменный стиль, почерк радиста ни с чем не перепутаешь.

Как вы думаете, какой максимальной длины in-список я видел в своей жизни? Надо считать в гигабайтах! Если не смотреть на то, что делает ваш ORM, то легко может получить несколько гигабайт, и этот запрос никогда не исполнится. Это плохо и означает, что у вас совершенно неоптимальный доступ к данным. Этот запрос плох еще по многим причинам, но основная причина того, что он может быть такой длины, которая в Postgres никогда в жизни не пролезет.

Второй момент – это парсинг. Можно написать очень витиеватый запрос, который просто будет долго парситься. В новой версии Postgres’а, если я не ошибаюсь, будет в EXPLAIN ‘е время парсинга, и можно будет понять, сколько на это уходит времени. Сейчас можно просто сделать EXPLAIN и посмотреть таймингом, соответственно, сколько у нас ушло на исполнение запроса, а сколько – на парсинг.

Потом запрос нужно оптимизировать. И это далеко не такая простая задача, как кажется, потому что оптимизатор – это достаточно сложные алгоритмы. Вот, к примеру, вы хотите сделать Join двух таблиц. Он берет одну таблицу, выбирает метод доступа к нужным данным и при-Join'ивает к ней следующую. Если у вас еще один Join с еще одной таблицей, то сначала он с-Join'ит две, потом с ResultSet’ом с-Join'ит еще одну. Если вы написали запрос, в котором 512 Join'ов, дальше начинается очень интересная «петрушка» с оптимизацией этого всего.

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

Далее может быть непосредственно исполнение. Если ваш запрос должен вернуть куда-то 10 Гб данных, сложно рассчитывать на то, что он будет работать миллисекунды. Никаким волшебством его не заставить. Поэтому, если вам нужно отдать много данных, то сразу имейте в виду, что волшебства не бывает. Оно бывает в мире NoSQL, а здесь его нет.

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

Самый главный слайд этой презентации:

Это EXPLAIN.

После того, как вы выделили топ запросов, вы как-то удостоверились, что эти запросы медленные, и хотите с ними что-то сделать, вам нужно прогнать EXPLAIN.

До этого этапа доходят многие, а дальше загвоздка. Люди жалуются: «Ну, мы посмотрели EXPLAIN, и что с ним дальше делать?». Вот это я сейчас вам и расскажу.

На слайде 2 EXPLAIN'а, немного разный синтаксис.

Можно вот так делать: explain (analyze on, buffers on), можно просто написать explain analyze, например.

Важно понимать, что EXPLAIN вам выведет просто план предполагаемый, какой он должен быть. Соответственно, если вы укажете еще и ANALYZE, то этот запрос будет реально исполнен, и будут показаны данные о том, как он был исполнен, т.е. не просто EXPLAIN, а еще какая-то трассировка, собственно говоря, что происходило.

Когда вы отобрали топ медленных запросов, правильно использовать EXPLAIN ANALYZE, потому что у вас может быть выбран неоптимальный план, может быть не собрана статистика и др.

Postgres – супер транзакционная штука, если у вас есть пишущий запрос, вы не хотите, чтобы эти результаты записались, пока вы что-то оптимизируете, говорите: «begin», запускаете запрос (но желательно смотреть, что, вообще, происходит – тяжелый запрос в пиковое время на боевую базу не всегда бывает хорошо), потом говорите «rallback», чтобы у вас эти данные не записались.

Здесь показываются некие цифры, часть которых принадлежит EXPLAIN'у, а часть – ANALYZE'у. Это важные цифры. В EXPLAIN'е есть условные «попугаи» под названием «cost». 1 cost в Postgres‘е по умолчанию – это время, которое затрачивается на извлечение одного блока размером 8 Кб при последовательном sequential scan'е. В принципе, эта величина зависит от машины, поэтому она условная, поэтому это удобно. Если у вас быстрые диски, это будет быстрее, если медленные – медленнее. Важно понимать, что cost=9.54 – это означает, что в 9.54 раза это будет медленнее, чем достать 1 блок размером 8 Кб.

При этом цифр две: первая означает, сколько пройдет времени до момента начала возврата первых результатов, а вторая – это сколько пройдет времени до того, как результат будет возвращен весь. Если вы извлекаете много данных, то первая цифра будет относительно маленькой, а вторая будет достаточно большой. Это актуальное время, сколько это на самом деле заняло. Если по каким-то причинам у вас cost очень маленький, а это время очень большое, значит у вас какие-то проблемы со сбором статистики, нужно проверить, включен ли autovacuum, потому что тот же самый демон autovacuum’а собирает еще и статистику для оптимизатора.

EXPLAIN – это такое дерево, есть нижние варианты, грубо говоря, как достать данные с диска – это скан табличек, скан индексов и т.д.; и более верхние варианты, когда на верх наслаивается какая-то агрегация, Join'ы и т.д. Когда вы смотрите на такой EXPLAIN, задача очень простая:

• понять, насколько быстро он работает, посмотреть на runtime, посмотреть, сколько там чего происходило;
• посмотреть, какой узел этого дерева самый дорогой. Если у вас на нижнем этапе сразу cost достаточно большой, actual time большой, то значит, вам этот кусок и надо оптимизировать. Например, если у вас сканируется таблица целиком, вам может там понадобится сделать индексы. Это то место, которое действительно нужно оптимизировать, на которое нужно смотреть.

Если у вас, например, чудит агрегация, как в нашем случае на слайде – она там более тяжелая, то вам нужно подумать, как от нее избавиться.

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

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

Если у вас вся таблица увешана индексами, которые не используются, с большой вероятностью вы можете часть из них снести, и будет быстрее. Тем не менее, если в вашем запросе нужно, например, извлечь половину данных из таблицы, с большой вероятностью ваш индекс не будет использоваться, потому что по индексу имеет смысл спозиционироваться в какое-то достаточно точное место и эти данные достать. Если вам нужно большую «простыню», которая сопоставима по размерам с таблицей, sequential scan самой таблицы будет всегда быстрее, чем index scan, потому что вам будет нужно сначала сделать index scan, потом еще одну операцию – достать данные.

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

В Postgres’е есть такой параметр – сессионная переменная, enable index scan установить в off или, наоборот, sequential scan установить в off, и вы можете посмотреть – с индексом или без него будет быстрее/медленнее. Оптимизировать так запросы «в бою» я бы не советовал, это очень жесткий «костыль» и очень серьезное ограничение функционала для оптимизатора, но поэкспериментировать, посмотреть – это полезно. Вы сделали запрос, сделали для него индекс, считаете, что он будет работать, отключите sequential scan, оптимизатор будет вынужден выбрать план с индексом, и посмотрите, не получилось ли медленнее, чем то, что Postgres предложил вам сам. В большинстве случаев это именно так.

Далее важно, как написан запрос. Если у нас будет что-то вроде этого – (where counter + 1 = 46) – индекс браться не будет, автоматически Postgres эту операцию сделать не может. Казалось бы, простое сложение, но с тем же успехом можно предложить оптимизатору еще и дифуры порешать. В Postgres’е большое количество типов данных, на них можно определять любые операторы, любые действия, например, алгебраические или др., и оптимизатор должен для всех этих типов знать, как это действие выполнять, а для него это слишком тяжелая задача, это никогда не будет работать.

Следующее – почему, например, Join работает плохо? Это один из важных узлов, его все используют.

Join'ы бывают разных типов, и я говорю не о LEFT, RIGHT, INNER и т.д., а об алгоритмах, как Join'ы выполняются. Postgres имеет три основных алгоритма Join'а, а именно – Nested Loop (название говорит само за себя – мы берем данные из одной таблицы и циклами их Join'им), Hash index (когда одна, чаще маленькая, таблица хэшируется и по этому хэшу Join'ится с другой таблицей) и Merge Join (который тоже очевидно, как работает).

Эти Join'ы не всегда одинаково полезны, оптимизатор может выбрать между ними. Например, у вас Join'ятся две таблицы, оптимизатор выбирает Hash Join, и вы понимаете, что он работает медленно, вас это не устраивает. Имеет смысл посмотреть, а индексированы ли у вас те поля, по которым вы Join'ите? Если у вас эти поля не индексированы, оптимизатор может не выбрать Nested Loop, который здесь очевидно выгоднее. Если вы создадите индекс, оптимизатор выберет Nested Loop, и все будет работать быстро.

Следующий момент – у вас оптимизатор из каких-то соображений выбирает Nested Loop, а вам кажется, что одна таблица очень маленькая, другая – очень большая и Hash Join там был бы очень уместен, потому что маленькую таблицу можно быстро прохэшировать и быстро с ней работать. Посмотрите, сколько у вас work mem'а. т.е. сколько памяти может занять один worker Postgres’а. Если эта таблица хэшируется в, например, 100 Мб, а у вас work mem'а выдано только 30 Мб, то worker будет работать медленно. Если вы добавите work mem'а и хэширование начнет вмещаться в память, оптимизатор выберет правильный Hash Join и будет быстро и хорошо.

Вот такое вот поле для экспериментов, тут надо думать и не стесняться проверять, пробовать и смотреть, что происходит.

Поскольку оптимизировать запросы нужно только «на бою» (потому что на тесте вы никогда не воспроизведете workload настолько же точно), то делать это надо с известной осторожностью.

Пример такой оптимизации:

Я уже показывал этот запрос и очень ругался на него. Одна из причин для такой ругани состоит в том, что, все-таки Postgres не совершенен, он – развивающаяся система, есть в нем какие-то недостатки (сообществу очень нужны разработчики оптимизатора, если вы хотите подконтрибьютить Postgres’у, то можете в эту сторону посмотреть, такие усилия всегда будут очень приветствоваться, потому что есть недоделки). Вот и случай такого длинного массива в WHERE очень распространенный, потому что многие ORM’ы это делают. По идее, Postgres должен как-то хэшировать этот массив и соответственно осуществлять в нем поиск. Вместо этого он его перебирает и получается достаточно противно, время растет очень существенно, и начинаются проблемы.

Посмотрим на EXPLAIN этого дела:

Мы видим, что фильтр без хэша работает плохо, несмотря на то, что имеется Index Scan для того, чтобы что-то сделать и выбрать на массиве, мы заседаем, а он работает максимально плохим образом и все тормозит.

В этой ситуации запрос необходимо переписать, включив воображение. Я уже говорил, что Postgres умеет Hash Join, но не умеет делать хэширование массива. Давайте сконвертируем эту «простыню» таким образом, чтобы результат можно было с-Join'ить. В итоге получится то же самое, только оптимизатор выберет более разумный план.

Можно использовать такую конструкцию VALUES, которая нам все это превратит в ResultSet:

В этой ситуации на самом деле будет Index Scan и Hash Join. Будет произведено хэширование, и запрос довольно существенно ускорится.

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

И это достаточно легко переделать.

Это был простой пример того, как, посмотрев на EXPLAIN, можно существенно улучшить производительность тормозного запроса. Это т запрос уйдет из топа, придет новый, его тоже можно будет оптимизировать.

Есть еще одна очень существенная проблема – бывают такие запросы, с которыми нельзя ничего сделать. И первейший из этих запросов –count(*).

Например, есть интернет-сайт, у него – Главная страница, она высоконагруженная, на ней отображаются счетчики. Какую информацию пользователю несет число на счетчике, если это count из таблицы, которая часто обновляется? Число означает, что на тот момент, когда пользователь послал свой запрос, цифра была такая. Обычно пользователю не важно знать эту цифру с высокой точностью, достаточно более-менее приближенно, а чаще, вообще, хватает знания о том, растет число или нет. Если это какой-то финансовый баланс, то можно это делать, но обычно это делается очень редко и не выводится на Главную страницу сайта, чтобы при каждом обращении count гонялся по таблице.

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

Первый вариант решения этой проблемы – не использовать count'ы. Полезность его сомнительна, а ресурсов он занимает много. Второй момент – можно использовать приближенный count. Есть PG-каталог, из него можно по-select’ить, сколько строк в таблице было на момент последнего analyze'а, когда был произведен последний сбор статистики. Эта приблизительная цифра будет меняться достаточно часто, но при этом запрос по PG-каталогу не стоит практически ничего – это select одного value по условию названия таблицы. Если вы не хотите пускать интернет-юзера базы в PG-каталог, вам ничто не мешает написать хранимую процедуру, сказать ей «security definer» и дать права только на эту процедуру, и интернет-пользователь будет спокойно доставать эти данные без всяких проблем с security.

Следующий запрос-проблема – Join на 300 таблиц. Проблема состоит в том, что будет 300! вариантов, как этот Join сделать. Более того, если вам понадобилось написать Join на 300 таблиц, это значит, что у вас очень плохо с дизайном схемы, что-то очень не продумано, и надо много чего переделывать. В норме Join – это на две, на три таблицы. Иногда на пять, изредка на десять, но это крайние случаи. Когда Join'ов сотни, любой БД станет плохо.

Еще одна проблема – когда клиенту возвращается 1 000 000 строк. Кто долистывал до последней страницы в Google? Часто это бывает? Если вы видите, что онлайновый запрос, результат которого отображается на сайте, по каким-то причинам возвращает 1 млн. строк, задумайтесь – нет ли какой-то ошибки? Может понадобиться 10, 20 строк, может быть, 100, но 1 млн. строк человек не читает. Если у вас столько строк возвращается, это значит, что у вас либо какая-то выгрузка данных, которую можно сделать ночью, можно сделать с помощью дампа, можно еще каким-то способом, либо у вас просто неправильно написан запрос.

Например, вы сгенерили ORM'ом запрос для какай-то листалки, дальше, соответственно, вытаскиваете этот огромный массив, а используете из него реально только 10%. В этой ситуации вам нужно использовать limit и offset и каким-то другим способом определенным окном идти по этим данным и не тянуть их все на клиент, потому что 1 млн. строк – это всегда медленно, и, как правило, это не осмысленно и содержит какую-то логическую ошибку.