Как индекс может замедлить запрос?

Сейчас разберём интересный парадокс: почему индекс может замедлить выполнение запроса? 🤔

Обычно индекс ускоряет поиск данных, но есть ситуации, когда его использование ведёт к ухудшению производительности. Давайте рассмотрим несколько таких случаев.

🚀 1. Неправильный выбор индекса
Допустим, у нас есть индекс по created_at, а мы выполняем запрос:

SELECT * FROM orders WHERE YEAR(created_at) = 2024;

Проблема в том, что функция YEAR(created_at) делает так, что индекс не используется эффективно. База данных должна пройтись по всем строкам, применяя функцию ко всем значениям. Лучше переписать так:

SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Теперь индекс сможет работать оптимально. 🔥

🏗 2. Слишком широкий индекс (Over-indexing)
Если у нас слишком много индексов на таблице, это приведёт к замедлению операций INSERT, UPDATE, DELETE. Почему? Потому что каждый раз при изменении данных БД должна обновлять все индексы. Поэтому добавляйте индексы осознанно!

📦 3. Низкая селективность индекса
Допустим, у нас есть индекс по status, но всего три возможных значения ('new', 'processing', 'done'). Если в таблице миллионы строк, но мало уникальных значений, индекс бесполезен — оптимизатор может решить, что проще выполнить полный скан таблицы.

⚠️ 4. Ошибка с покрывающим индексом
Иногда индекс покрывает все нужные колонки (INDEX(col1, col2, col3)), но запрос выбирает ещё одну (col4). Тогда база вынуждена обращаться к самой таблице, что убивает эффективность индекса.

📌 Вывод: индекс — мощный инструмент, но его неправильное использование может навредить. Перед добавлением индексов всегда анализируйте планы выполнения запросов (EXPLAIN в MySQL, EXPLAIN ANALYZE в PostgreSQL).

📲 Мы в MAX

#db

👉 @database_info

Визуализация SQL-запросов

Ментальная модель, помогающая представить, как выполняются SQL-запросы.

Фактическая последовательность выполнения может отличаться от этой модели из-за стратегий оптимизации, применяемых оптимизатором запросов.

📲 Мы в MAX

#db

👉 @database_info

SQL JOINs наглядно: как работать с объединением таблиц

Хотите лучше понимать SQL JOIN? Вот наглядная шпаргалка с примерами и визуализацией!

🔹 INNER JOIN – пересечение двух таблиц, возвращает только совпадающие строки.

SELECT *
FROM A
INNER JOIN B ON A.key = B.key;

🔹 FULL JOIN – объединяет все данные из обеих таблиц, заполняя пропущенные значения NULL.

SELECT *
FROM A
FULL JOIN B ON A.key = B.key;

🔹 FULL JOIN с фильтрацией NULL – выбирает только строки, которые есть только в одной из таблиц.

SELECT *
FROM A
FULL JOIN B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL;

🔹 LEFT JOIN – возвращает все строки из A и совпадающие строки из B.

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key;

🔹 LEFT JOIN (только уникальные в A) – возвращает только строки из A, которых нет в B.

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

🔹 RIGHT JOIN – аналогично LEFT JOIN, но с приоритетом B.

SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key;

🔹 RIGHT JOIN (только уникальные в B) – выбирает строки, которые есть в B, но отсутствуют в A.

SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

Сохраняйте в закладки и пользуйтесь! ⚡

📲 Мы в MAX

#db

👉 @database_info

В новой статье на Хабре Алексей Кременьков, бэкенд-разработчик в Яндекс 360, рассказывает о создании и эволюции сервиса динамического шардирования Sharpei для масштабирования PostgreSQL под нагрузкой в 300К+ RPS.

Пошагово разберём, как:

• Создали собственный инструмент для управления 700+ шардами PostgreSQL
• Справились с пиковыми нагрузками при миграциях
• Автоматизировали перенос пользователей между шардами и переехали в облако без даунтайма

Сервис Sharpei позволил Яндекс Почте перейти к гибкому горизонтальному масштабированию, полностью автоматизировать управление шардами и добиться четырёх девяток отказоустойчивости.

