Uso de pg_stat_statements para analizar el uso de índices y filtros

Los índices son como marcadores en un libro. Te ayudan a encontrar datos rápido. Pero, ¿qué pasa si ponemos un montón de marcadores y nadie los usa? O peor aún, ¿si los marcadores están tan mal puestos que te obligan a leer el libro de principio a fin? Por eso es importante analizar cómo se usan los índices.

Las consultas mal hechas pueden ignorar los índices, lo que lleva a costosos escaneos secuenciales (Seq Scan). Esto hace que las consultas sean más lentas y aumenta la carga del servidor. Nuestro objetivo es entender qué consultas no usan índices y por qué.

¿Cómo saber si se usan los índices?

Vamos a ver dos problemas clave:

1. ¿Se están usando los índices que creamos?
2. Si se usan, ¿son realmente efectivos?

Para esto podemos analizar las estadísticas de consultas en pg_stat_statements, fijándonos en varias columnas:

• rows: cantidad de filas procesadas por la consulta.
• shared_blks_hit: cantidad de páginas leídas desde memoria (no desde disco).
• shared_blks_read: cantidad de páginas realmente leídas desde disco.

Cuantas menos filas procese la consulta y cuanto mayor sea la proporción de shared_blks_hit respecto al total de páginas, mejor está funcionando nuestro índice.

Ejemplo de análisis de indexación

Supón que tenemos una tabla de estudiantes:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- Añadimos un índice por grade_level
CREATE INDEX idx_grade_level ON students(grade_level);

Ahora insertamos datos para hacer pruebas:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Estudiante ' || generate_series(1, 100000),
    'Apellido',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

Hacemos una consulta para buscar estudiantes con un nivel concreto:

SELECT *
FROM students
WHERE grade_level = 10;

Comprobando en pg_stat_statements

Después de ejecutar la consulta varias veces, podemos mirar las estadísticas:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Interpretando el resultado:

• rows: Si la consulta devuelve demasiadas filas, ¿tiene sentido el índice? Puede que no sea útil para condiciones poco selectivas.
• shared_blks_hit y shared_blks_read: Si se leen muchas páginas desde disco (shared_blks_read), puede que el índice no esté funcionando o que los datos no estén en el buffer pool.

Optimizando la indexación

Crear un índice es solo la mitad del trabajo. Es importante que PostgreSQL realmente lo use. A veces, aunque lo intentes todo, la base decide no usar el índice y hace un escaneo secuencial de toda la tabla. ¿Por qué pasa esto? Vamos a verlo.

Primero, veamos por qué un índice puede ser ignorado incluso cuando parece útil. Luego, algunos trucos para que la base “recuerde” que el índice existe y lo use como toca.

¿Y si el índice no se usa?

A veces PostgreSQL ignora el índice y hace un escaneo secuencial (Seq Scan). Puede ser por varias razones:

1. Baja selectividad de la condición. Si la consulta devuelve más de la mitad de las filas de la tabla, puede ser más rápido hacer un escaneo secuencial.
2. Tipo de datos o funciones. Si usas una función sobre la columna indexada, el índice puede ser ignorado. Por ejemplo:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- El índice no se usa

En estos casos puedes reescribir la consulta:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Usa el índice

3. Tipo de índice inadecuado. Por ejemplo, para búsquedas de texto completo es mejor usar índices GIN o GiST, no B-TREE.

4. Errores en las estadísticas. Si las estadísticas están desactualizadas, el optimizador puede tomar malas decisiones. Usa ANALYZE:

   ANALYZE students;
   

Mejorando la consulta

Volviendo a nuestro ejemplo. Si el índice no se usa, puedes probar lo siguiente:

1. Asegúrate de que la consulta usa filtros que puedan aprovechar el índice: no uses funciones, conversiones de tipo, etc.
2. Si el filtro devuelve muchos valores, piensa si el índice es necesario. Si es una consulta frecuente, prueba a cambiar la estructura de la tabla o añade vistas materializadas.
3. Si se usa Seq Scan por el gran volumen de datos, prueba a particionar la tabla (PARTITION BY).

Comprobando la eficiencia de la indexación

Después de optimizar, ejecuta de nuevo la consulta y revisa las estadísticas:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Compara las métricas antes y después. Deberías ver menos lecturas desde disco (shared_blks_read) y más aciertos en memoria (shared_blks_hit).

Casos reales

1. Mal uso del índice

Tenemos una tabla de productos con un campo de texto description:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Índice para búsqueda de texto completo
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Si hacemos la consulta:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

¡El índice no se usará! El motivo es que ILIKE no es compatible con GIN. Para usar el índice, la consulta debe reescribirse así:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Falta de índice donde hace falta

Supón la consulta:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

hace un escaneo secuencial (Seq Scan). Esto puede ser porque no hay índice en birth_date. Si creas el índice:

CREATE INDEX idx_birth_date ON students(birth_date);

y actualizas las estadísticas (ANALYZE students), podrás acelerar mucho esa consulta.