Оптимизация аналитических запросов

1) Зачем оптимизировать (контекст iGaming)

Бизнес-скорость: отчеты GGR/NET, провайдеры/игры, RG/AML и маркетинг в p95 SLA.
Стоимость: меньше сканируемых байт и шафла → ниже $/запрос.
Надежность: стабильные пиковые часы, отсутствие «заморозок» BI.
Масштаб: десятки брендов/рынков, миллиарды строк, минуты свежести.

2) Профиль нагрузки и SLO

Опишите «первые 90%» запросов: окна (7/28/90d), фильтры (`brand,country,provider,psp,status`), join’ы, JSON-атрибуты, топ-K и перцентили.
SLO примеры: p95 ≤ 1.2 s для дашборда, scanned bytes ≤ 256 MB/запрос, freshness ≤ 5 min.

3) Анатомия планов: что искать

Predicate/Projection pushdown: фильтры и список столбцов опускаются до источника.
Partition pruning & data skipping: отсечение лишних партиций/файлов (min-max/bloom/manifest).
Vectorized scan / late materialization: чтение по колонкам, отложенные JOIN/PROJECT.
Join strategy: Broadcast Hash (BHJ), Sort-Merge (SMJ), Nested Loop (NLJ — избегать).
Spill & shuffle: объем перетасовки и пролив на диск — главный враг SLA.
Adaptive query execution: смена стратегии в рантайме (переключение BHJ↔SMJ, динамический коалес).

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

4) Партиции, сортировки, кластер-кейсы

Партиции: по `date` + 1–2 измерения доступа (например, `brand,country`).
Сортировка/кластеризация: `ORDER BY / CLUSTER BY / Z-order` по частым фильтрам/джоинам (`provider, game_id, occurred_at`).
Рекластеризация и компакция: регулярная перекладка для data skipping; целевой размер файлов 128–1024 МБ.

5) JOIN-паттерны

Broadcast Hash Join (BHJ): маленькое измерение (≤ сотни МБ) → broadcast к факту.

sql
/ hint if engine supports/
SELECT /+ BROADCAST(dim_provider) /...

Sort-Merge Join (SMJ): большие наборы, совместимые ключевые сортировки/кластер-кейсы → минимальный шафл.
Pre-join / денормализация: вынесите стабильные атрибуты из `dim_` в факт-снимок (projection/materialized view) — минус JOIN на критическом пути.
Anti/semijoins: перепишите `NOT IN/EXISTS` в явные semi-/anti-join планы.
Устранение кардинального взрыва: проверяйте дубликаты ключей в измерениях, используйте surrogate-keys.

6) GROUP BY, агрегаты и предагрегации

Rollup/Cube/Grouping Sets: одна фаза вместо нескольких агрегаций.

sql
SELECT brand, country, DATE(ts) d, SUM(amount)
FROM gold. payments
WHERE ts >= NOW() - INTERVAL '7 days'
GROUP BY GROUPING SETS ((brand,country,d),(brand,d),(d));

Материализованные представления (MV)/проекции: `payments_7d_by_brand_psp`, `rounds_1d_by_provider_game`.
Partial → Final aggregation: позвольте движку агрегировать частично на воркерах (local) и финально на координаторе.
Approximate: HLL для `COUNT(DISTINCT user)`, TDigest для перцентилей — кратно дешевле и достаточно для BI.

7) Оконные функции (аккуратно)

PARTITION BY ровно по ключам с высокой селективностью; ORDER BY — по колоночным сортировкам.
Заменяйте тяжелые окна на предагрегаты и semi-joins, где возможно.

sql
-- Instead of window distinct
SELECT brand, COUNT() users
FROM (SELECT DISTINCT brand, user_id FROM gold. sessions WHERE d>=CURRENT_DATE-7) t
GROUP BY brand;

8) Фильтры, пагинация и TOP-K

Порядок фильтров не важен для CBO, но важна селективность и индексы/сортировки.
LIMIT … WITH TIES / APPROX TOP-K — сокращают скан.
Пагинация: `keyset pagination` вместо `OFFSET/LIMIT` для больших таблиц.