↘️ Подробнее читайте на Хабре

Оптимизация запросов: как найти узкое место? 🔍

Привет, коллеги! Сегодня я покажу вам, как находить узкие места в SQL-запросах и оптимизировать их. Если ваш запрос работает медленно, скорее всего, проблема в одном из трех мест:

1️⃣ Неверные индексы
- Проверьте EXPLAIN ANALYZE, если используется Seq Scan вместо Index Scan, значит, индексы либо отсутствуют, либо неэффективны.
- Добавьте индексы на часто фильтруемые и соединяемые поля.

2️⃣ Проблемные JOIN'ы
- Проверьте, какие типы JOIN используются. NESTED LOOP JOIN может быть проблемой на больших таблицах.
- Используйте HASH JOIN или MERGE JOIN, если это возможно.

3️⃣ Громоздкие операции (GROUP BY, ORDER BY, DISTINCT)
- Сортировка и группировка требуют много ресурсов.
- Можно ли заменить DISTINCT на EXISTS?
- Используйте индексированные столбцы в ORDER BY.



📲 Мы в MAX

#db

👉 @database_info

🧠 Почему PostgreSQL иногда "зависает" на UPDATE и как это пофиксить

Сегодня я расскажу про одну интересную особенность PostgreSQL, с которой сталкивался лично: внезапные подвисания при UPDATE большого количества строк. Причём CPU почти не загружен, а запрос как будто "висит".

📌 Проблема часто кроется в отсутствии индекса на колонку фильтра в WHERE. Пример:

UPDATE orders SET status = 'archived' WHERE created_at < '2022-01-01';

Если на created_at нет индекса, то PostgreSQL делает sequential scan всей таблицы. А теперь внимание: если в таблице много "мертвых" строк, которых ещё не убрал autovacuum, то PostgreSQL должен:

1. Прочитать кучу ненужных версий строк (MVCC).
2. Проверять видимость каждой строки.
3. Иногда ещё и ждать завершения других транзакций, держащих старые снапшоты.

🛠 Что делать:
- Проверить наличие индекса на колонку фильтра:

  CREATE INDEX idx_orders_created_at ON orders(created_at);
  

- Проверить состояние autovacuum:

  SELECT relname, n_dead_tup, last_vacuum, last_autovacuum 
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
  

- Можно вручную запустить:

  VACUUM ANALYZE orders;
  

🔥 Лайфхак: если UPDATE всё равно медленный, попробуй его разбить на батчи по 10 000 строк. Это снизит нагрузку и ускорит выполнение.

📲 Мы в MAX

#db

👉 @database_info

📊 Зачем DBA нужно уметь читать планы выполнения запросов (EXPLAIN)?

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

Когда приходит запрос от разработчика: "Почему тормозит?" - ты открываешь EXPLAIN (ANALYZE, BUFFERS) и видишь:

Seq Scan on users  (cost=0.00..44231.00 rows=1000000 width=64)
  Filter: (status = 'active')

И тут всё понятно: фильтрация идёт по колонке без индекса, Postgres делает полный проход по таблице. Один CREATE INDEX - и запрос летит 🚀

Но не всё так просто. Иногда план говорит:

Index Scan using idx_users_status on users
  Index Cond: (status = 'active')

А запрос всё равно медленный. Почему?

➡️ Buffers: shared hit=5 read=100000 dirtied=0 - вот оно. Индекс-то используется, но данные не в кэше, приходится читать с диска. А диск медленный. Решение? Подумать о горячем кэше, пачке RAM или REINDEX, если индекс раздулся.

Каждый EXPLAIN - как рентген. Не читаешь - лечишь наугад.

📲 Мы в MAX

#db

👉 @database_info

Как работают джойны SQL?

На приведенной ниже схеме подробно показано, как работают 4 типа объединений SQL.

🔹INNER JOIN возвращает совпадающие строки в обеих таблицах
🔹LEFT JOIN возвращает все записи из левой таблицы и соответствующие записи из правой
🔹RIGHT JOIN возвращает все записи из правой таблицы и соответствующие записи из левой
🔹FULL OUTER JOIN возвращает все записи, в которых есть совпадения, в левой или правой таблице

