ZrobDev/ajuste-SQL

Ajuste de SQL (priorizando PostgreSQL)

Mide antes de cambiar nada. Un cambio de ajuste sin un plan y una medición de antes/después es una suposición.

Procedimiento

1. Encuentra el objetivo: ordena por costo total, no por la llamada individual más lenta (ver el bucle de triaje).
2. Captura la línea base: EXPLAIN (ANALYZE, BUFFERS) <consulta> en datos de tamaño similar a los de producción. Los datos pequeños de desarrollo eligen planes diferentes.
3. Localiza el nodo donde el tiempo o las filas se desvían (reglas de lectura a continuación). Arregla solo ese nodo.
4. Vuelve a ejecutar la misma captura del plan. Conserva el cambio solo si mejora tanto en tiempo como en buffers.
5. Registra el índice o la reescritura y el motivo, junto a la migración del esquema.

Envuelve EXPLAIN ANALYZE de INSERT/UPDATE/DELETE en BEGIN; ... ROLLBACK; porque ANALYZE realmente ejecuta la sentencia.

Lectura de un plan

• Léelo de abajo hacia arriba, nodos internos primero. El actual time por nodo es por bucle; multiplícalo por loops para obtener el costo real.
• Compara rows (estimación) con actual rows. Una diferencia de 10x o más es la causa raíz mucho más frecuente que un índice faltante: ejecuta ANALYZE <tabla>, aumenta el objetivo de estadísticas de la columna, o añade estadísticas extendidas (CREATE STATISTICS ... (dependencies, ndistinct)) para columnas correlacionadas.
• Rows Removed by Filter mucho mayor que las filas devueltas significa que el índice (o la falta de él) no es selectivo para este predicado.
• Buffers: shared read es disco o caché del SO; shared hit es shared_buffers. Muchas lecturas en ejecuciones repetidas significa que el conjunto de trabajo no cabe.
• Sort Method: external merge Disk: significa que work_mem era demasiado pequeño para esta ordenación. Auméntalo por sesión o por rol, no globalmente, porque se multiplica por nodo de ordenación y por conexión.
• Nested Loop con un recuento grande de loops internos y un lado interno sin índice es el clásico join lento. Hash Join con derrame (Batches: >1) es la misma historia de work_mem.
• Seq Scan es correcto cuando la consulta lee una gran fracción de la tabla. No añadas un índice para eliminar un Seq Scan que devuelve el 20% de las filas.
• Heap Fetches en un Index Only Scan significa que el mapa de visibilidad está desactualizado; ejecuta VACUUM en la tabla.

Elección de un índice

Indexa solo el predicado, join o ORDER BY que mediste. Cada índice cuesta tiempo de escritura, WAL y trabajo de vacuum.

• btree: predeterminado para igualdad, rangos, ordenación. Orden de columnas: columnas de igualdad primero, luego la columna de rango u ordenación. El skip scan en PG 18 ayuda en algunos casos donde falta la columna principal, pero no diseñes en torno a él.
• Parcial (WHERE estado = 'abierto'): cuando las consultas apuntan a un subconjunto pequeño y estable (elementos abiertos, filas no procesadas). El WHERE de la consulta debe implicar el predicado del índice.
• Cobertura (INCLUDE (col)): cuando un index-only scan eliminaría heap fetches para una consulta caliente. Las columnas incluidas no son claves de búsqueda.
• De expresión (ON (lower(email)), ON ((payload->>'id'))): cuando la consulta aplica una función a la columna. La consulta debe usar la expresión idéntica.
• BRIN: para tablas enormes de solo inserción donde la columna se correlaciona con el orden físico (marcas de tiempo, ids seriales). Índice diminuto, poda gruesa. Verifica pg_stats.correlation cercano a 1; si es bajo, BRIN no podará.
• GIN: contención en jsonb, arrays, texto completo. GiST: rangos, geometría, restricciones de exclusión.
• Crea en tablas en vivo con CREATE INDEX CONCURRENTLY; no puede ejecutarse dentro de un bloque de transacción y una ejecución fallida deja un índice INVÁLIDO que hay que eliminar.
• Elimina índices no utilizados solo después de verificar pg_stat_user_indexes.idx_scan durante un ciclo completo de negocio (fin de mes para cargas de trabajo financieras) y también en las réplicas.

Sargabilidad

Un predicado puede usar un índice solo si la columna indexada está sola en un lado de la comparación.

• WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' en lugar de WHERE extract(year from created_at) = 2026 o date_trunc(...) = ....
• WHERE col = $1 con tipos coincidentes; los casts de tipo columna_numerica = '5'::text en el lado de la columna bloquean el índice. Convierte el parámetro, no la columna.
• Evita el comodín inicial LIKE '%x'; usa pg_trgm GIN o búsqueda de texto completo.
• OR entre diferentes columnas a menudo pierde el índice; prueba con UNION ALL de dos ramas indexadas.
• NOT IN (subconsulta) es hostil con NULL y lento; usa NOT EXISTS.
• Prefiere EXISTS para semi-joins en lugar de DISTINCT después de un join, porque se detiene en la primera coincidencia y no duplica filas.

