Comments 29
В следующей серии научитесь делать частичный индекс с условием по статусу, правильно понимаю? Ну серьезно, это не тянет на статью, это максимум заметка в телеграм канале.
Полный перебор 800 тысяч строк
А план говорит про другое количество строк: Seq Scan on orders (cost=0.00..45892.00 rows=234521 width=312)
там дальше
Rows Removed by Filter: 567842
567842+234521=802363 строки
Я пригласил человека не потому что это сложная статья, а потому что это частая проблема у наших разработчиков.
И в следующий раз, после того как я сделаю show create table <> и увижу эту проблему, я просто скину ссылку на эту статью.
И в следующий раз, после того как я сделаю show create table <> и увижу эту проблему
SHOW CREATE TABLE не может показать ЭТУ проблему в принципе.
Смотря в каком контексте. Поясню подробно.
1. Вижу в slow queries запрос. Беру его.
2. Иду в таблицу (ы), которые он дергает. Здесь всего одна таблица. Смотрю ее описание. Иногда здесь и останавливаюсь. Честно
3. Если пункта 2 не хватило, то делается EXPLAIN.
Сам по себе SHOW CREATE TABLE показывает только DDL таблицы, но мы вообще-то шли в контексте обсуждения проблемы и статьи. Если ты видишь отсутствующий индекс в таблицe orders на поле created_at c таким запросом
SELECT * FROM orders WHERE created_at<$1;
нужно ли тебе что-то еще? гонять эксплейн? Очевидно же.
Так и здесь -- если я вижу подобную ситуацию, мне не всегда нужен explain.
Это лучше, чем очередная статья про АИ, но, честно говоря, АИ скорее всего тоже справился бы не хуже
Вот тогда коммент про AI. Я скопировал первый абзац из Что имеем, плюс запрос, плюс "Запрос выполняется очень долго, как можно ускорить?" chatgpt всё четко расписал, первым предложил частичный индекс, второй вариант - решение из статьи. И потом ещё несколько пунктов, тоже полезных
про AI ладно, главное чтобы не обработанная AI. Эта статья написана человеком, к ней не прикасался AI, приятно читать. Написано по-русски «составной индекс», а не “многоколоночный”, за одно это статья достойна похвалы. Статью можно обновить - дополнить решением с частичным индексом и будет совсем хорошо. Новую статью про частичный индекс писать не нужно, в хабе PostgreSQL уже есть свой «enfant terrible» pg_expecto, который бы так и сделал, хорошо, что ему это удаётся только раз в неделю
А может кто-нибудь обьяснить вообще почему сортировка по дате? Разве нет в таблице id с primarykey и автоинкрементом? Я всегда по нему сортирую. Или есть какой-то подводный камень?
Интересно, а где все это время был мониторинг? В ответ на 12 секунд открытия страницы прилетает тикет от человека а не от мониторинга.
Actual Time: 0.04..0.08 ms
40 миллисекунд. Не 12 секунд, не 4 секунды. 40 мс.
0.04 ms и 40 миллисекунд не одно и то же, они различаются... на 3 порядка.
По кейсу вообще есть мысль что просто нужны отдельные таблицы по статусам. Выполненные и невыполненные ордера.
Почему не секционирование?
Потому что таскать записи между партициями по полю, которое меняет значение (в процессе жизни записи), не считается best practices для высоконагруженных таблиц.
То есть, по-вашему, таскать из одной таблицы в другую - нормально, а из секции в секцию, которые по сути такие же таблицы (у нас же постгресс) - это уже моветон? Вот совсем не понимаю. Зато понимаю, что, как только потребуется что-то без оглядки на статус, придётся лезть в две отдельные таблицы и объединять полученные субнаборы. То есть от двух таблиц, как по мне, никакого профиту и геморрой на горизонте. Да и хранение одной сущности в нескольких таблицах - это ещё меньший best practices, кмк.
Если вы таскали из таблицы в таблицу, то вероятно, вы делали это руками или с автоматизацией. Т.е. это был контролируемый процесс против неконтролируемого процесса, который происходит в движке СУБД, когда он решает перенести данные.
Второе, представьте, вы решили секционировать таблицу по статусам. Как вы будете подчищать данные? У вас есть 100 тысяч заказов в работе, 30 тысяч в статусах оспаривается, возврат, гарантия, и, скажем, за 10 лет работы магазина 20 млн заказов в последней секции со статусом Done. Оп, и вы уже пишите какую-то логику чтобы подчищать Done и устраивать по ней вакуум. Без логики, целиком, нельзя - можно удалить заказ, который выполнен 5 лет назад, но более поздние заказы вам могут пригодиться для гарантии, спорных случаев, бухгалтерской проверки.
Поэтому я бы сделал вообще по-другому. Сделал бы секционирование по дате создания, и транкейтил бы их спустя 37 (49, 61, подставьте ваше значение) месяцев, предварительно убедившись, что внутри партиции нет заказов, зависших в любых статусах, кроме "завершен". Если там что-то есть - то перенес бы в отдельную таблицу, и все равно бы затранкейтил бы =)
Т.е. это был контролируемый процесс против неконтролируемого процесса, который происходит в движке СУБД, когда он решает перенести данные.
Не так. Контролируемый кодом против контролируемого движком. И я сторонник как раз того, чтобы контролировала СУБД.
представьте, вы решили секционировать таблицу по статусам. Как вы будете подчищать данные?
Я не понял, что имеется в виду под термином "подчищать". Но если вы имеете в виду удаление, то я этого делать не буду в принципе. А ещё я вспомню о субсекционировании.
Ого! Ничего себе. Давайте вспомним, что Postgres - версионник (mvcc). Это как-то меняет ваше утверждение?
Смотря на каком уровне мы оперируем. На логическом - запись имеет тот же ключ, эта та же запись. На физическом - старая будет отмечена как удаленная, а новая будет записана.
Теперь возвращаемся к моему утверждению, что в случае, если запись поменяет "прописку" из одной секции в другую, то это будет несколько дороже по сравнению со случаем, где работа ведется в одной партиции.
Хотя бы потому что вы накладываете 2 блокировки, на 2 партиции. И как я сказал, в пределе, под высокой нагрузкой, это может быть не самым оптимальным решением.
Секционированием можно отрезать прошлые периоды, которые редко запрашиваются. Актуальные и часто запрашиваемые данные держите в куче MAXVALUE. В результате получите сокращение набора данных для выборки, сокращение B-Tree, что должно дать неплохое ускорение. Нужно тестировать на вашей рабочей нагрузке и железе.
У вас на прод попала схема без индексов оптимизированных под запросы из приложения. Вы начинаете делать индексы под запросы сразу на проде, после жалобы, видимо пользователей. Что-то идёт не так. И дело не в индексах.
С...подключением?))
800000 строк, 12 секунд. У вас прод на Raspberry PI чтоль?
EXPLAIN ANALYZE — всегда. Не гадать, а смотреть план
Нет, ну стоящая мысль. Так гляди дойдем до того, что при ошибке просмотр логов помогает лучше отдела экстрасенсов
Как один индекс на created_at сократил время ответа API с 12 секунд до 40 мс