sql
-- keyset
SELECT FROM t WHERE (date, id) > (:last_date,:last_id) ORDER BY date, id LIMIT 1000;

9) JSON/полуструктурированные

Материализуйте горячие пути в колонки (`device.os`, `psp.method`).
Используйте инвертированные индексы/GIN на JSON-путях, если движок поддерживает.
Избегайте UDF по строкам: лучше проекции с выделением атрибутов.

10) Approx и сэмплирование

HLL/Theta Sketch: дешевый `COUNT DISTINCT`.
TDigest/KLL: перцентили p95/p99 без full sort.
Reservoir/stratified sampling: интерактивные исследования и превью.

11) Память, пролив и конкарренси

Spill-guard: лимиты памяти на join/agg; при проливе — уменьшайте batch/parallelism, увеличивайте сортировку по ключу.
Concurrency & QoS: пулы для «горячих» дашбордов и тяжелых ад-hoc; лимиты скана/времени; kill-switch на «забытые» запросы.
Result cache / query cache: включите для повторяемых BI-шаблонов, инвалидируйте по токену свежести.

12) Тесты регрессий и «двойной прогон»

Храните эталонные профили (план/скан-байты/время) для топ-N запросов.
Перед релизом индексов/кластеров — A/B-прогон: сравните p95, scanned bytes, skipped share, shuffle.
Создайте «fail-fast» пороги: если p95 вырос > X% — откат.

13) Наблюдаемость и SLO

• p50/p95/p99 latency, scanned bytes/query, skipped bytes %, files touched;
• shuffle bytes, spilled bytes, peak memory;
• cache hit-rate; accuracy approx-агрегатов.

Алерты: рост scanned bytes, падение skipped share, частые NLJ, пролив > порога.

14) Кейсы iGaming (рецепты)

14.1 Платежи/PSP: «пики отказов»

WHERE: `ts BETWEEN now()-7d AND now()`, `brand,country,psp,status`.
Партиция: day; ORDER/Z-order: `(brand,country,ts)`; bitmap: `psp,status`; bloom: `transaction_id`.
MV: `payments_7d_by_brand_psp(status)`.
Итог: p95 → ~1s, scanned bytes ↓ в 5–10×, нулевой пролив.

14.2 Игровые раунды: топ-K игр/час

ORDER BY / cluster по `(provider, game_id, occurred_at)`; projection для предагрегатов.
Approx Top-K + TDigest для p95 длительности раунда.
Итог: суб-секундные графики на горячем кэше.

14.3 RG/AML: активные ограничения

JSON `reason` → колонка; bitmap `rg_state`, `kyc_level`; semi-join с последним состоянием.
Итог: отчет «за 30 дней» — секундами, без full scan.

15) Чек-лист оптимизации (ежедневный)

1. Сбор топ-N запросов и их профилей (план/байты/шафл).
2. Партиции по дате + согласованные сортировки/кластер-кейсы.
3. Проверка pushdown и projection pruning (только нужные колонки).
4. JOIN-стратегия: broadcast малых, сортировка для SMJ, нет NLJ.
5. Предагрегации/MV для горячих дашбордов.
6. Approx там, где допустимо (distinct/percentiles/top-k).
7. JSON → колонки и/или инвертированные индексы.
8. Компакция/рекластеризация; цель по skipped bytes ≥ 70%.
9. Кэш результатов и раздельные пулы конкарренси.
10. Мониторинг: p95, scanned bytes, shuffle, spill, hit-rate.

16) Шаблоны (готово к использованию)

16.1 Политика оптимизации (YAML)

yaml workload: bi_hot slo:
p95_latency_ms: 1200 scanned_bytes_max_mb: 256 skipped_bytes_share_min: 0. 70 storage:
partition_by: ["date"]
cluster_by: ["brand","country","occurred_at"]
indexes:
bloom: ["transaction_id","user_surrogate_id"]
bitmap: ["psp","status","rg_state"]
aggregation:
mv:
- name: mv_payments_7d_brand_psp window: "7d"
group_by: ["brand","psp","status"]
approx:
count_distinct: "hll"
percentile: "tdigest"
concurrency:
pools: {bi_hot: 50, adhoc: 10}
timeout_s: 120

