🗄️ SQL и базы данных · 20 вопросов · ~45 мин · ⏱ Таймер 45:00 · Сложный ·

Оптимизация сложного SQL

Итоговый тест не просит выбрать «самый быстрый оператор» в вакууме. В каждом случае есть объём, распределение данных и план: иногда нужен составной или частичный индекс, иногда переписывание коррелированного подзапроса, предварительная агрегация, изменение модели либо отказ от бессмысленного ускорения маленькой таблицы. Решение оценивается по измеряемому узкому месту и цене изменений.

Отвечено: 0 из 20

⏱ --:--

💡 Инструкция: Выбери один ответ из четырёх. В тесте 20 вопросов и 45 минут. После завершения откроются общий процент, четыре тематические шкалы, правильные ответы и разбор каждого задания.

Вопрос 1 из 20

Почему запрос возвращает слишком много строк после JOIN orders и order_items?

SELECT o.id, o.total
FROM orders o
JOIN order_items i ON i.order_id=o.id;

ORDER BY создаёт копии LEFT JOIN создаёт декартово произведение независимо от условия ON Заказ повторяется по числу его позиций Индекс автоматически дублирует строки

Вопрос 2 из 20

Как получить заказы, у которых есть позиция sku=$1, не размножая заказы?

SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id=o.id AND i.sku=$1);SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id=o.id AND i.sku=$1) SELECT o.* FROM orders o JOIN order_items i ON true WHERE i.sku=$1;SELECT o.* FROM orders o JOIN order_items i ON true WHERE i.sku=$1 SELECT o.* FROM orders o JOIN order_items i ON i.order_id=o.id WHERE i.sku=$1;SELECT DISTINCT * FROM orders, order_items SELECT o.* FROM orders o WHERE o.id IN (SELECT i.id FROM order_items i WHERE i.sku=$1);SELECT o.* FROM orders o WHERE count(order_items)>0

Вопрос 3 из 20

Что такое проблема N+1 в выдаче ста заказов?

Один запрос обращается к N индексам и ещё раз читает таблицу LIMIT возвращает одну лишнюю строку для проверки следующей страницы В таблице после JOIN получается N+1 вычисляемый столбец Один запрос получает список, затем ещё по запросу на связанные данные каждого заказа

Вопрос 4 из 20

Почему OFFSET 500000 LIMIT 50 часто дорог?

SELECT * FROM events ORDER BY created_at,id OFFSET 500000 LIMIT 50;

OFFSET создаёт постоянный индекс LIMIT игнорирует ORDER BY OFFSET блокирует все записи Нужно прочитать и пропустить предыдущие строки

Вопрос 5 из 20

created_at и id заданы NOT NULL и вместе образуют уникальный порядок. Как переписать глубокую пагинацию?

GROUP BY created_at,id ORDER BY created_at DESC,id DESC LIMIT 50GROUP BY created_at,id LIMIT 50 WHERE (created_at,id) < ($1,$2) ORDER BY created_at DESC,id DESC LIMIT 50 WHERE created_at=$1 AND id<$2 ORDER BY created_at DESC,id DESC LIMIT 50WHERE created_at = max(created_at) WHERE id<$2 ORDER BY created_at DESC,id DESC OFFSET $1 LIMIT 50WHERE id NOT NULL OFFSET $1 LIMIT 50

Вопрос 6 из 20

В плане Nested Loop имеет loops=100000, а внутренний Index Scan возвращает rows=1. Что важно?

Nested Loop (actual rows=100000 loops=1)
  -> Seq Scan outer
  -> Index Scan inner (actual rows=1 loops=100000)

loops относится к числу столбцов Внутренний поиск повторён сто тысяч раз Большое число loops само по себе доказывает, что Nested Loop выбран ошибочно Index Scan выполнен только один раз

Вопрос 7 из 20

Оценка плана ожидала 20 строк, а фактически получила 2 000 000. К чему это может привести?

Index Scan (rows=20) (actual rows=2000000)

План всё равно вернёт правильные строки, а ошибка оценки влияет только на число в EXPLAIN Оценки влияют только на формат EXPLAIN Алгоритм выбран для ошибочно малого объёма Запрос станет синтаксически неверным

Вопрос 8 из 20

Что проверить первым при таком расхождении оценки и факта?

Сравнить только общее время выполнения, не проверяя estimated и actual rows Сразу отключить Nested Loop и принудительно выбрать Hash Join для всех запросов Актуальность статистики, корреляции столбцов и выражения, которые статистика плохо описывает Добавить отдельный индекс на каждый столбец условия и не обновлять статистику

Вопрос 9 из 20

В Sort указано Disk: 8GB. Что это означает?

Sort Method: external merge  Disk: 8388608kB

