18 сентября я добавлял в карту парковок второй город. Миграция завела колонку city у таблицы парковок, дальше надо было научить API отдавать данные по одному городу.
Запрос, который показывает текущую занятость, устроен так: сначала подзапрос «самый свежий замер по каждой парковке», потом join обратно к замерам, чтобы достать строки целиком.
Фильтр по городу я поставил в подзапрос. Это же очевидно: сужаем выборку как можно раньше, чтобы агрегат считался по меньшему объёму. Даже комментарий написал, уверенный такой:
# Сузить до города дешевле на подзапросе: иначе max(time) считается # по всем парковкам мира, а потом большая часть отбрасывается. latest = latest.join(ParkingSpot, ParkingSpot.id == ParkingOccupancy.parking_id).filter( ParkingSpot.city == city )
На тестовой базе отработало мгновенно. Там 750 строк.
На проде
Запрос завис.
Не «стал медленным», а именно завис: браузер отваливался по таймауту раньше, чем приходил ответ. Пошёл мерить на боевой базе.
без join 0,71 с с join 53,7 с
Семьдесят пять раз. Одна добавленная строчка join, которая по всем учебникам должна была ускорить.
parking_occupancy — гипертаблица TimescaleDB: на тот момент 4,6 млн строк, разложенных по 78 чанкам. Планировщик с добавленным join переставал видеть её как единое целое и уходил в nested loop с bitmap-сканом по каждой парковке в каждом чанке. Двести десять парковок на семьдесят восемь чанков.
Чинится просто. Подзапрос вернул в исходный вид, а по городу стал отсекать уже готовый результат:
# ВАЖНО: город здесь НЕ фильтруется. # Замерено на боевой базе: без join 0,71 с, с join 53,7 с subq = ( self.db.query( ParkingOccupancy.parking_id, func.max(ParkingOccupancy.time).label('max_time'), ) .group_by(ParkingOccupancy.parking_id) .subquery() )
А фильтр уехал в цикл по результату, где строк меньше тысячи и стоимость нулевая:
for occ in latest_rows: spot = spots.get(occ.parking_id) if city and (spot is None or spot.city != city): continue
Выглядит как антипаттерн: тащим всё из базы и отбрасываем в приложении. Но «всё» здесь это одна строка на парковку, меньше тысячи штук, а экономия — пятьдесят три секунды.
Сайт заработал, я закоммитил, оставил в коде измеренные цифры вместо прежнего уверенного комментария и забыл об этом на две недели.
Через две недели
Сел писать эту статью и решил снять планы запросов, чтобы показать разницу наглядно. Беру оба варианта, делаю EXPLAIN.
Плохой вариант:
Finalize GroupAggregate (cost=89253.52..89304.19 rows=200 width=12) -> Gather Merge -> Partial HashAggregate -> Hash Join (cost=50.00..85210.80 rows=606611 width=12) Hash Cond: (chunk.parking_id = parking_spots.id) -> Parallel Append -> Parallel Seq Scan on _hyper_1_79_chunk -> Parallel Seq Scan on _hyper_1_78_chunk ...
Хороший вариант:
Finalize HashAggregate (cost=87519.58..87521.58 rows=200 width=12)
Стоимость 89 304 против 87 521. Разница в два процента.
Никакого nested loop. Hash Join, параллельное сканирование чанков, всё аккуратно. Запустил с замером времени, по два прогона на вариант и в обратном порядке, чтобы не поймать разницу на прогретом кэше:
с join 634 мс, 529 мс без join 420 мс, 381 мс
Запрос, который висел пятьдесят три секунды, теперь отрабатывает за полсекунды. Я в нём ничего не менял — я просто выполнил ровно тот же текст, который две недели назад заваливал прод.
Что произошло
Статистика.
Колонку city завела миграция того же дня, 18 сентября. Фильтр по ней я написал через несколько часов. Автоанализ по новой колонке к тому моменту пройти не успел, и планировщик не имел ни малейшего представления о том, насколько city = 'moscow' селективен. В такой ситуации он подставляет значение по умолчанию и строит план на догадке.
Сегодня статистика есть:
SELECT attname, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename='parking_spots' AND attname='city'; attname | n_distinct | most_common_vals | most_common_freqs ---------+------------+--------------------+------------------------ city | 2 | {melbourne,moscow} | {0.71657753,0.28342247}
Два значения, частоты 0,72 и 0,28. Автоанализ собрал её сегодня в 07:55. Теперь планировщик знает, что фильтр отрежет примерно четверть парковок, понимает, что городов всего два, и выбирает hash join вместо вложенного цикла.
Оговорюсь честно: я не могу доказать, что 18 сентября статистики не было — снимка pg_stats на тот день у меня нет. Но колонка появилась в тот же день, автоанализ запускается по объёму изменений и мгновенным не бывает, а сегодняшний план на той же базе и том же запросе ведёт себя принципиально иначе. Это самое правдоподобное объяснение из тех, что я могу подкрепить.
Чем это неприятно
У меня в коде лежит комментарий:
# Замерено на боевой базе: без join 0,71 с, с join 53,7 с
Он был правдой 18 сентября. Сегодня он читается как утверждение о природе вещей: «join по гипертаблице стоит 53 секунды». А это неверно уже две недели.
Через полгода кто-нибудь (скорее всего я) прочитает этот комментарий, поверит и не станет трогать работающий код. Что, в общем, нормально. Хуже другое: он унесёт из него ложный вывод — «join с гипертаблицей всегда катастрофа» — и будет носить его с собой в другие проекты.
Правильный вывод другой. План запроса не свойство запроса. Это свойство запроса, статистики и данных в конкретный момент времени. Один и тот же SQL на одной и той же базе выполняется за 53 секунды и за полсекунды в зависимости от того, успел ли автоанализ посмотреть на колонку.
Остался ли смысл в правке
Да, и это единственная часть истории, которая не изменилась.
Текущий вариант не зависит от планировщика вообще. Он считает агрегат по гипертаблице одним понятным способом и фильтрует меньше тысячи строк в приложении. Что бы ни думала статистика, сколько бы городов ни добавилось, сработал автоанализ или нет — поведение одинаковое.
Старый вариант сегодня работает нормально, но его нормальность держится на том, что планировщик хорошо информирован. Добавится третий город, перельются данные, пройдёт массовая вставка и собьёт статистику — и он снова может уехать в плохой план. Молча, в проде, без изменения кода.
Разница между вариантами сегодня 150 миллисекунд в пользу того, который я оставил. Но выбирал я его не за эти 150 миллисекунд, а за то, что у него нет сценария, в котором он внезапно становится в семьдесят пять раз хуже.
Что забрать с собой
Тестовая база в 750 строк не проверяет планы. Она проверяет синтаксис и логику. Любая проблема, которая начинается со слов «планировщик выбрал», на таком объёме невидима по определению: при таком размере таблицы все планы одинаково мгновенны.
Статистика по свежесозданной колонке — это пустота. Если миграция добавляет колонку и в тот же день по ней пишется фильтр, планировщик работает вслепую. ANALYZE на таблицу после миграции стоит секунды и снимает целый класс сюрпризов.
Измеренная цифра в комментарии живёт меньше, чем кажется. Я считал, что оставляю в коде твёрдый факт вместо прежнего самоуверенного рассуждения. Факт протух за две недели. Если пишете замер в комментарий, пишите рядом дату и условия — иначе через полгода он прочитается как закон природы.
Перемеряйте собственные выводы, прежде чем их публиковать. Я собирался написать статью «join по гипертаблице убивает план, вот доказательство». Пошёл за доказательством и обнаружил, что его больше нет. Если бы я просто пересказал коммит двухнедельной давности, я бы уверенно научил читателей неправильному.
И маленькое наблюдение напоследок. У гипертаблицы родительская таблица пустая, данные лежат в чанках, поэтому привычные места врут:
SELECT relname, n_live_tup, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'parking_occupancy'; relname | n_live_tup | last_autoanalyze -------------------+------------+------------------ parking_occupancy | 0 |
Ноль строк и никогда не анализировалась — при 5,1 млн строк в 80 чанках. Статистика живёт на чанках, и собрана она сейчас на двенадцати из восьмидесяти: автоанализ срабатывает по объёму изменений, а старые чанки давно не пишутся.
Это шестая статья по следам одного проекта — карты загруженности парковок parkout.ru. Предыдущие: про чёрную карту без единой ошибки в консоли, про −1000% занятости в собственном датасете, про сам датасет, про метрику, которая стала хуже и от этого правдивее и про сертификат, из-за которого сайт лежал 21 день.