Estadísticas y el optimizador — ANALYZE y selectividad

El dataset de este artículo — perf_sales (50 000 filas de ventas)

Este artículo trata sobre cómo la base de datos decide qué índice usar.

La pieza clave son las estadísticas.

Las estadísticas son hechos sobre la distribución de tus datos — "cuántas filas tiene la tabla", "cuántos valores distintos tiene una columna" — y las recoges con el comando ANALYZE.

A partir de esas estadísticas la base de datos calcula la selectividad (la estimación de qué fracción de filas sobrevive a una condición) y decide si usar un índice o leer toda la tabla.

Trabajarás con la tabla de ventas perf_sales (50 000 filas).

Tiene columnas con cardinalidades muy distintas: emp_id (30 comerciales distintos), region (4 valores: East / West / North / South), product (200 valores, P001 a P200) y status (en su mayoría paid, además de pending y refunded).

Verás cómo cambia el plan antes y después de ANALYZE.

Antes de los ejercicios, revisa las definiciones de columna, una muestra de los datos y cómo se distribuyen los valores por cada columna en perf_sales. La generación de datos lleva un momento, así que la primera ejecución puede pausarse unos segundos.

① Usa PRAGMA table_info(perf_sales); para ver los nombres de columna, los tipos y la clave primaria.

② Usa SELECT * FROM perf_sales LIMIT 5; para previsualizar las 5 primeras filas.

③ Cuenta las filas por cada valor de region para ver cómo se distribuyen los valores (cardinalidad).

ANALYZE y sqlite_stat1 — recoger estadísticas

Ejecuta ANALYZE y la base de datos recorre las tablas y los índices, recoge estadísticas y las guarda en una tabla interna llamada sqlite_stat1.

sqlite_stat1 registra hechos como "cuántas filas, de media, corresponden a un solo valor de este índice".

Ejecutar ANALYZE construye estadísticas basadas en tus datos reales, lo que ayuda al optimizador a elegir planes de ejecución precisos.

Usa ANALYZE por sí solo para cubrir todas las tablas, o ANALYZE table_name para enfocarte en una tabla específica.

-- Indexa dos columnas con distintas cardinalidades (status=3 valores / qty=20 valores)
DROP INDEX IF EXISTS ix_status;
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_status ON perf_sales(status);
CREATE INDEX ix_qty    ON perf_sales(qty);

-- Recoge estadísticas y mira lo guardado (filas medias por valor)
ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1
WHERE tbl = 'perf_sales' ORDER BY idx;
--> ix_status: una fila como 50000 16667 (unas 16667 filas por valor)
--> ix_qty:    una fila como 50000 2500 (unas 2500 filas por valor)

Vas a recoger de verdad las estadísticas que capturan "cuánto puede acotar cada índice" y leer el resultado. Haz la creación del índice, la recogida de estadísticas y la verificación en una sola ejecución. (Ejecútalo correctamente para revelar la explicación.)

① Elimina cualquier índice con el mismo nombre con DROP INDEX IF EXISTS, luego usa CREATE INDEX para construir un índice sobre emp_id y otro sobre region.

② Ejecuta ANALYZE; para recoger las estadísticas.

③ Desde sqlite_stat1, extrae las filas donde tbl sea perf_sales y mira la columna stat para cada índice (el total de filas más las filas medias por valor).

La selectividad cambia el plan — columnas que compensan frente a las que no

La selectividad es la estimación de "qué fracción del total de filas sobrevive después de un filtro".

Cuanto más pequeña sea esa fracción, mayor la selectividad y más compensa el índice.

emp_id = 7 acota a unas 1 667 filas (alrededor del 3% de la tabla), así que tiene alta selectividad y vale la pena usar el índice.

Por otro lado, status = 'paid' deja unas 40 000 filas (alrededor del 80% de la tabla), así que la selectividad es baja y leer cada fila en orden supera a saltar por el índice fila a fila.

Una vez que las estadísticas están en su sitio, el optimizador (el planificador de consultas) lee la selectividad y compara lo caro que sería un plan con índice frente a un barrido completo.

Con índices compuestos o múltiples candidatos en juego, se apoya en las estadísticas para preferir "el índice que acota a menos filas".

El siguiente ejercicio muestra que incluso con la misma tabla y los mismos índices, el plan puede cambiar según la selectividad de la columna del WHERE y si las columnas que seleccionas están en el índice.

-- Indexa una columna de alta selectividad (amount: casi único) y una de baja (status: 3 valores),
-- luego, tras ANALYZE, compara los planes para consultas de la misma forma
DROP INDEX IF EXISTS ix_amount;
DROP INDEX IF EXISTS ix_status;
CREATE INDEX ix_amount ON perf_sales(amount);
CREATE INDEX ix_status ON perf_sales(status);
ANALYZE;

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount = 500000;
--> SEARCH perf_sales USING INDEX ix_amount (amount=?)

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'paid';
--> la baja selectividad abre la posibilidad de un plan inclinado a SCAN

Compara "una condición que casi ninguna fila cumple" con "una condición que casi todas las filas cumplen", formuladas como consultas que seleccionan una columna que no está en el índice. Con COUNT(*), SQLite puede terminar el conteo solo con el índice, así que el índice se usa independientemente de la selectividad. Para ver SCAN ganar de verdad con un filtro de baja selectividad, este ejercicio usa la forma SELECT sale_id, amount — extrayendo `amount`, que no está en el índice.

① Elimina cualquier índice con el mismo nombre con DROP INDEX IF EXISTS, construye un índice sobre emp_id y otro sobre status, luego ejecuta ANALYZE; para recoger estadísticas.

