История одного SQL расследования

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

Предыстория

Чтобы было понятно о чём речь, я расскажу совсем немного о VWO. Это платформа, с помощью которой можно запускать разные таргетированные кампании на своих сайтах: проводить A/B эксперименты, отслеживать посетителей и конверсии, делать анализ воронки продаж, отображать тепловые карты и проигрывать записи визитов.

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

Используя платформу, можно сделать произвольный запрос на большом наборе данных. Вот простенький пример:

Показать все клики на странице "abc.com"
ОТ <даты d1> ДО <даты d2>
для людей, которые
использовали Chrome ИЛИ
(находились в Европе И использовали iPhone)

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

Медленный запрос

Клиент, о котором идет речь, пытался сделать что-то, что интуитивно должно работать быстро:

Покажи все записи сессий
для пользователей посетивших любую страницу
с урлом, где есть "/jobs"

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

Предварительное следствие

Давайте посмотрим, что же происходит в базе данных. Ниже приведен исходный медленный SQL-запрос:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

А вот тайминги:

Планируемое время: 1.480 ms
Время выполнения: 1431924.650 ms

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

Давайте поизучаем запрос дальше. Как видно, он делает JOIN трёх таблиц:

1. sessions: для отображения сессионной информации: браузер, юзер агент, страна и так далее.
2. recording_data: записанные урлы, страницы, продолжительность визитов
3. urls: чтобы избежать дублирования чрезвычайно больших урлов, мы храним их в отдельной таблице.

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

В поисках улик

При ближайшем рассмотрении мы видим, что что-то в конкретном запросе не так. Стоит присмотреться к этой строке:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

Первая мысль была, что возможно, из-за ILIKE на всех этих длинных урлах (у нас есть более 1,4 миллиона уникальных URL-адресов, собранных для этого аккаунта) производительность может проседать.

Но, нет — дело не в этом!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

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

Следующий подозреваемый по списку — несколько JOIN. Возможно, их чрезмерное использование привело к замедлению? Обычно JOIN'ы — самые очевидные кандидаты на проблемы с производительностью, но я не верил, что наш случай типовой.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

И это так же был не наш случай. JOIN'ы оказались весьма быстрыми.

Сужаем круг подозреваемых

Я был готов начать менять запрос для достижения любых возможных улучшений производительности. Мы с командой разработали 2 главных идеи:

• Использовать EXISTS для URL подзапроса: Мы хотели ещё раз проверить, нет ли проблем с подзапросом для урлов. Один из способов этого добиться — просто использовать EXISTS. EXISTS может сильно улучшить производительность так как заканчивается сразу, как только находит единственную строку по условию.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

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

• Перемещаем подзапрос в CTE : если запрос быстр сам по себе, мы можем просто сначала рассчитать быстрый результат, а затем предоставить его основному запросу

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Но и это было всё ещё очень медленно.

Находим виновника

Всё это время перед глазами мелькала одна мелочь, от которой я постоянно отмахивался. Но поскольку уже больше ничего не оставалось, я решил взглянуть и на неё. Я говорю про && оператор. Пока EXISTS просто улучшил производительность, && был единственным оставшимся общим фактором во всех версиях медленного запроса.

Глядя на документацию, мы видим, что && используется, когда нужно найти общие элементы между двумя массивами.

В оригинальном запросе это:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Что означает, что мы делаем поиск по шаблону по нашим урлам, затем находим пересечение со всеми урлами с общими записями. Это немного запутанно, поскольку «urls» здесь не ссылается на таблицу, содержащую все URL-адреса, а на столбец «urls» в таблице recording_data.

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

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

Там было несколько строк фильтров только из &&. Что означало, что эта операция не только была дорогостоящей, но и выполнялась несколько раз.

Я проверил это, изолировав условие

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

Этот запрос выполнялся медленно. Поскольку JOIN-ы быстрые и подзапросы быстрые, оставался только && оператор.

Вот только это ключевая операция. Нам всегда нужно искать по всей основной таблице URL-адресов, чтобы искать по шаблону, и нам всегда нужно находить пересечения. Мы не можем искать по записям урлов напрямую, потому что это просто айдишники ссылающиеся на urls.

На пути к решению

&& медленный, потому что оба сета огромны. Операция будет относительно быстрой, если я заменю urls на { "http://google.com/", "http://wingify.com/" }.

Я начал искать способ сделать в Postgres пересечение множеств без использования &&, но без особого успеха.

В конце концов, мы решили просто решить проблему изолированно: дай мне все urls строки, для которых урл соответствует шаблону. Без дополнительных условий это будет - 

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

Вместо JOIN синтаксиса я просто использовал подзапрос и развернул recording_data.urls массив, чтобы можно было напрямую применять условие в WHERE.

Самое важное здесь в том, что && используется для проверки, содержит ли данная запись соответствующий URL-адрес. Немного прищурившись, можно увидеть в этой операции перемещение по элементам массива (или строкам таблицы) и остановку при выполнении условия (соответствия). Ничего не напоминает? Ага, EXISTS.

Поскольку на recording_data.urls можно ссылаться извне контекста подзапроса, когда это происходит, мы можем вернуться к нашему старому другу EXISTS и обернуть им подзапрос.

Объединяя всё вместе, мы получаем окончательный оптимизированный запрос:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

И окончательное время выполнения Time: 1898.717 ms Пора праздновать?!?

Не так быстро! Сначала нужно проверить корректность. Я был крайне подозрителен в отношении EXISTS оптимизации, так как она меняет логику на более раннее завершение. Мы должны быть уверены, что мы не добавили неочевидную ошибку в запрос.

Простая проверка заключалась в выполнении count(*) и на медленных, и на быстрых запросах для большого количества разных наборов данных. Затем, для небольшого подмножества данных я проверил правильность всех результатов вручную.

Все проверки дали стабильно положительные результаты. Мы всё починили!

Извлеченные Уроки

Из этой истории можно извлечь немало уроков:

1. Планы запросов не рассказывают всю историю, но могут давать подсказки
2. Главные подозреваемые не всегда являются настоящими виновниками
3. Медленные запросы можно разбить, чтобы изолировать узкие места
4. Не все оптимизации по природе редуктивны
5. Использование EXIST, где это возможно, способно привести к резому росту производительности

Вывод

Мы прошли от времени запроса в ~24 минуты до 2 секунд — весьма серьёзный рост производительности! Хотя эта статья и вышла большой, все эксперименты, которые мы делали, произошли в один день, и по прикидкам, заняли от 1,5 до 2 часов для оптимизаций и тестирования.

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

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

Особая благодарность моим товарищам по команде Адитье Мишре, Адитье Гауру и Варуну Малхотре за мозговой штурм и Динкару Пандиру за то, что нашёл важную ошибку в нашем финальном запросе, прежде чем мы окончательно с ним распрощались!