Otimizar consultas analíticas

1) Por que otimizar (contexto iGaming)

Velocidade de negócios: relatórios GGR/NET, provedores/jogos, RG/AML e marketing em p95 SLA.
Valor: menos bytes de escaneamento e safra → abaixo de $/consulta.
Confiabilidade: relógio de pico estável, falta de «congelamento» BI.
Escala: dezenas de marcas/mercados, bilhões de linhas, minutos de frescura.

2) Perfil de carga e SLO

Descreva «primeiros 90%» dos pedidos: janelas (7/28/90d), filtros ('brand, country, provider, psp, status'), join's, atributos JSON, top K e percentil.
SLO exemplos p95 ≤ 1. 2 s para dashbord, scanned bytes ≤ 256 MV/consulta, freshness ≤ 5 min.

3) Anatomia de planos: o que procurar

Predicate/Project pushdown: os filtros e a lista de colunas são baixados para a origem.
Partition pruning & data skipping: corte de partituras/arquivos extras (min-max/bloom/manifest).
Vectorized scan/late materialização: leitura por colunas postergadas pelo JOIN/PROJECT.
Join strategy: Broadcast Hash (BHJ), Sort-Merge (SMJ), Nested Loop (NLJ — избегать).
Spill & shuffle: Volume e estreito em disco é o maior inimigo do SLA.
Adaptative query exation: mudança de estratégia no RAT (mudança de BHJ↔SMJ, coligação dinâmica).

O plano tem de mostrar quantos bytes lemos, onde é que estamos a bater, o que estamos a esconder.

4) Partições, arrumações, maletas de cluster

Partições: por 'data' + 1-2 medidas de acesso (por exemplo, 'brand, country').
Triagem/clusterização: 'ORDER BY/CLUSTER BY/Z-order' por filtros/joins frequentes ('provider, game _ id, accurred _ at').
Reclasterização e compactação: translado regular para o data skipping; tamanho alvo dos arquivos 128-1024 MB.

5) JOIN-pattern

Broadcast Hash Join (BHJ): pequena dimensão (≤ de cem MB) → broadcast para o fato.

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

Sort-Merge Join (SMJ): conjuntos grandes, arrumações-chave compatíveis/maletas de cluster → safra mínima.
Pré-join/denormalização: leve os atributos estáveis de 'dim _' para um dado-foto (project/materialization view) - menos o JOIN no caminho crítico.
Anti/semijoins: troque 'NOT IN/EXISTs' para planos explícitos semi-/anti-join.
Resolver uma explosão radical: verifique as chaves duplicadas nas dimensões, use surrogate-keys.

6) GROUP BY, equipamentos e pré-agremiações

Rollup/Cube/Grouping Sets: uma fase em vez de várias unidades.

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));

Apresentações materializadas (MV )/projeções: 'payments _ 7d _ by _ brand _ psp', 'rounds _ 1d _ by _ provider _ game'.
Partial → Final aggregation: Deixe o motor adicionar em parte dos workers (local) e finamente no coordenador.
Approximate: HLL para 'COUNT (DISTINCT user)', TDigest para percentilos é duas vezes mais barato e suficiente para BI.

7) Funções de janela (com cuidado)

PARTITION BY exatamente pelas chaves de alta seletividade; ORDER BY - por ordem invertebrada.
Substitua as janelas pesadas por pré-regatas e semi-joins sempre que possível.

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) Filtros, paginação e TOP-K

A ordem dos filtros não é importante para o CBO, mas a seletividade e os índices/triagem são importantes.
LIMIT … WITH TIES/APPROX TOP-K - Encurtam a rocha.
Paginação: 'keyset pagination' em vez de 'OFFSET/LIMIT' para grandes tabelas.

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

9) JSON/meio estruturado

Materialize os caminhos quentes nas colunas ('device. os`, `psp. method`).
Use os índices invertidos/GIN nos caminhos JSON se o motor suporta.
Evite o UDF por linha: melhor projeção com atribuição de atributos.

10) Approx e samplicação

HLL/Theta Sketch: barato 'COUNT DISTINCt'.
TDigest/KLL: Percentaram p95/p99 sem full sort.
Reservoir/stratied sampling: pesquisa interativa e exaltação.

11) Memória, estreito e concarrense

Spill-guard: limites de memória para join/agg; no estreito - reduza o batch/paralelismo e aumente a triagem por chave.
Concurrency & QoS: balas para dashboards «quentes» e pesados ad-hoc; limites de scan/hora; kill-switch para pedidos «esquecidos».
Result cache/query cache: inclua para modelos BI repetíveis, inválida pelo token de frescura.

12) Testes de regressão e «duplo teste»

Guarde os perfis de referência (plano/scan bytes/hora) para as consultas top N.
Antes de lançar os índices/clusters - A/B-teste: compare p95, scanned bytes, skipped share, shuffle.
Crie um limiar «fail-fast»: se o p95 cresceu> X% - retrocesso.

