Комментарии 11
Не, ну рассказ в стиле "автомобиль съехал с горки не быстрее велосипеда, а потом мы завелись...". Как по мне - вообще ни о чём, чисто ради плюсадина в публикации.
объём данных — 2 ГБ (на основе реальных обезличенных данных);
В смысле вся база 2 ГБ? И запрос выполнялся 5 секунд? У меня возникают очевидные вопросы и получаются нехорошие выводы.
Хотел спросить про то как делали и делали ли секционирование на этом добре, но 2гб пожалуй снимают вопрос. :)
2 ГБ на шесть справочных таблиц — значимый объём, но он обусловлен полнотой нормативной информации, обязательной для межоператорских расчётов.
Длительность запросов связана с необходимостью джойна таблиц и фильтрации по актуальности диапазона действия записей — это продиктовано бизнес‑логикой. Секционирование тоже реализовано не ради общей оптимизации, а под операционные циклы и правила распределения данных между операторами. Так обеспечивается соответствие расчётов регламенту.
Ну, видите ли, 2Гб - это размер памяти самого маленького сервера. То есть вся база вполне влезает в память, а скорее всего, на тестовом сервере памяти было гораздо больше? То есть все операции делались в памяти, ничего не свопилось и не кэшировалось. Это нифига не нагрузочный тест.
5 секунд - это много для таких операций в памяти. Нет, конечно, я смогу написать такие запросы, чтобы выполнялись и дольше на 2 Гб, но, наверное, хотелось бы деталей. Может я плохо читал статью, но не нашёл описания тестов и параметров базы.
Тут на хабре есть один товарищ, который, не обращая внимания на ведро минусов, генерирует через AI десятки статей о своем чудо-плагине к БД и приводит детали тестов. Тесты у него - это хранимые процедуры, которые выполняются в цикле на самой базе, причём вместо одного запроса делается минимум три. То есть нагрузочные тесты сама база себе шлёт. Там тоже "методика"..
По православному НТ проводить не менее 4 часов, а лучше длинным циклом 24-48 часов. Так вы сможете отследить не только SLA по выполняемым запросам, но и оценить утечки памяти и нагрузку на CPU.
В целом статья про конкретную проблему в процессе миграции и ее решение. Не рокет сайнс, но новичкам и любопытным будет полезно
Да, главная цель статьи — показать с какими сложностями сталкиваешься, когда переходишь от учебных примеров к реальным проектным задачам. Мой опыт пригодится и тем, кто работает с «чистым» PostgreSQL, и тем, кто использует доработанные решения вроде Nexign Nord: базовые принципы переноса логики универсальны, но при работе с надстройками важно дополнительно учитывать специфику встроенных расширений. Подробнее про наши доработки в Nexign Nord делились в прошлой статье: https://habr.com/ru/companies/nexign/articles/930790/
Самое непонятное, как же gist мог оказаться быстрей btree при работе с обычными диапазонами. Впрочем, так как в статье нет ни примера кода, ни результатов его модификации, ни бенчмарков, то речь может идти о каком-то хитром частном случае или кривых запросах из ORM.
Использование GIST индекса – лучшее решение для поля диапазонного типа данных за счет применения операций Аллена (<<, &<, && и т.д.) для определения пересечений, вхождений и иных взаимоотношений диапазонов. С помощью него также реализуются проверки целостности данных при вставке/модификации с помощью ключевого слова EXCLUDE.
В рамках статьи рассматривались запросы на выборку данных, соответствующих диапазону актуальности. Так, первоначальный запрос вида
select *
from rate_plans rp
join dir_change_hist dch on rp.rtpl_id = dch.rtpl_rtpl_id
join serv_item_hist sih on dch.drch_id = sih.drch_drch_id
join srit_drcl sd on sih.srit_id = sd.srit_srit_id
join direction_classes dc on sd.drcl_drcl_id = dc.drcl_id
join dir_composition_hist dch2 on sd.drcl_drcl_id = dch2.drcl_drcl_id
where dch.status = 'O' and dch.del_date is null
and sih.status = 'O' and sih.del_date is null
and sd.del_date is null
and dch2.del_date is null
and dch.start_date <= current_timestamp
and dch.end_date >= current_timestamp
and sih.start_date <= current_timestamp
and sih.end_date >= current_timestamp
and dch2.start_date <= current_timestamp
and dch2.end_date>= current_timestamp
and rp.traffic_yn ='Y'
limit 1000;
Преобразовывался к
select *
from rate_plans rp
join dir_change_hist dch on rp.rtpl_id = dch.rtpl_rtpl_id
join serv_item_hist sih on dch.drch_id = sih.drch_drch_id
join srit_drcl sd on sih.srit_id = sd.srit_srit_id
join direction_classes dc on sd.drcl_drcl_id = dc.drcl_id
join dir_composition_hist dch2 on sd.drcl_drcl_id = dch2.drcl_drcl_id
where dch.status = 'O' and dch.del_date is null
and sih.status = 'O' and sih.del_date is null
and sd.del_date is null
and dch2.del_date is null
and now()::timestamp <@ dch.validity
and now()::timestamp <@ sih.validity
and now()::timestamp <@ dch2.validity
and rp.traffic_yn ='Y'
limit 1000;
Спасибо. Как и подозревалось, речь о кривых запросах. При оптимизированных запросах btree показывает в разы лучшую производительность, чем gist. Я этот вопрос уже подробно рассматривал на Хабре тут.
Только сейчас заметил, что ссылка не прикрепилась. Пост тут.
Если кратко, то претензия к этому:
select *
from rate_plans rp
join dir_change_hist dch on rp.rtpl_id = dch.rtpl_rtpl_id
where dch.status = 'O' and dch.del_date is null
and dch.start_date <= current_timestamp
and dch.end_date >= current_timestamp
and rp.traffic_yn ='Y'Планировщик в случае btree ничего не знает о диапазонах. Для него start_date и end_date никак логически не связаны и о том, что пересечения диапазонов недопустимы он тоже не знает. Но мы можем ему явно об этом сообщить, переписав запрос так:
SELECT *
FROM rate_plans RP
JOIN LATERAL (
SELECT *
FROM dir_change_hist D
WHERE D.rtpl_rtpl_id = RP.rtpl_id
AND D.status = 'O' AND D.del_date IS NULL
AND D.start_date <= CURRENT TIMESTAMP
ORDER BY D.start_date DESC
LIMIT 1
) DCH ON D.end_date > CURRENT TIMESTAMP
WHERE RP.traffic_yn = 'Y'Вот тут показано, что подобный поход выигрывает по производительности у gist в пять(!) раз.
Диапазонный тип данных в PostgreSQL: ускоряем запросы