Recomendaciones de Rendimiento

La performance de las consultas puede ser afectada por muchos factores. Algunos pueden ser controlados por el usuario, otros son fundamentales al diseño del sistema.

En esta sección se provee algunas recomendaciones sobre el entendimiento y tuneado (optimización) de la performance de PostgreSQL.

Para consejos generales ver Preguntas Frecuentes

Para mayor información ver el  Manual

Usar EXPLAIN

Se puede usar el comando EXPLAIN para ver que plan está creando el planeador para la consulta. Por ejemplo:

EXPLAIN SELECT * FROM movimientos WHERE concepto_id=202;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on movimientos  (cost=4.82..68.92 rows=74 width=95)
   Recheck Cond: (conceptoid = 202)
   ->  Bitmap Index Scan on movimientos_conceptoid  (cost=0.00..4.81 rows=74 width=0)
         Index Cond: (conceptoid = 202)
(4 filas)

Los números citados por son (de izquierda a derecha, acumulando en los nodos superiores sus hijos):

  • Costo de inicio estimado (tiempo gastado antes de que inicia el barrido de salida)
  • Costo total estimado (si todas las filas fueran devueltas)
  • Número estimado de filas de salida
  • Ancho promedio estimad (en bytes) de las filas de salida

El costo es medido en unidades arbitrarias determinados por la configuración, tradicionalmente equivale a la cantidad de páginas leídas desde el disco (seq_page_cost = 1.0).

A su vez, se puede verificar la exactitud de los costos estimados del planeador usando EXPLAIN ANALIZE:

EXPLAIN ANALYZE SELECT * FROM movimientos WHERE concepto_id=202;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on movimientos  (cost=4.82..68.92 rows=74 width=95) (actual time=0.048..0.097 rows=74 loops=1)
   Recheck Cond: (conceptoid = 202)
   ->  Bitmap Index Scan on movimientos_conceptoid  (cost=0.00..4.81 rows=74 width=0) (actual time=0.038..0.038 rows=74 loops=1)
         Index Cond: (conceptoid = 202)
 Total runtime: 0.165 ms
(5 filas)

En este caso, además de los costos, EXPLAIN ANALYZE devuelve los tiempos de ejecución (actual time en milisegundos). Si bien las unidades de costo y tiempo son distintas, lo importante es que mantengan proporción entre sí. En los nodos donde se ejecuta varias veces, multiplicar los tiempos por la cantidad de ciclos (loops)

También se puede experimentar estrategias de planeamiento alternativas deshabilitando o desaconsejando ciertas tipos de planes (enable_bitmapscan, enable_hashagg, enable_hashjoin, enable_indexscan, enable_mergejoin, enable_nestloop, enable_seqscan, enable_sort, enable_tidscan):

SET enable_bitmapscan = off;
EXPLAIN ANALYZE SELECT * FROM movimientos WHERE concepto_id=202;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on movimientos  (cost=0.00..116.44 rows=74 width=95) (actual time=1.081..1.669 rows=74 loops=1)
   Filter: (conceptoid = 202)
 Total runtime: 1.729 ms
(3 filas)

En este ejemplo particular vemos que un barrido secuencial es mucho más costoso que usar un ínidice. Esto puede variar, y es determinado por las estadísticas recolectadas (ver siguiente pregunta)

Muchas veces, si utilizamos ordenamientos, el EXPLAIN ANALYZE se encargará de mostrar el tipo de ordenamiento y dispositivo en el cual fue llevado a cabo. Trate de evitar el external o disk merge aunmentando el work_mem, lo ideal (aunque no siempre es posible) es uqe la consulta sea llevada a cabo con el método de quicksort. Para saber cuanta cantidad de work_mem requiere para la sesión, verifique el número de kilobytes utilizados en ese nodo.

-- TODO(PONER EJEMPLO)

Estadísticas

El planeador necesita hacer un estimado de la selectividad de las clausulas WHERE. Para ello utiliza estadísticas almacenadas en el catalogo de sistema pg_statistic, cuyas entradas son actualizadas por ANALYZE y VACUUM ANALYZE, y siempre son estimadas, aún cuando se hayan recolectado recientemente.

La cantidad de información almacenada en pg_statistic por ANALYZE puede ser establecido columna por columna usando el comando ALTER TABLE SET STATISTICS, o globalmente ajustando default_statistics_target. Este valor puede ser establecido desde cliente con set default_statistic_target = <num>. El límite por defecto es de 100 entradas para la versión 8.3, en 8.4 el máximo es de 1000. Subiendo este límite permitiría hacer estimados mas precisos, particularmente para columnas con una distribución de datos irregular, al precio de consumir más espacio en pg_statistic y ligeramente un poco más de tiempo para computar los estimados. En el caso que ud. quiera mejorar la performance de la recolección de estadísticas, puede asignar mayor prioridad al proceso con el título stats collector. Inversamente, un limite menor debería ser suficiente para columnas con distribuciones de datos simples.

Por ejemplo, para una columna llamada conceptoid de una tabla movimientos podriamos consultar las estadísticas de la siguiente manera:

# select most_common_vals, most_common_freqs, histogram_bounds from pg_stats where tablename='movimientos' and attname='conceptoid';

Esto nos devolvería una tabla con tres columnas y sus respectivos datos:

  • most_common_vals (valores más comunes): {910002,910001,20001,0,50,101,30002,30001,201,8,202,60}
  • most_common_freqs (frecuencias de los valores más comunes): {0.384718,0.202658,0.0790698,0.055814,0.041196,0.0387597,0.0330011,0.021041,0.020598,0.0163898,0.0163898,0.0112957}
  • histogram_bounds (limites del histograma): {1,3,4,4,4,7,9,10,11,11,12,13,13,26,26,51,91,102,102,203,301,301,301,1000,1000,1000,20002,20002,60000}

En este caso, el dato 910002 tiene relativamente alta probabilidad (0.384718), por lo que es probable que el planeador decida hacer un barrido secuencial que usar un índice, ya que sería más óptimo recorrer toda la tabla.

El histograma divide el rango en porciones de igual frecuencia, lo que permite calcular la selectividad de un valor y luego las filas estimadas.

Populando la base de datos

En el caso de necesitar insertar una gran cantidad de datos o tener que restaurar una base de datos, las siguientes sugerencias pueden hacer el proceso más eficiente:

  • Deshabilitar autocommit (enviar un BEGIN al comienzo y un COMMIT al final)
  • Usar COPY, que es un comando optimizado para cargar gran cantidad de filas.
  • Crear los indices luego de cargar la tabla (eliminarlos y recrearlos luego si ya existen)
  • Crear las restricciones de clave foranea luego de cargar la tabla (eliminarlas y recrearlas luego si ya existen)
  • Incrementar la memoria de trabajo (maintance_work_mem), ayudando al CREATE INDEX y ALTER TABLE ADD FOREIGN KEY.
  • Incrementar los segmentos de control (checkpoint_segments), lo que reducirá su ocurrencia minimizando las escrituras al disco. Este valor por defecto tiene 3 segmentos de 16 MB cada uno.
  • Desactivar el modo de archivo de WAL (archive_mode) -si es que lo ha configurado-
  • Ejecutar ANALYZE siempre que haya alterado significativamente la distribución de los datos en una tabla, para mantener actualizadas las estadísticas