Query optimization на собеседовании Data Engineer

Зачем optimization

Slow query — частая боль. Знание optimization techniques — must-have DE.

ANALYZE и статистика

Cost-based optimizer использует stats:

• Row count.
• Distinct values per column.
• Histograms.
• Most common values.
• Correlation column ↔ physical order.

После bulk INSERT / UPDATE — stats stale.

ANALYZE my_table;
ANALYZE VERBOSE my_table (col1, col2);

В Postgres autovacuum обычно делает периодически. После big change — вручную.

Rewrite techniques

EXISTS вместо JOIN с DISTINCT.

-- bad
SELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id = u.id;

-- good
SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Window functions vs self-join.

-- bad
SELECT a.id, a.value, b.value AS prev
FROM events a LEFT JOIN events b ON b.id = a.id - 1;

-- good
SELECT id, value, LAG(value) OVER (ORDER BY id) AS prev FROM events;

Predicate pushdown. Apply filters early.

-- bad: filter после CTE
WITH all_orders AS (SELECT * FROM orders) ...

-- good: filter в CTE
WITH recent AS (SELECT * FROM orders WHERE created_at > '2026-01-01') ...

Avoid functions on indexed column в WHERE.

-- bad: можно не использовать индекс
WHERE LOWER(email) = 'a@b'

-- good
CREATE INDEX idx_email_lower ON users(LOWER(email));

Index hints

В Postgres SET enable_seqscan = off — temporary. На проде — исправь stats / structure.

В MySQL / Oracle — phys hints USE INDEX, но frowned upon — лучше fix optimizer.

Materialized views

Pre-compute heavy queries.

CREATE MATERIALIZED VIEW daily_stats AS
SELECT day, country, COUNT(*) AS orders
FROM orders GROUP BY 1, 2;

REFRESH MATERIALIZED VIEW daily_stats;

Read из MV — fast. Refresh — depending на business need (hourly / daily).

ClickHouse — auto-incrementally maintained MVs.

Связанные темы

• EXPLAIN и план запроса для DE
• Индексы БД для DE
• Партиционирование таблиц для DE
• SCD типы для DE
• Подготовка к собесу Data Engineer

FAQ

Это официальная информация?

Нет. Статья основана на стандартных подходах SQL optimization.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.