16.2 Тест регрессии запроса (псевдо-SQL)

sql
-- baseline: p95<=1200ms, scanned_bytes<=256MB
EXPLAIN ANALYZE
SELECT brand, psp, status, COUNT() cnt, SUM(amount) amt
FROM gold. payments
WHERE ts >= NOW() - INTERVAL '7 days'
AND brand =:brand AND country =:country
GROUP BY brand, psp, status;

16.3 Переписывание DISTINCT

sql
-- Bad: Heavy COUNT (DISTINCT user_id)
SELECT COUNT(DISTINCT user_id) FROM gold. sessions WHERE d>=CURRENT_DATE-7;

-- Better: HLL sketch/preaggregate
SELECT hll_union(user_hll) FROM agg. sessions_7d_user_hll WHERE d>=CURRENT_DATE-7;

16.4 Keyset-пагинация

sql
SELECT
FROM gold. game_rounds
WHERE (occurred_at, round_id) > (:ts,:rid)
AND brand=:brand AND country=:country
ORDER BY occurred_at, round_id
LIMIT 1000;

17) Анти-паттерны

`SELECT ` в проде; отсутствие projection pruning.
OFFSET-пагинация на миллионах строк.
COUNT DISTINCT без скетчей; перцентили через полный sort.
NLJ на больших наборах; join по JSON-выражениям.
Мелкие партиции и разрозненные файлы (шторм метаданных).
UDF-строки в WHERE вместо материализации колонок.
Игнор статистик/ANALYZE — слепой оптимизатор и full scan.
Отсутствие регрессионных тестов и порогов отката.

18) Дорожная карта внедрения

0–30 дней (MVP)

1. Замер топ-N запросов и установка SLO/SLI.
2. Партиции по дате + сортировки/кластер-кейсы; включить data skipping/bloom.
3. Один MV на «горячий» отчет по платежам; HLL/TDigest в BI.
4. Разделение пулов запросов, включение result cache.

30–90 дней

1. Перепись тяжелых окон/JSON → предагрегации/колонки.
2. Broadcast-join малых измерений; SMJ для больших; устранение NLJ.
3. Компакция и рекластеризация по расписанию; автосоветчик ключей.
4. Наблюдаемость и алерты деградаций, A/B планов, авто-откат.

3–6 месяцев

1. Каталог проекций/MV с версионированием и SLA.
2. Approx-ядро для distinct/percentile/top-k по всем дашбордам.
3. Единые шаблоны регресс-тестов и бюджетов $/запрос.
4. Постоянная гигиена JSON и UDF: материализация и индексы.

19) RACI

Data Platform (R): партиции/кластеризация/компакция, MV/проекции, кэши, мониторинг.
Analytics/BI (R): переписывание SQL, approx-агрегаты, тесты регрессий.
Domain Owners (C): требования к разрезам и точности.
Security/DPO (A/R): приватность/PII, k-анонимность агрегатов.
SRE/Observability (C): SLO/алертинг, конкарренси и капасити.
Finance (C): бюджеты на $/запрос и экономический эффект.

20) Связанные разделы

Индексация аналитических хранилищ, Схемы данных и их эволюция, Валидация данных, DataOps-практики, Кластеризация данных, Снижение размерности, API аналитики и метрик, MLOps: эксплуатация моделей.

Итог

Оптимизация запросов — это не «магический хинт», а система: грамотная разметка данных (партиции/кластера), предагрегации и approximate-алгоритмы, правильные JOIN-стратегии, кэш/конкарренси и постоянный мониторинг p95 и scanned bytes. Для iGaming это означает быстрые и стабильные метрики платежей, игр и комплаенса — в рамках SLA и бюджета.