📲 Мы в MAX

#db

👉 @database_info

Сегодня расскажу вам про одну часто недооценённую, но крайне полезную SQL-фишку — CROSS APPLY в SQL Server (и его аналог в других СУБД — LATERAL).

Когда обычный JOIN бессилен

Допустим, у нас есть таблица Orders, и мы хотим для каждой строки выбрать топ-1 продукт по сумме, но выборка зависит от строки — тут уже обычный JOIN не справится. Вот пример, где приходит на помощь CROSS APPLY:

SELECT 
    o.OrderID,
    p.ProductName,
    p.Amount
FROM Orders o
CROSS APPLY (
    SELECT TOP 1 *
    FROM Products p
    WHERE p.OrderID = o.OrderID
    ORDER BY p.Amount DESC
) p;

Что делает CROSS APPLY?

Он буквально говорит: «Для каждой строки из Orders выполни подзапрос с её параметрами». Это похоже на foreach, где внутренняя выборка может меняться в зависимости от строки внешней таблицы.

Аналог в PostgreSQL:

SELECT 
    o.order_id,
    p.product_name,
    p.amount
FROM orders o,
LATERAL (
    SELECT *
    FROM products p
    WHERE p.order_id = o.order_id
    ORDER BY p.amount DESC
    LIMIT 1
) p;

🔥 Используйте CROSS APPLY, когда:
- Нужна подстрочная логика внутри запроса
- Не получается реализовать через обычный JOIN
- Вы работаете с функциями, которые возвращают таблицу (TVF)

📲 Мы в MAX

#db

👉 @database_info

SQL. Как работать с датами в SQL Server

Из строки в дату и обратно
Приведение строки к дате

-- формат dd.mm.yyyy
select try_convert(date, isnull(@fielddateBirth,'01.01.1980'), 104)

-- формат yyyy-mm-dd
select try_convert(date, isnull(@fielddateBirth,'1980-01-01'), 23)

Приведение даты к строке

-- формат dd.MM.yyyy
select try_convert(nvarchar(16), getdate(), 104)

-- формат yyyy-mm-dd
select convert(nvarchar, isnull(dateBirth,'1900-01-01'), 23)

Приведение даты к строке в заданном формате:

select format(getdate(), 'dd.MM.yyyy HH:mm')

Полезные функции по работе с датами

select getdate() -- получить текущую дату

select dateadd(day, 7, getdate()) -- добавить 7 дней к дате

select dateadd(month, -2, getdate()) -- отнять 2 месяца от даты

select datediff(minute, date1, date2) -- получить разницу в минутах между date2 и date1

SELECT DAY(GETDATE())       -- 28  получить день даты (аналогично month, year, week)

SELECT DATENAME(month, GETDATE())       -- July получить название месяца 

SELECT DATEPART(month, GETDATE())       -- 7 получить часть даты (например, месяц, год или день)

SELECT DATEFROMPARTS(2017, 7, 28)       -- 2017-07-28  строит дату из частей

SELECT ISDATE('2017-07-28')     -- 1 - проверка является ли строка датой 

Полезные вычисления по датам (типовые задачи)

-- начало и конец недели
SET DATEFIRST 4   /* or use any other weird value to test it */
DECLARE @d DATETIME = GETDATE()
SELECT
  @d ThatDate,
  cast( DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) as date) Monday,
  cast(DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) as date) Sunday


-- начало месяца
SELECT DATEADD(month, DATEDIFF(month, 0, @d), 0) AS StartOfMonth

-- номер недели в году 
set datefirst 1;
select datepart(week, '2017-02-01');

-- получение дня недели
select datename(dw,getdate()) --Thursday
select datepart(dw,getdate()) --2

📲 Мы в MAX

#db

👉 @database_info

🧩 Сегодня покажу вам простой, но крайне полезный приём, как находить “тяжёлые” запросы в PostgreSQL, которые тормозят базу.

📌 Если у вас база под нагрузкой, и “что-то всё стало медленно”, первым делом проверьте:

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 5;

Этот запрос показывает топ-5 самых долгих активных запросов. Обратите внимание на query_start - именно он поможет понять, кто завис и тормозит остальных.