Сортировка вышла на временный диск Таблица занимает ровно 8GB Планировщик заранее зарезервировал для одного запроса 8 ГБ work_mem Узел Sort прочитал 8 ГБ постоянных страниц таблицы или индекса, независимо от временных файлов

Вопрос 10 из 20

Почему верхний Aggregate с временем 12 секунд не обязательно источник проблемы?

Верхний Aggregate выполняется первым и поэтому не включает работу дочерних узлов Время Aggregate отражает только вычисление агрегатной функции и не включает получение строк от потомков EXPLAIN не показывает время Он может ждать огромный поток от дорогого дочернего JOIN; план читают вместе с потомками

Вопрос 11 из 20

Запрос фильтрует tenant_id равенством, created_at диапазоном и сортирует created_at DESC. Какой индекс наиболее естественен?

CREATE INDEX ON events (payload); CREATE INDEX ON events (created_at, tenant_id); CREATE INDEX ON events (tenant_id, created_at DESC); CREATE INDEX ON events (tenant_id) WHERE created_at IS NULL;

Вопрос 12 из 20

Когда INCLUDE может помочь?

CREATE INDEX ON orders (customer_id, created_at DESC) INCLUDE (status,total);

Когда надо индексировать все поля таблицы без цены Когда столбец должен участвовать в уникальности Когда нужно добавить условие WHERE внутрь индекса Для покрытия возвращаемых полей без добавления их в ключ

Вопрос 13 из 20

Какой частичный индекс подходит очереди, где почти все задачи уже done?

CREATE INDEX ON jobs (id) WHERE status='done' CREATE INDEX ON jobs (status, payload) CREATE INDEX ON jobs (priority DESC, id) WHERE status='ready' CREATE INDEX ON jobs ((status IS NULL))

Вопрос 14 из 20

Почему условие запроса должно логически подразумевать предикат частичного индекса?

CREATE INDEX ix_ready ON jobs(id) WHERE status='ready';
SELECT * FROM jobs WHERE status IN ('ready','running');

Предикат влияет только на имя Без точного совпадения текста предиката PostgreSQL удаляет частичный индекс из схемы Любой частичный индекс подходит любому WHERE Иначе индекс не содержит всех нужных строк

Вопрос 15 из 20

Почему четыре отдельных индекса не всегда заменяют (a,b,c)?

Отдельные индексы можно последовательно применить так же, как единый ключ, включая итоговую сортировку Составной индекс хранит совместный порядок и может лучше поддержать равенства и сортировку одной операцией Составной индекс отличается от четырёх отдельных только размером, но не порядком ключей Планировщик объединяет отдельные индексы в один упорядоченный поток, эквивалентный (a,b,c)

Вопрос 16 из 20

Когда материализованное представление уместнее запроса по сырым данным?

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

Вопрос 17 из 20

Почему партиционирование не является автоматическим ускорителем?

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

Вопрос 18 из 20

Когда хранение order_total в orders может быть оправдано?

orders.total  <->  SUM(order_items.qty * order_items.price)

Когда сумма пересчитывается при каждом чтении без заметной задержки Когда значение обновляет периодическое задание без проверки расхождений При частом чтении и надёжном обновлении суммы Когда order_items меняются редко, но единого механизма обновления суммы нет

Вопрос 19 из 20

Какая завершающая часть делает повторный пересчёт дневного итога идемпотентным?

INSERT INTO daily_sales(day, total)
SELECT $1, SUM(total)
FROM orders
WHERE created_at::date = $1
-- выберите продолжение

ON CONFLICT (day) DO UPDATE SET total = daily_sales.total + EXCLUDED.total;

INSERT INTO daily_sales(day, total)
SELECT $1, SUM(total) FROM orders
WHERE created_at::date = $1;

ON CONFLICT (day) DO UPDATE SET total = EXCLUDED.total;

INSERT INTO daily_sales(day, total)
SELECT $1, SUM(total) FROM orders
WHERE created_at::date = $1
ON CONFLICT (day) DO UPDATE
SET total = EXCLUDED.total;

ON CONFLICT (day) DO UPDATE SET day = EXCLUDED.day;

UPDATE daily_sales
SET total = total + $2
WHERE day = $1;

ON CONFLICT (day) DO NOTHING;

DELETE FROM orders
WHERE created_at::date = $1;

Вопрос 20 из 20

Как принять решение об изменении схемы ради скорости?

Оценить длину SQL: более короткий запрос считать более быстрым и устойчивым Денормализовать схему целиком, чтобы исключить JOIN до проведения измерений Зафиксировать метрику и план, проверить гипотезу на представительном объёме, оценить цену записи и предусмотреть откат Выбрать самое глубокое изменение схемы, потому что его эффект будет заметнее

Ответьте на все 20 вопросов, чтобы получить результат

🔗 Встроить тест на свой сайт (iframe) ▼

Скопируйте код и вставьте в любое место на вашем сайте:

Также доступна прямая ссылка на embed-страницу