🐘 PostgreSQL vs MySQL: магия индексов, о которой я раньше не знал Думал, что знаю всё про индексы. Ну, MySQL — там просто: поле в WHERE → индекс → жизнь прекрасна. Но PostgreSQL решил устроить мне квест. 💡 Немного предыстории Я работаю с аналитиками: они собирают отчёты, часто на временных таблицах и с не самым оптимальным SQL. Оптимизация у них в приоритете где-то на уровне «когда-нибудь». Запросы начали тормозить, и я пошёл на охоту за индексами. 🔍 Запрос select distinct date_trunc('day', h."timestamp")::date as date, h.hypervisor_hostname from public.os_hypervisor h join public.reporting_dates d on d.d = date_trunc('day', h."timestamp")::date where date_trunc('day', h."timestamp")::date >= '2023-01-01' and h.os_aggregate_id = '570'; Название таблиц и запрос приведены для иллюстрации примера, все совпадения случайны. public.os_hypervisor - здесь мы храним данные о гипервизорах, каждый день записывается некоторое количество строк. public.reporting_dates — это набор дат, временных отсечек. Вот часть результата EXPLAIN -> Parallel Seq Scan on os_hypervisor h (cost=0.00..9537.64 rows=6869 width=28) "Filter: ((os_aggregate_id = '570'::bigint) AND ((date_trunc('day'::text, ""timestamp""))::date >= '2023-01-01'::date))" EXPLAIN показал Parallel Seq Scan — значит, таблицу читают несколько воркеров одновремено, распределя нагрузку. Parallel Seq Scan конечно по лучше, Seq Scan - но хочется то не всю большую таблицу перебирать. Так же стоит обратить внимание на cost - это абстрактная единица стоимости выполнения запроса. Чем больше, тем дольше выполняется. И тут для запроса всё ещё нужно перебрать 6869 строк. ⚙️ Пробую классику Ставлю индексы на timestamp и os_aggregate_id: CREATE INDEX idx_ts ON public.os_hypervisor ("timestamp"); CREATE INDEX idx_agg_id ON public.os_hypervisor (os_aggregate_id); Смотрим результат EXPLAIN: -> Index Scan using idx_agg_id on os_hypervisor h (cost=0.42..8102.64 rows=16655 width=28) Index Cond: (os_aggregate_id = '570'::bigint) PostgreSQL радостно берёт индекс по os_aggregate_id… но timestamp игнорирует. 🤔 Хотя cost немного снизился, но увеличилось количество строк. (Это связано с понятие высокой/низкой кардинальность полей - эта тема отдельного поста) 💥 Озарение В фильтре мы не просто берём поле, а делаем date_trunc(... )::date. Значит, и индекс нужен по выражению, а не по чистому полю: CREATE INDEX idx_ts_trunc ON public.os_hypervisor ((date_trunc('day', "timestamp")::date)); EXPLAIN после этого: cost=0.42..171.80 против прежних 8102.64. А количество строк снизится rows=60. В EXPLAIN так же отлеживать примерное время выполнения. При Parallel Seq Scan было time=0.181..24.965, а стало time=0.029..0.070. Как вам такой разрыв? ⚠️ Финальный нюанс В другой таблице индекс не сработал, потому что в условии было date_trunc('day', h."timestamp") >= '2023-01-01' без ::date. Добавил приведение → планировщик взял индекс. 📌 Вывод В PostgreSQL индексы по выражениям — мощная штука. 1. Но если в запросе нет полного совпадения выражения с индексом (даже в типах) — он их проигнорирует. 2. Все таки, всегда лучше погонять EXPLAIN - потому что не всегда очевидно по какому поля из условия индекс будет лучше работать. Но есть некоторая стратегия, но об этом в других постах. ❤️ — интересно, давай ещё 🔥 — хочу разбор реальных кейсов 🤝 — отдельный пост про PostgreSQL-магии #MyDevAdventure #DataEngineer #PostgresMagic