Índices, selectividad y planes de ejecución
AvanzadoUn índice es una estructura aparte, ordenada por una o más columnas, que funciona como el índice alfabético de un libro: lleva directo a las filas buscadas. Solo ayuda si el filtro es selectivo, es decir, si deja pasar una parte chica de la tabla. Si deja pasar casi todo, leer la tabla completa es más barato. El plan de ejecución (EXPLAIN) muestra qué decidió el motor.
Ejemplo
SELECT
status,
count(*) AS filas,
round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct_de_la_tabla
FROM transactions
GROUP BY status
ORDER BY filas DESC;Resultado
| status | filas | pct_de_la_tabla |
|---|---|---|
| completed | 30740 | 95.34 |
| failed | 829 | 2.57 |
| pending | 377 | 1.17 |
| reversed | 297 | 0.92 |
Cómo leerlo
Esta medición responde si un índice sobre transactions.status serviría. Para WHERE status = 'failed' sí podría: el filtro deja el 2.57 % de las filas. Para WHERE status = 'completed' no: deja el 95.34 %, y recorrer el índice para después buscar casi todas las filas cuesta más que leer la tabla de corrido. Acompañar un pedido de índice con esta medición es más convincente que pedirlo sin datos.
Error común
Pedir un índice por cada columna que aparece en un WHERE. Cada índice ocupa espacio y hace más lentas las escrituras, porque se actualiza con cada INSERT y UPDATE, y el motor igual lo ignora si el filtro deja pasar la mayoría de las filas. Primero se mide la selectividad y se revisa el plan; después se decide.
En otros motores: SQL Server, Oracle, MySQL, BigQuery, Snowflake
SQL ServerOracleMySQL
Usan índices B-tree (árbol balanceado) como PostgreSQL, cada uno con su visor de planes: plan estimado y plan real en SQL Server Management Studio,
EXPLAIN PLAN FORconDBMS_XPLAN.DISPLAYen Oracle,EXPLAINyEXPLAIN ANALYZEen MySQL.BigQuery
No tiene índices B-tree creados por el usuario. El costo depende sobre todo de los datos leídos, que se reducen con tablas particionadas (por fecha, en general), agrupadas (clustering) y seleccionando solo las columnas necesarias. El plan se ve en los detalles de ejecución de cada consulta.
Snowflake
Guarda las tablas en micro-particiones con el mínimo y el máximo de cada columna, y descarta las que no pueden cumplir el filtro (pruning). En tablas muy grandes se puede definir una clave de clustering. El plan se revisa en el Query Profile.
Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.
Ejemplo que no se ejecuta en el curso
CREATE INDEX transactions_status_idx
ON transactions (status);
EXPLAIN ANALYZE
SELECT id, amount, currency
FROM transactions
WHERE status = 'failed';No se puede ejecutar en el curso: CREATE INDEX y EXPLAIN están bloqueados en el sandbox. En PostgreSQL, el plan muestra el tipo de lectura (Seq Scan recorre toda la tabla; Index Scan y Bitmap Heap Scan usan un índice) y, con ANALYZE, las filas estimadas contra las reales y el tiempo de cada paso.