Optimización de consultas analíticas

1) Por qué optimizar (contexto de iGaming)

Velocidad de negocio: informes GGR/NET, proveedores/juegos, RG/AML y marketing en p95 SLA.
Costo: Menos bytes escaneados y el azafle → por debajo de $/solicitud.
Fiabilidad: horas punta estables, sin «heladas» BI.
Escala: decenas de marcas/mercados, miles de millones de líneas, minutos de frescura.

2) Perfil de carga y SLO

Describa el «primer 90%» de las consultas: ventanas (7/28/90d), filtros ('brand, country, provider, psp, status'), join's, atributos JSON, top K y percentili.
Ejemplos de SLO: p95 ≤ 1. 2 s para dashboard, bytes escaneados ≤ 256 MW/consulta, freshness ≤ 5 min.

3) Anatomía de los planes: qué buscar

Predicate/Projection pushdown: los filtros y la lista de columnas descienden al origen.
Partition pruning & data skipping: recorte de lotes/archivos adicionales (min-max/bloom/manifiesto).
Vectorized scan/late materialization: lectura por columnas aplazadas por JOIN/PROJECT.
Join strategy: Broadcast Hash (BHJ), Sort-Merge (SMJ), Nested Loop (NLJ — избегать).
Spill & shuffle: el volumen de barajado y el estrecho en disco es el principal enemigo del SLA.
Ejecución de consulta adaptativa: cambio de estrategia en el rantime (cambio de BHJ↔SMJ, coales dinámicos).

El plan debe mostrar: cuántos bytes leemos, dónde afloramos, qué cacheamos.

4) lotes, ordenaciones, casos de racimo

Lotes: por 'fecha' + 1-2 dimensiones de acceso (por ejemplo, 'marca, país').
Ordenar/agrupar: 'ORDER BY/CLUSTER BY/Z-order' por filtros/joins frecuentes ('provider, game_id, occurred_at').
Publicidad y Compactación: encrucijada regular para skipping de datos; tamaño de archivo objetivo 128-1024 MB.

5) Patrones JOIN

Broadcast Hash Join (BHJ): pequeña medición (≤ cientos de MB) → broadcast al hecho.

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

Sort-Merge Join (SMJ): conjuntos grandes, ordenaciones clave compatibles/casos de clúster → mínimo de azafle.
Pre-join/desnormalización: lleve los atributos estables de 'dim _' a la instantánea de hecho (projection/materialized view) - menos JOIN en el camino crítico.
Anti/semijoins: correspondencia 'NOT IN/EXISTS' a planes semi/anti-join explícitos.
Eliminación de la explosión cardinal: compruebe las claves duplicadas en las medidas, utilice surrogate-keys.

6) GROUP BY, unidades y preagrupaciones

Rollup/Cube/Grouping Sets: una fase en lugar de varias agregaciones.

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

Vistas/proyecciones materializadas (MV): 'payments _ 7d _ by _ brand _ psp', 'rounds _ 1d _ by _ provider _ game'.
Agregación final → parcial: permita que el motor se agregue parcialmente en los workers (local) y finalmente en el coordinador.
Approximate: HLL para 'COUNT (DISTINCT user)', TDigest para percentiles - múltiplo más barato y suficiente para BI.

7) Funciones de ventana (suavemente)

PARTITION BY exactamente por llaves con alta selectividad; ORDER BY - Por orden de columna.
Reemplace las ventanas pesadas por pre-unidades y semi-juntas siempre que sea posible.

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, paginación y TOP-K

El orden de los filtros no es importante para el CBO, pero la selectividad y los índices/ordenamientos son importantes.
LIMIT … WITH TIES/APPROX TOP-K - acortar el escaneo.
Paginación: 'keyset pagination' en lugar de 'OFFSET/LIMIT' para tablas grandes.

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

9) JSON/semiestructurado

Materializa las rutas calientes en columnas ('device. os`, `psp. method`).
Utilice índices invertidos/GIN en las rutas JSON si el motor es compatible.
Evite UDF por línea: mejor proyección con resaltar atributos.

10) Approx y samplay

HLL/Theta Sketch: barato 'COUNT DISTINCT'.
TDigest/KLL: percentili p95/p99 sin sort completo.
Reservoir/stratified sampling: investigación interactiva y previsualización.

11) Memoria, Estrecho y concarrensi

Spill-guard: límites de memoria en join/agg; en el estrecho: reduzca el batch/parallelismo, aumente la clasificación por clave.
Concurrency & QoS: pools para dashboards «calientes» y al-hoc pesados; límites de tiempo/escaneo; kill-switch en consultas «olvidadas».
Cap cache/query cache: incluye para plantillas BI repetibles, inválido por token de frescura.

12) Pruebas de regresión y «doble ejecución»

Almacena los perfiles de referencia (plan/escaneo-bytes/tiempo) para las consultas N superiores.
Antes de la liberación de índices/clústeres - A/B-running: comparar p95, bytes escaneados, skipped share, shuffle.
Cree umbrales «fail-fast»: si p95 creció> X% - retroceso.

