Самый больной вопрос для любого разработчика, которому приходится вычитывать данные из базы: "Как сделать мой запрос быстрее?". Классический ответ - необходимо создать подходящий индекс. Но куда именно его стоит "накатывать", да и как вообще он должен выглядеть?..

Мы научили наш сервис визуализации планов PostgreSQL отвечать на эти вопросы, и под катом расскажем, чем именно он руководствуется в своих рекомендациях.

Вчера Hubert 'depesz' Lubaczewski закрыл доступ с российских IP ко всем своим сайтам, включая широко известный визуализатор планов PostgreSQL-запросов explain.depesz.com.

Но это не беда, потому что в компании "Тензор" мы разработали сервис explain.tensor.ru, функционал которого гораздо обширнее, и которым можете воспользоваться и вы.

Соблазн использовать модель EAV (Entity-Attribute-Value) при организации структуры БД весьма велик, особенно когда предметная область заранее плохо известна (или разработчик просто не хочет в нее углубляться). Это ведь так удобно - создать "универсальный" способ описания характеристик объектов, который больше не потребует доработок базы ни при появлении новых типов объектов, ни при возникновении новых атрибутов...

Однако, за любую универсальность приходится платить сложностью и производительностью запросов - так что json[b] может оказаться более эффективной заменой. Но если уж такая модификация невозможна - давайте попробуем выжать максимум производительности из доставшегося нам legacy на самом простом примере.

В моей практике ускорения SQL-запросов для PostgreSQL, в большинстве случаев, все сводится к применению типовых методик - их не особенно-то и много, и прочитать про большинство из них можно в моем профиле.

Но иногда обнаруживаются очень странные вещи в поведении этой, безусловно, отличной СУБД.

Все началось с запроса, который мне показали с диагнозом "необъяснимо тормозит"...

В "Тензоре" мы разрабатываем множество сервисов для управления бизнесом. А в бизнесе очень часто возникает желание немного "заглянуть в будущее" - спрогнозировать и увидеть на графике значение каких-то величин, которые мы можем только предполагать на основании данных предыдущих периодов. Например, на какую примерно выручку мы сможем рассчитывать в следующем месяце или сколько продуктов стоит закупить в столовую на следующую неделю.

Для решения этой задачи можно строить сложные математические модели и проверять их на "кластерах с бигдатой", но мы попробуем найти вариант попроще - когда есть всего одна метрика, SQL и немного житейской логики.

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

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

Для систем управления бизнесом часто приходится решать очень похожий класс задач по вычислению количества уникальных объектов на произвольном временном интервале. В контексте CRM это могут быть "пользователи, обращавшиеся на горячую линию на прошлой неделе", "контрагенты, оплатившие за последние 30 дней" или "потенциальные клиенты, с кем был контакт в этом квартале".

Искать в большом количестве фактов «уники» — всегда сложно и долго, если их достаточно много. Если интервалы фиксированы (календарные месяц/квартал/год), можно материализовывать такие агрегаты заранее. А если интервал — произвольный, как тогда эффективно найти ответ?

Большая часть оптимизаций запросов к базам PostgreSQL может выполняться "механически", следуя разного рода маркерам в плане выполнения запроса, которые подскажут, что и как можно ускорить. Но "глубинные" переработки алгоритма, вроде описанных в статье про DBA-детектив, требуют от разработчика детального понимания используемой структуры логических связей.

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

А ведь она уже и так находится "под ногами" в момент анализа плана запроса - надо только лишь удобно увидеть ее!

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

Если не видели дайджест за первый год — время наверстать упущенное!

SQL является мощным инструментом для обработки множеств, а функционал PostgreSQL позволяет делать многие вещи еще проще, поэтому идеально подходит для реализации некоторых алгоритмов на графах.

Причем работа с графами - это не просто разминка для ума, а вполне себе прикладная задача. Например, в прошлой статье мы сделали "из мухи - слона" волновым алгоритмом Ли, аналогичным используемому у нас в СБИС при расчете себестоимости в многокомпонентных актах выпуска.

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

Правила игры очень просты: надо построить цепочку слов от начального (МУХА) до конечного (СЛОН), на каждом шаге меняя только одну букву. При этом могут использоваться только русские 4-буквенные нарицательные существительные в начальной форме: например, слова БАЗА, НОЧЬ, САНИ допускаются, а слова ЛИТЬ, ХОТЯ, РУКУ, НОЧИ, САНЯ, ОСЛО, АБВГ, ФЦНМ — нет.

Эта игра под названием «Дублеты» приобрела известность благодаря Льюису Кэрроллу — не только автору книг про Алису, но ещё и замечательному математику. В марте 1879 года он начал раз в неделю публиковать в журнале «Ярмарка тщеславия» по три задания в форме броских фраз: «Turn POOR into RICH» — «Преврати бедного в богатого», «Evolve MAN from APE» — «Выведи человека из обезьяны», «Make TEA HOT» — «Сделай чай горячим». В том же году он выпустил брошюру «Дублеты», подробно описал в ней правила и предложил читателям попрактиковаться на нескольких десятках примеров.

Александр Пиперски, "Из мухи — слона", «Квантик» №2, 2019 и №3, 2019

Сегодня мы научимся реализовывать на SQL волновой алгоритм, решив заодно классический пример из этой игры для конкретного словаря.

Как найти самые "горячие" запросы на вашем PostgreSQL-сервере? Поискать их в логе и проанализировать план или воспользоваться расширением pg_stat_statements.

А если в лог попадает миллион запросов за сутки?.. Тогда любое значение лимита pg_stat_statements.max окажется недостаточно велико, чтобы собрать правдивую статистику. Так давайте собирать эту статистику прямо с планов!

Но для некоторых сервисов СБИС нам в "Тензоре" производительность запросов к базе настолько важна, что auto_explain.log_min_duration приходится выставлять в единицы миллисекунд - и вот они, миллионы планов... Как не потеряться в них?

При реализации некоторых прикладных задач в рамках экосистемы СБИС случается сталкиваться с неочевидными возможностями PostgreSQL, которые позволяют вместо сложной логики создать решение "в один ход".

Сегодня на примере вполне реальной задачи рассмотрим такие возможности оператора INSERT ... ON CONFLICT.

В первой части серии статей про работу с адресами по КЛАДР мы научились импортировать данные этого справочника к себе в базу и превращать их во что-то более удобное для дальнейшей работы.

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

Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение deja vu, что вот ровно этот же кусок плана ты уже где-то раньше видел...

Пролистываешь выше - и таки-да, вот он рядом - но почему он там оказался, и как выйти из Матрицы самому и помочь коллегам?

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

Таким видом у нас в стране можно считать код по справочникам КЛАДР или ФИАС.

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

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

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

Поэтому сегодня рассмотрим некоторые типичные ситуации, в которых разработчики иногда принимают не самые оптимальные решения, гоняя по сети мегабайты трафика при общении с сервером PostgreSQL - а заодно посмотрим, как можно увидеть такую ситуацию в плане с помощью explain.tensor.ru и подумаем над вариантами, как сделать подобное взаимодействие более эффективным.

Однажды при выполнении достаточно тривиального запроса:

UPDATE tbl SET val = val + 1 WHERE id IN (1, 2, 3)

... вы получаете ошибку ERROR: deadlock detected

Но почему? Ведь еще вчера все успешно работало!

И что с этим теперь делать? Давайте разбираться.

Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросов explain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.

Иногда в интерфейсе наших приложений СБИС возникает необходимость "сгруппировать" часть записей в некотором списке (например, служебные сообщения в чате, контакты и телефонные звонки).

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