② Añade EXPLAIN QUERY PLAN y revisa el plan para la consulta que selecciona sale_id y amount para filas con emp_id = 7 (alta selectividad: unas 1 667 filas / 3%).

③ Luego añade EXPLAIN QUERY PLAN y revisa el plan para la misma forma con status = 'paid', y lee cómo el lado de emp_id y el lado de status se reparten entre SEARCH y SCAN (baja selectividad: unas 40 000 filas / 80%).

En la consola de este curso, la baja selectividad puede aún mantener un INDEX SCAN

En la consola del navegador aquí (SQLite ejecutándose en memoria), el plan de status = 'paid' del paso ③ puede quedarse en SEARCH perf_sales USING INDEX ix_status (status=?). Aquí está la razón.

- Se ejecuta en memoria: en una base de datos en disco, el hueco entre "E/S aleatoria a través de un índice" y "un SCAN secuencial" es grande, y por eso la baja selectividad favorece a SCAN. En memoria, ambos son rápidos y el hueco se reduce.

- Las entradas del índice están ordenadas por valor: ix_status está ordenado por status, así que el bloque 'paid' se lee como un rango contiguo — no hay acceso aleatorio.

- El acceso a tabla basado en rowid es barato: SQLite puede obtener una fila de la tabla directamente vía su rowid interno, así que incluso las lecturas de tabla guiadas por índice se mantienen razonablemente baratas.

El comportamiento de un cambio nítido a `SCAN` cuando la selectividad cae es más fácil de observar en PostgreSQL u Oracle, RDBMS con almacenamiento real en disco y un optimizador completo basado en coste. Llévate de este ejercicio que hay un principio en juego: incluso con índices idénticos, la combinación de selectividad y columnas seleccionadas puede invertir la elección del optimizador. El umbral exacto entre SCAN y SEARCH se mueve con el motor, la presencia de disco y la precisión de las estadísticas.

Optimización basada en coste y variables de enlace — internos de otras bases de datos

La maquinaria que has visto — "estimar la selectividad a partir de estadísticas, luego elegir el plan más barato" — se llama optimizador basado en coste (CBO).

En bases de datos grandes como Oracle y SQL Server, esta maquinaria tiene más capas dentro.

Una es el parsing (análisis de la sentencia SQL): analizar una sentencia SQL por primera vez y construir un plan es un hard parse, mientras que reutilizar un plan analizado previamente es un soft parse.

Otra son las variables de enlace (variable de enlace: un mecanismo que no pone valores directamente en el SQL sino que deja un hueco como ? y pasa el valor en tiempo de ejecución), junto con el bind peeking — mirar el valor que se le da al optimizador y elegir un plan basado en cómo de sesgado está ese valor.

Estos son mecanismos internos de los motores de ejecución de las bases de datos grandes. Conocer los conceptos hace que las discusiones de tuning sean mucho más fáciles de seguir.

En la consola del navegador de este curso, puedes observar realmente cómo el plan reacciona a la selectividad (la sección anterior), pero el cambio entre hard y soft parses y el comportamiento del bind peeking no se pueden reproducir aquí como explica el callout de abajo, así que trabajarás los conceptos con un diagrama y un ejemplo de código de solo lectura.

Los hard / soft parses y el bind peeking son internos al estilo Oracle

El cambio entre hard y soft parses y el bind peeking (volver a elegir el plan basado en cómo de sesgado está el valor pasado) viven dentro de los motores de ejecución de bases de datos grandes como Oracle y SQL Server.

La consola del navegador de este curso no expone las vistas necesarias para observar estos estados internos (piensa en V$SQL de Oracle), así que no hay forma de demostrarlos en la consola.

Aquí obtendrás el concepto del diagrama y del ejemplo de código de solo lectura de abajo.

Por otro lado, la recogida de estadísticas con ANALYZE y cómo el plan reacciona a la selectividad, ambas cubiertas antes, son observables de verdad.

El corazón de la optimización basada en coste — "estadísticas -> selectividad -> elección del plan" — es algo que puedes confirmar de forma práctica en la consola de este curso, así que domínalo primero.

-- Una idea de cómo se ve esto en Oracle (solo lectura; no ejecutar en la consola de este curso)
-- Las variables de enlace pasan el valor después (:s se rellena en tiempo de ejecución)
-- SELECT * FROM perf_sales WHERE status = :s;
--
-- Cadenas SQL idénticas reutilizan el plan = soft parse
-- Cadenas incluso ligeramente distintas lo reconstruyen = hard parse
-- En Oracle inspeccionarías el parsing vía V$SQL.SQL_TEXT, pero aquí no

-- Lo que sí puedes observar de verdad en la consola de este curso:
-- una vez recogidas las estadísticas, la estimación de selectividad se basa en datos reales
ANALYZE;
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status = 'pending';
--> SEARCH perf_sales USING INDEX ... (status=?)

De la optimización basada en coste, la parte que la consola de este curso te permite observar realmente es "recoger estadísticas desplaza la estimación", y lo verificarás sobre la columna product (200 valores, alta selectividad). Haz la creación del índice y las revisiones de plan antes y después de recoger estadísticas en una sola ejecución.

① Elimina cualquier índice con el mismo nombre con DROP INDEX IF EXISTS, luego crea un índice sobre la columna product.

② Añade EXPLAIN QUERY PLAN y revisa el plan para una consulta que cuente filas donde product sea 'P050', sin estadísticas en su sitio.

③ Ejecuta ANALYZE; para recoger estadísticas, luego vuelve a ejecutar el EXPLAIN QUERY PLAN de la misma consulta. Como product tiene 200 valores y alta selectividad, el plan debería seguir usando el índice también después de ANALYZE.