13) Observabilidad y SLO

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

Alertas: crecimiento de bytes escaneados, caída de share skipped, NLJ frecuentes, estrecho> umbral.

14) Casos de iGaming (recetas)

14. 1 Pagos/PSP: «picos de fallos»

WHERE: `ts BETWEEN now()-7d AND now()`, `brand,country,psp,status`.
Fiesta: día; ORDER/Z-order: `(brand,country,ts)`; bitmap: `psp,status`; bloom: `transaction_id`.
MV: `payments_7d_by_brand_psp(status)`.
En pocas palabras: p95 → ~ 1s, bytes escaneados ↓ en 5-10 ×, estrecho cero.

14. 2 rondas de juego: los mejores juegos K/hora

ORDER BY / cluster по `(provider, game_id, occurred_at)`; projection para preagrupaciones.
Approx Top-K + TDigest para la duración p95 de la ronda.
En pocas palabras: gráficos de sub-segundo en la caché caliente.

14. 3 RG/AML: restricciones activas

JSON 'reason' → columna; bitmap `rg_state`, `kyc_level`; semi-join con el último estado.
En pocas palabras: informe «30 días» - segundos, sin análisis completo.

15) Lista de verificación de optimización (diaria)

1. Recopilar el N superior de las solicitudes y sus perfiles (plan/bytes/azafle).
2. Lotes por fecha + ordenaciones convenidas/casos de clúster.
3. Compruebe pushdown y projection pruning (sólo los altavoces deseados).
4. Estrategia JOIN: broadcast small, clasificación para SMJ, sin NLJ.
5. Preagregaciones/MV para dashboards calientes.
6. Approx donde es válido (distinct/percentiles/top-k).
7. JSON → altavoces y/o índices invertidos.
8. Compacto/publicidad; el objetivo de skipped bytes ≥ 70%.
9. Caché de resultados y grupos de concarrency separados.
10. Monitoreo: p95, scanned bytes, shuffle, spill, hit-rate.

16) Plantillas (listas para usar)

16. 1 Política de optimización (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 Prueba de regresión de solicitud (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 Reescritura de 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-paginación

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-patrones

'SELECT' en venta; sin projection pruning.
Paginación OFFSET en millones de líneas.
COUNT DISTINCT sin sketches; percentili a través de un sort completo.
NLJ en conjuntos grandes; join por expresiones JSON.
Lotes pequeños y archivos dispersos (tormenta de metadatos).
Cadenas UDF en WHERE en lugar de materializar columnas.
Ignore Statistics/ANALYZE es un optimizador ciego y un análisis completo.
No hay pruebas de regresión ni umbrales de retroceso.

18) Hoja de ruta para la implementación

0-30 días (MVP)

1. Mida las consultas N superiores y la instalación de SLO/SLI.
2. Lotes por fecha + ordenaciones/casos de clúster; habilitar datos skipping/bloom.
3. Un MV por informe de pago «caliente»; HLL/TDigest в BI.
4. División de agrupaciones de consultas, habilita la caché de resultados.

30-90 días

1. Censo de ventanas pesadas/JSON → preagregación/columna.
2. Broadcast-join de pequeñas dimensiones; SMJ para los grandes; eliminación de NLJ.
3. Compacto y publicidad programada; autocopiadora de llaves.
4. Observabilidad y alertas de degradación, planes A/B, auto-retroceso.

3-6 meses

1. Catálogo de proyecciones/MV con versionamiento y SLA.
2. Núcleo approx para distinct/percentile/top-k en todos los dashboards.
3. Plantillas uniformes de pruebas de regresión y presupuestos $/solicitud.
4. Higiene constante JSON y UDF: materialización e índices.

19) RACI

Plataforma de datos (R): lotes/clústeres/compacciones, MV/proyecciones, cachés, monitoreo.
Analytics/BI (R): reescritura de SQL, agregados approx, pruebas de regresión.
Domain Owners (C): requisitos de corte y precisión.
Seguridad/DPO (A/R): privacidad/PII, k-anonimato de las unidades.
SRE/Observabilidad (C): SLO/alerta, concarrensi y capacity.
Finanzas (C): presupuestos para $/solicitud y efecto económico.

20) Secciones relacionadas

Indexación de almacenamiento analítico, Esquemas de datos y su evolución, Validación de datos, Prácticas de DataOps, Clusterización de datos, Reducción de dimensión, API de análisis y métricas, MLOps: explotación de modelos.

Resultado

La optimización de consultas no es un «hint mágico», sino un sistema: marcaje competente de datos (lotes/clústeres), preagregaciones y algoritmos approximados, estrategias JOIN correctas, caché/concarrency y monitoreo constante de p95 y bytes escaneados. Para iGaming, esto significa métricas de pago, juegos y cumplimiento rápidas y estables, dentro del SLA y el presupuesto.