Diagnostics slow queries на собеседовании Data Engineer

Систематический подход

1. Identify slow queries (top-N by total time).
2. Verify slow в isolation (EXPLAIN ANALYZE).
3. Identify root cause (plan, indexes, stats).
4. Fix.
5. Verify в proде.

Идентификация slow query

Postgres pg_stat_statements:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

MySQL slow query log.

ClickHouse system.query_log.

APM tools (DataDog, New Relic) — track per-app.

Начни с topof total time, не среднего.

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Look for:

• Sequential scans на big tables — missing indexes.
• High Rows Removed by Filter — filter после scan, нужен index.
• Lying estimates (rows estimate vs actual) — stats stale.
• Materialize / Spill — work_mem too small.
• Hash Batches > 1 — hash spills диск.

Common causes

Stale statistics. ANALYZE table_name.

Missing indexes. Add or rewrite.

Bad query. Subquery вместо JOIN, function в WHERE, etc.

Lock contention. Concurrent transactions wait. Check pg_stat_activity.

Resource exhaustion. OOM, full disk, swap.

Schema design. Wide table, normalized too much.

Plan flip. Sometimes optimizer chooses different plan after stats change.

Tools

explain.dalibo.com. Visual EXPLAIN analyzer.

pgBadger. Postgres log analysis.

pg_repack. Online table reorg.

py-spy / py-perf. Application profiling.

Wireshark / tcpdump. Network-level.

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

• EXPLAIN и план запроса для DE
• Query optimization для DE
• SQL anti-patterns для DE
• Postgres VACUUM bloat для DE
• Подготовка к собесу Data Engineer

FAQ

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

Нет. Статья основана на индустриальном опыте debugging.

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