Оптимізація аналітичних запитів

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 МВ/запит, 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 і бюджету.