13) Observabilidade e SLO

• p50/p95/p99 latency, scanned bytes/query, skipped bytes %, files touched;
• shuffle bytes, spilled bytes, peak memory;
• cache hit-rate; accuracy approx equipamentos.

Alerts: Crescimento scanned bytes, queda skipped share, frequentes NLJ, estreito> limiar.

14) Mala de iGaming (receitas)

14. 1 Pagamentos/PSP: «picos de rejeição»

WHERE: `ts BETWEEN now()-7d AND now()`, `brand,country,psp,status`.
Partição: day; ORDER/Z-order: `(brand,country,ts)`; bitmap: `psp,status`; bloom: `transaction_id`.
MV: `payments_7d_by_brand_psp(status)`.
Resultado: p95 → £1s, scanned bytes ↓ em 5-10 x, estreito zero.

14. 2 Rodadas de jogos: top K jogos/hora

ORDER BY / cluster по `(provider, game_id, occurred_at)`; projecção para pré-regatas.
Approx Top-K + TDigest para a duração da rodada.
O resultado são gráficos de segundo segundo segundo segundo em um cofre.

14. 3 RG/AML: restrições ativas

JSON 'reason' → coluna; bitmap `rg_state`, `kyc_level`; semi-join com o último estado.
Resultado: relatório de 30 dias - segundos, sem full scan.

15) Folha de cheque de otimização (diária)

1. Coleta os pedidos top N e seus perfis (plano/bytes/safra).
2. Partições por data + arranjos/maletas de cluster acordados.
3. Verificar pushdown e project pruning (somente as colunas desejadas).
4. Estratégia JOIN: broadcast pequenos, triagem para SMJ, sem NLJ.
5. Pré-regulação/MV para dashboards quentes.
6. Approx onde é permitido (distinct/percentiles/top-k).
7. JSON colunas e/ou índices invertidos.
8. Compactação/reclasterização; alvo de skipped bytes ≥ 70%.
9. A caixa de resultados e as balas de concarrensia separadas.
10. Monitoramento: p95, scanned bytes, shuffle, spill, hit-rate.

16) Modelos (pronto para uso)

16. 1 Política de otimização (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 Teste de regressão de consulta (pseudo-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 Reescrever o 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 paginação 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) Anti-pattern

'SELECT' em venda; falta do project pruning.
Paginação OFFSET em milhões de linhas.
COUNT DISTINCT sem esquetes; percentaram através de um porto completo.
NLJ em grandes conjuntos; join pelas expressões JSON.
Pequenas partições e arquivos esparsos (tempestade de metadados).
Linhas UDF no WHERE em vez de materializar colunas.
Estatístico ignorado/ANALYZE - Otimizador cego e full scan.
Não há testes de regressão ou liminares de retração.

18) Mapa de trânsito de implementação

0-30 dias (MVP)

1. Medir o top N de solicitações e instalar o SLO/SLI.
2. Partições por data + triagem/mala de cluster; incluir o data skipping/bloom.
3. Um MV para relatório de pagamento «quente»; HLL/TDigest в BI.
4. Separação de pool de consulta, ativação de result cache.

30 a 90 dias

1. Censo de janelas pesadas/JSON → pré/coluna.
2. Broadcast-join pequenas dimensões; SMJ para grandes; eliminar NLJ.
3. Compactação e reclasterização programada; O conselheiro automático das chaves.
4. Observabilidade e alertas de degradação, planos A/B, auto-recall.

3-6 meses

1. Catálogo de projeções/MV com versioning e SLA.
2. O núcleo Approx para distinct/percentile/top-k em todos os dashboards.
3. Modelos de testes de regresso e orçamentos $/consulta.
4. Higiene permanente JSON e UDF: materialização e índices.

19) RACI

Data Platford (R): partituras/clusterização/compactação, MV/projeções, cachês, monitoramento.
Analytics/BI (R): reescrever SQL, aparatos approx, testes de regressão.
Domain Owners (C): requisitos de corte e precisão.
Segurança/DPO (A/R): privacidade/PII, k-anonimato das unidades.
SRE/Observabilidade (C): SLO/alerting, concarrense e capasiti.
Finance (C): orçamentos para $/pedido e impacto econômico.

20) Seções relacionadas

Indexação de armazenamento analítico, Circuitos de Dados e sua evolução, Validação de Dados, Práticas de Exploração de Dados, Clusterização, Redução de Dimensões, API Analistas e Métricas, MLOs: Operação de Modelos.

Resultado

A otimização das consultas não é um «hint mágico», mas sim um sistema de sinalização de dados (partições/cluster), pré-agremiação e aproximate-algoritmos, estratégias JOIN corretas, dinheiro/concarrance e monitoramento contínuo p95 e scanned bytes. Para iGaming, isso significa métricas rápidas e estáveis de pagamentos, jogos e complacência - dentro da SLA e orçamento.