Saltar al contenido

EXPLAIN ANALYZE en Postgres: lee el plan y elige el índice

En la práctica/ 10 min de lectura

Cómo leer EXPLAIN ANALYZE, distinguir un Seq Scan de un Index Scan, elegir las columnas del índice para WHERE y ORDER BY y medir su costo en escrituras.

  • Bases de datos
  • Rendimiento
En este artículo (9)
  1. Leer EXPLAIN ANALYZE paso a paso
  2. Seq Scan vs. Index Scan
  3. Elegir las columnas del índice: WHERE y ORDER BY
  4. Medir antes y después
  5. El costo de los índices en las escrituras
  6. Encontrar índices que no se usan
  7. Errores comunes
  8. Preguntas frecuentes
  9. Checklist

EXPLAIN ANALYZE ejecuta tu consulta y muestra el plan real: qué recorrió Postgres, cuántas filas descartó y cuánto tardó. Un Seq Scan que lee toda la tabla para devolver pocas filas suele indicar que falta un índice en las columnas del WHERE y del ORDER BY. Con el índice correcto, nuestra consulta de prueba bajó de 15.4 a 0.12 ms.

Abajo tienes cómo leer el plan línea por línea, los nodos de lectura que verás, cómo elegir las columnas del índice, cómo medir antes y después, lo que cuesta cada índice en las escrituras y cómo encontrar los que no se usan. Todo salió de una prueba en PGlite 0.5.10 (PostgreSQL 18.6 en WebAssembly) con 200,000 pedidos inventados; los tiempos son de ese entorno, no de tu servidor.

Leer EXPLAIN ANALYZE paso a paso

Poner EXPLAIN ANALYZE antes de una consulta la ejecuta de verdad y devuelve el plan con los números reales. Esta es la consulta de la prueba: los últimos 20 pedidos de un cliente, en una tabla orders sin más índice que la clave primaria. (Los 4 segundos de los que hablo en el Reel son un ejemplo ilustrativo; los tiempos de este artículo los medimos.)

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