А если хотите посмотреть историю медленных запросов за последние часы/дни - подключайте pg_stat_statements:

SELECT 
  calls, 
  total_time, 
  mean_time, 
  query 
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

🔍 Тут видно, какие запросы в сумме "съели" больше всего времени. И это гораздо честнее, чем смотреть только на mean_time или calls по отдельности.

💡Совет: подключите pg_stat_statements на проде и делайте такой анализ хотя бы раз в неделю. Это поможет находить проблемные места в приложении до того, как начнётся пожар.

📲 Мы в MAX

#db

👉 @database_info

🎯 Сегодня покажу простой способ ускорить запросы в PostgreSQL, даже не трогая сам SQL-код.

Часто вижу, как разработчики и админы оптимизируют запросы, играя с индексами или переписывая JOIN'ы. Но забывают про один мощный инструмент — ANALYZE.

ANALYZE обновляет статистику по таблицам. Эта статистика — хлеб для планировщика запросов. Если она устарела, PostgreSQL может выбрать неэффективный план, даже если у вас всё индексировано как надо.

👨‍🔧 Простой пример:

ANALYZE my_big_table;

Запускаешь — и вдруг сложный JOIN срабатывает в разы быстрее. Потому что PostgreSQL теперь знает, какие там объемы данных, сколько уникальных значений в колонках и т.п.

🧠 Совет: если ты регулярно заливаешь данные в таблицы (например, через ETL или бэкапы) — добавь ANALYZE в конец процедуры. Это дёшево, но может дать мощный прирост производительности.

Можно даже так:

VACUUM ANALYZE my_big_table;

Так ты и "мусор" уберёшь, и статистику обновишь за один проход.

📲 Мы в MAX

#db

👉 @database_info

🧩 Как сделать backup PostgreSQL с минимальной нагрузкой на прод?

Сегодня покажу один из самых эффективных способов бэкапа PostgreSQL — с помощью pg_basebackup + реплики.

Сценарий: у нас есть продовый PostgreSQL и настроенная горячая реплика (streaming replication). Зачем использовать реплику для бэкапа?

Причины:
- 💡 На проде бэкап может замедлить отклик приложения.
- 🔁 Реплика — отличный способ разгрузить основной сервер.
- ⏱ Бэкап с pg_basebackup возможен только на стопнутой БД или через репликацию.

Как сделать:

pg_basebackup -h replica.host -U repl_user -D /backup/pg -F tar -z -P

Пояснения:
- -h — адрес реплики
- -U — пользователь с правами репликации
- -D — куда класть бэкап
- -F tar -z — формат архива и сжатие
- -P — прогресс в консоли

Важно:
Пользователь repl_user должен быть прописан в pg_hba.conf и иметь роль REPLICATION.

А если добавить в cron, то получишь стабильный ночной бэкап без боли.

📲 Мы в MAX

#db

👉 @database_info

🚀 Сегодня покажу, как быстро диагностировать «тормоза» в PostgreSQL - без всяких внешних тулов и дополнительных логов. Только pg_stat_activity и немного здравого смысла.


Пользователи жалуются - "всё тормозит". Как понять, что именно? Открываем сессию в psql от суперпользователя и запускаем:

SELECT pid, state, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

📌 Что это нам даёт:
- Видим все активные (и зависшие) запросы.
- Сколько времени они уже выполняются (duration).
- На чём конкретно «висят»: CPU, IO, Lock, Client и т.д. (wait_event_type + `wait_event).

Пример:

wait_event_type: Lock
wait_event: relation

→ Сразу ясно: кто-то держит блокировку на таблицу, и все остальные ждут.


🔥Чтобы найти виновника, можно запустить:

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
  AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
  AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
  AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
  AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
  AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
  AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
  AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Этот запрос покажет, кто кого блокирует, и с каким запросом.

🙌 Это простая, но мощная техника диагностики. Помогала мне не раз в проде - особенно, когда времени мало, а багов много.

Ты пользуешься pg_stat_activity в проде? Или сразу лезешь в лог? Расскажи в комментах!

📲 Мы в MAX

#db

👉 @database_info