Optimizacion De Consultas Postgres

En el uso de bases de datos una de las tareas mas importantes es generar consultas que sean eficientes y rápidas. Esto puede hacer que las consultas y por consiguiente las aplicaciones se aceleren de manera dramática, en un caso, conseguimos la atención de un cliente al acelerar una consulta sobre todo el universo de manzanas geográficas de México.

Lo primero que debemos hacer es medir, para saber cual es nuestra línea base y para esto vamos a utilizar la herramienta pgbench esta herramienta se instala desde aqui. Tenemos que inicializarla en la terminal como:

$ ./pgbench -i -p 5432 -d postgres

Luego tenemos que preparar nuestra corrida de benchmark, para esto necesitamos responder las siguientes preguntas:

Una vez que tenemos estos datos podemos comenzar nuestras pruebas:

en general vamos a correr el programa como

$ ./pgbench -T <secs> -c <clients> -t <transactions> -f <script>

Ver el manual.

Una vez que sabemos que está ocurriendo en nuestra base, podemos configurar los parámetros de postgres.conf como shared_buffers o effective_cache_size.

Una vez que para las consultas que tenemos planeadas sabemos cuanto se tardan con la base sin optimizar y luego optimizada podemos medir cuales son las consultas mas lentas para optimizarlas tambien. Para esto vamos a utilizar el comando EXPLAIN y el comando ANALIZE.

El comando Explain Analyze

El comando explain (en inglés, explica), y el comando analyze ( del inglés, analiza) son las herramientas básicas del programador de bases de datos que desean acelerar una consulta. explain explica el orden en que el intérprete va a ejecutar la consulta uy analize hace una prueba corriendo la consulta. Para optimizar las consultas en general lo que debemos hacer es seguir los siguientes pasos:

  1. Indexar las tablas o condiciones sobre las que filtramos
  2. Buscar las condiciones mas restrictivas sobre las que filtramos e indexarlas. Idealmente para cada tipo de llave podemos utilizar un tipo de índice diferente.

Tipos de índices

Idealmente para diferentes tipos de filtros podemos usar diferentes técnicas de indexación.