Funciones ventana vs auto-joins

Usa una función ventana cuando el objetivo es un valor por fila a partir de filas relacionadas en el mismo conjunto: totales acumulados, rango, lag/lead, "última fila por clave" (row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1), porque un escaneo y una ordenación reemplazan un join de la tabla consigo misma. Usa un auto-join o LATERAL solo cuando el segundo lado necesite su propio filtro o una sonda de índice (top-N por clave de una tabla grande: JOIN LATERAL (SELECT ... ORDER BY ts DESC LIMIT 3) con un índice en (k, ts DESC)). DISTINCT ON (k) ... ORDER BY k, ts DESC es la forma corta de PostgreSQL para el último por clave.

CTEs

Desde PG 12, un CTE no recursivo y sin efectos secundarios referenciado una vez se inserta en línea como una subconsulta. Escribe para legibilidad por defecto.

• Añade AS MATERIALIZED cuando el CTE es costoso y se referencia varias veces, o cuando debas evitar que el planificador inserte un predicado malo en él.
• Añade AS NOT MATERIALIZED para forzar la inserción en línea de un CTE referenciado varias veces cuando el filtro externo lo hace barato.
• Los CTEs que modifican datos siempre se ejecutan una vez; no se optimizan.

Bucle de triaje con pg_stat_statements

Requiere pg_stat_statements en shared_preload_libraries (reinicio) y CREATE EXTENSION.

1. Ordena por total_exec_time DESC (también verifica shared_blks_read, temp_blks_written, rows). Una consulta de 5 ms llamada 40 M de veces supera a una consulta nocturna de 30 s.
2. Toma las 3 primeras. Para cada una, obtén un conjunto de parámetros real (las estadísticas están normalizadas) y ejecuta el paso 2 del procedimiento.
3. Corrige, despliega, luego SELECT pg_stat_statements_reset(<userid>, <dbid>, <queryid>) solo para esa consulta, espera una ventana representativa, compara.
4. Repite hasta que la entrada superior sea un costo justificado. Detente ahí. Combínalo con auto_explain (log_min_duration, log_analyze con moderación) para capturar planes de consultas que no puedas reproducir.

PL/pgSQL solo donde vale la pena

Usa una función o procedimiento cuando elimine viajes de ida y vuelta para lógica fila por fila que no puede ser basada en conjuntos, aplique un invariante de trigger, o encapsule una transacción de múltiples sentencias. No lo uses para recorrer filas emitiendo un UPDATE cada una: reescribe como un UPDATE ... FROM o INSERT ... SELECT. Marca las funciones puras como IMMUTABLE/STABLE correctamente (una etiqueta incorrecta produce resultados en caché incorrectos o bloquea la inserción en línea). Prefiere funciones SQL simples cuando sea posible para que el planificador pueda insertarlas en línea.

N+1 y paginación

• N+1: una consulta por fila padre desde el código de la aplicación o el ORM. Corrige con un solo WHERE id = ANY($1) en lote, un join, o la opción de carga ansiosa del ORM. Detéctalo mediante pg_stat_statements: una sentencia normalizada con calls cercano al recuento de filas padre.
• Paginación con OFFSET lee y descarta todas las filas saltadas, por lo que la página N cuesta O(N). Usa keyset: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50 con un índice en (created_at DESC, id DESC). La columna de desempate es obligatoria o las filas se saltan o repiten. Keyset no puede saltar a la página N; usa OFFSET solo para conjuntos pequeños y acotados.
• count(*) en tablas grandes es un escaneo completo; muestra recuentos aproximados (reltuples) o limita con SELECT count(*) FROM (SELECT 1 ... LIMIT 10001).

Notas sobre ETL masivo

Carga por lotes con COPY (o INSERT ... SELECT) en lugar de inserciones fila por fila; elimina o pospone los índices secundarios solo para cargas iniciales grandes, luego ejecuta ANALYZE después. Los UPDATE grandes inflan las tablas: procesa por lotes por rango de clave, confirma por lote, y deja que autovacuum se mantenga al día.

Otros dialectos (breve)

• BigQuery: el costo son los bytes facturados, no las filas. Particiona por una columna de fecha/marca de tiempo y fíltrala con un literal o parámetro para que ocurra la poda; agrupa en clúster por las siguientes columnas más filtradas. SELECT * factura cada columna; nombra las columnas. Usa dry run para ver los bytes antes de ejecutar. LIMIT no reduce los bytes facturados.
• Snowflake: la poda usa metadatos min/max de micro particiones, así que filtra por columnas cuyos valores estén ordenados naturalmente en la carga; verifica "particiones escaneadas vs total" en el perfil de la consulta. Añade una clave de agrupamiento solo para tablas muy grandes con un patrón de filtro estable, porque el re-agrupamiento cuesta créditos. Escala el tamaño del almacén hacia arriba para derrames (derrame local/remoto en el perfil), escala horizontalmente (multi-clúster) para colas/concurrencia. Auto-suspensión corta.

Versiones actuales y enlaces a documentación: references/landscape-2026-11.md. Lecciones de uso real: references/gotchas.md.