Limit  (cost=4168.06..4168.11 rows=20 width=22) (actual time=15.375..15.402 rows=20.00 loops=1)
  Buffers: shared hit=1667
  ->  Sort  (cost=4168.06..4168.16 rows=40 width=22) (actual time=15.372..15.382 rows=20.00 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 19kB
        Buffers: shared hit=1667
        ->  Seq Scan on orders  (cost=0.00..4167.00 rows=40 width=22) (actual time=0.129..15.287 rows=40.00 loops=1)
              Filter: (customer_id = 4242)
              Rows Removed by Filter: 199960
              Buffers: shared hit=1667
Planning Time: 0.109 ms
Execution Time: 15.432 ms
  1. El plan se lee de adentro hacia afuera: Seq Scan produce filas, Sort las ordena y Limit se queda con 20.
  2. cost=0.00..4167.00 es una estimación en unidades arbitrarias, de arranque a total; el costo de un nodo incluye el de sus hijos.
  3. actual time=0.129..15.287 rows=40.00 loops=1 es lo medido: milisegundos hasta la primera y la última fila, filas y veces que se ejecutó el nodo. Con loops mayor que 1, multiplica el tiempo por ese número.
  4. Filter y Rows Removed by Filter: 199960: leyó 200,000 filas para quedarse con 40. Ese número delata la falta de un índice.
  5. Buffers: shared hit=1667 son las páginas leídas, todas ya en la caché de Postgres (hit). En PostgreSQL 18, ANALYZE activa BUFFERS solo; escribirlo no estorba.
  6. Planning Time es lo que tardó en elegir el plan y Execution Time lo que tardó en ejecutarlo.
el mismo plan, línea por línea, sin índice y con élantes · sin índiceLimitSort (Sort Key: created_at DESC)ordena 40 filas en memoriaSeq Scan on orderslee toda la tablaactual time=0.129..15.287 rows=40 loops=1ms reales · filas · vecesFilter: (customer_id = 4242)el WHERE se aplica aquíRows Removed by Filter: 199960filas leídas para nadaBuffers: shared hit=1667páginas leídasExecution Time: 15.432 mslo que de verdad tardódespués · índice (customer_id, created_at DESC)LimitIndex Scan using orders_customer_created_idxsin Sort: ya viene ordenadoIndex Cond: (customer_id = 4242) · Buffers: shared hit=24Execution Time: 0.123 ms24 páginas, no 1667lee el actual time y los Buffers, no solo el costmedido en PGlite 0.5.10 (PostgreSQL 18.6), 200,000 filas inventadas

Desliza la figura para verla completa

Dos advertencias de la documentación de EXPLAIN. Como EXPLAIN ANALYZE ejecuta la consulta, un INSERT, UPDATE o DELETE sí cambia datos: envuélvelo en BEGIN y ROLLBACK. Y los resultados en una tabla pequeña no se pueden extrapolar a una grande: la medición también tiene su costo.

Seq Scan vs. Index Scan

Un Seq Scan lee la tabla entera, fila por fila. Un Index Scan recorre el índice, que está ordenado, y va solo a las filas que le interesan. Un Bitmap Heap Scan es el intermedio: un Bitmap Index Scan marca dónde están las filas y luego Postgres las lee por páginas, en orden físico. Ninguno es siempre mejor: según la documentación, cuando la consulta necesita una fracción grande de la tabla, ordenar todo suele ser más rápido que ir y venir por el índice. Por eso el planificador decide con estadísticas, y por eso tener un índice no garantiza que se use.

un libro sin índice y con índice: cuántas páginas abressin índice · Seq Scanhojea todo el libro: 1667 páginas leídas · 15.4 mscon índice · Index Scancustomer_id = 4242 → ve directo a estas páginasabre el índice y salta a la fila: 24 páginas leídas · 0.12 msel índice ahorra páginas, no magiaesquema, no a escala · cifras medidas en PGlite con 200,000 filas

Desliza la figura para verla completa

Elegir las columnas del índice: WHERE y ORDER BY

La regla: primero las columnas con igualdad del WHERE, después las del ORDER BY. La documentación de índices multicolumna dice que las igualdades en las columnas iniciales delimitan la parte del índice que se recorre, y la de índices y ORDER BY que, con un ORDER BY y LIMIT n, un índice que coincide devuelve las primeras n filas directamente, sin recorrer el resto. Para nuestra consulta:

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
ANALYZE orders;

Limit  (cost=0.42..82.77 rows=20 width=22) (actual time=0.021..0.094 rows=20.00 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_created_idx on orders  (cost=0.42..165.11 rows=40 width=22) (actual time=0.018..0.065 rows=20.00 loops=1)
        Index Cond: (customer_id = 4242)
        Buffers: shared hit=24
Planning Time: 0.086 ms
Execution Time: 0.123 ms
  1. customer_id va primero porque la consulta lo compara con =: Postgres salta a las entradas de ese cliente.
  2. created_at DESC va después: dentro de cada cliente las entradas ya están en el orden que pide la consulta, así que el Sort desaparece y Limit se detiene a las 20.
  3. ANALYZE orders actualiza las estadísticas que usa el planificador.
  4. El plan ahora es un Index Scan con Index Cond, sin Filter ni Sort, y lee 24 páginas en vez de 1667.

En producción, crea el índice con CREATE INDEX CONCURRENTLY: según la documentación de CREATE INDEX, el normal bloquea las escrituras en la tabla mientras se construye. CONCURRENTLY no las bloquea, pero tarda más, no puede ir dentro de una transacción y, si falla, deja un índice INVALID que hay que borrar y volver a crear.

El orden importa. Con las columnas al revés, (created_at, customer_id), Postgres usó un Index Scan Backward: recorre desde las entradas más nuevas de todos los clientes y comprueba customer_id en cada una hasta juntar 20. Leyó 400 páginas y tardó 3.6 ms, unas 17 veces más páginas y 29 veces más tiempo que el índice correcto. La documentación lo anticipa: las condiciones en columnas a la derecha se revisan en el índice, pero no necesariamente reducen lo que se recorre. Y si pides ORDER BY a ASC, b DESC, un índice normal no sirve: defínelo con (a ASC, b DESC); pero para DESC en todas las columnas basta uno normal, porque Postgres lo recorre hacia atrás.

Dos límites: la documentación pide usar los índices multicolumna con moderación, y dice que con más de tres columnas rara vez ayudan. Y si solo consultas un subconjunto de filas, un índice parcial es más pequeño; es el caso de la tabla jobs de la cola de trabajos en Postgres con SKIP LOCKED, que indexa solo los pendientes.

Medir antes y después

Compara siempre la misma consulta en el mismo estado. Nuestra receta: ANALYZE tras cargar los datos y tras cada índice, tres ejecuciones seguidas por variante y nos quedamos con la tercera (caché caliente). Compara los Buffers además de los milisegundos: las páginas leídas cambian poco entre ejecuciones; el tiempo, bastante.

VarianteNodo de lecturaBuffersExecution Time
A · sin índiceSeq Scan + Sort166715.432 ms
B · (customer_id)Bitmap Heap Scan + Sort420.468 ms
D · (created_at, customer_id)Index Scan Backward4003.596 ms
C · (customer_id, created_at DESC)Index Scan, sin Sort240.123 ms

El índice solo en customer_id ya ayuda mucho: con unos 40 pedidos por cliente, ordenarlos es barato. Con un cliente de miles de pedidos, ese Sort dejaría de ser barato, aunque eso es razonamiento, no lo medimos.

los nodos de lectura que vas a ver en un plannodoqué leeen nuestra pruebaSeq Scantoda la tabla, fila a filaA · sin índice · 15.4 msBitmap Heap Scanlas filas que marca el índiceB · (customer_id) · 0.47 msIndex Scanfilas siguiendo el índiceC · 2 columnas · 0.12 msIndex Scan Backwardel índice, hacia atrásD · al revés · 3.6 msC = (customer_id, created_at DESC) · D = (created_at, customer_id)el planificador elige el nodo; tú le das las opcionesmisma consulta, 200,000 filas, PGlite 0.5.10 (PostgreSQL 18.6)

Desliza la figura para verla completa

El costo de los índices en las escrituras

Cada INSERT añade una entrada a cada índice de la tabla, y cada UPDATE de una columna indexada también. Lo medimos: insertar 100,000 filas en una tabla nueva, tres veces por variante, en PGlite.

  • Solo la clave primaria: 684, 739 y 703 ms (mediana 703).
  • Con un índice más: 937, 729 y 720 ms (mediana 729, dentro del ruido).
  • Con tres índices más: 1257, 1501 y 1210 ms (mediana 1257, cerca de 1.8 veces la base).

Es una prueba local y pequeña, pero la dirección es clara. Un índice que ninguna consulta usa solo cobra ese impuesto, y además ocupa disco: el de status de la prueba pesó 1376 kB.

Encontrar índices que no se usan

La vista pg_stat_user_indexes cuenta, en idx_scan, cuántos recorridos se iniciaron en cada índice. Esta consulta lista los que están en cero, sin contar los únicos (que protegen datos aunque no se lean):

SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

 table_name |    index_name     | idx_scan |  size
 orders     | orders_status_idx |        0 | 1376 kB
  1. idx_scan = 0 son los índices que ningún plan ha usado desde que empezaron a contarse las estadísticas.
  2. NOT i.indisunique deja fuera la clave primaria y las restricciones únicas.
  3. Ordenar por tamaño pone arriba los que más disco ocupan.

Cuidado antes de borrar. Las estadísticas se conservan si el servidor se apaga limpiamente, pero según la documentación se reinician tras una caída, un apagado inmediato, una copia base o una recuperación a un punto en el tiempo. Mira un periodo representativo, con los reportes de fin de mes incluidos, y borra con el CREATE INDEX a la mano para recrearlo.

Errores comunes

  • Mirar solo el cost. Es una estimación. Lee actual time, Rows Removed by Filter y Buffers.
  • Medir en una tabla de juguete. Con pocas filas, un Seq Scan gana y el índice no se nota. Prueba con un volumen parecido al real.
  • Indexar cada columna por si acaso. Cada índice cobra en las escrituras y ocupa disco. Crea uno cuando una consulta lo pida y compruébalo con el plan.
  • Poner las columnas en el orden equivocado. Igualdades primero, luego el ORDER BY. Nuestra variante D leyó 17 veces más páginas.
  • Olvidar ANALYZE. El planificador decide con estadísticas; con datos recién cargados o cambiados, actualízalas.
  • Crear el índice sin CONCURRENTLY en producción. Bloquea las escrituras de la tabla mientras se construye.
  • Ejecutar EXPLAIN ANALYZE sobre una escritura sin ROLLBACK. Los cambios ocurren de verdad.

Preguntas frecuentes

¿Es seguro usar EXPLAIN ANALYZE en producción?

Ejecuta la consulta, así que cuesta lo mismo que ella: con una lenta, espera esa lentitud. Si es un INSERT, UPDATE o DELETE, hace los cambios; envuélvelo en BEGIN y ROLLBACK. Para solo ver el plan, sin ejecutar, usa EXPLAIN a secas.

¿Por qué Postgres no usa mi índice?

Las causas más comunes: la consulta necesita una fracción grande de la tabla, las columnas del índice no coinciden con el WHERE y el ORDER BY, las estadísticas están viejas, o la tabla es tan pequeña que el Seq Scan sale más barato. Revisa el plan antes de forzar nada.

¿Cuántas columnas debe tener un índice?

Las que la consulta necesite, y pocas: la documentación dice que los multicolumna se usen con moderación y que con más de tres rara vez ayudan, salvo en usos muy estilizados de la tabla.

¿Cuántos índices son demasiados?

No hay un número. En nuestra prueba, tres índices extra llevaron el insert de 100,000 filas a cerca de 1.8 veces el tiempo base. Si tu tabla recibe muchas escrituras, mide, y revisa de vez en cuando pg_stat_user_indexes.

Checklist

  • Lees actual time, Rows Removed by Filter y Buffers, no solo el cost.
  • Las escrituras se prueban dentro de BEGIN y ROLLBACK.
  • El índice tiene primero las columnas con = del WHERE y después las del ORDER BY.
  • Haces ANALYZE y comparas el plan antes y después, con el mismo volumen de datos.
  • En producción creas el índice con CONCURRENTLY y buscas índices INVALID si falla.
  • Mides el costo del índice en tus escrituras si la tabla recibe muchas.
  • Revisas pg_stat_user_indexes en un periodo representativo antes de borrar nada.

Si la tabla es una cola, el índice parcial de la cola de trabajos en Postgres con SKIP LOCKED es un buen ejemplo de indexar solo lo que se consulta. Con los pasos de arriba, lee el plan, crea un índice, mide y decide con datos.

¿Viste esto en Instagram? Comenta INDICES en el Reel y te mando la guía.

Escrito por

Luis Morales

Luis Morales

Desarrollador frontend y Shopify, EcomLabs

@luismoralesdev en Instagram

Publicado el 12 de noviembre de 2026 · actualizado el 18 de noviembre de 2026

← Blog