analizando consultas en Postgresql. Parte I

Tenemos una potente herramienta para analizar las consultas en PostgreSQL: explain analyze

explain analyze select * from usuario;

Seq Scan on usuario  (cost=0.00..4743.71 rows=44471 width=243) (actual time=0.026..59.431 rows=44474 loops=1)

 Total runtime: 63.645 ms

(2 filas)

Seq Scan, significa que la consulta se hará fila a fila secuencialmente sin acceso por índice.

El valor numérico cost=0,00, es el tiempo que tiene que esperar la consulta en ser ejecutada. En este caso el tiempo es 0.00 porque puede ejecutarse inmediatamente.

El segundo valor 4743,71, es el numero de fetches necesarios para recuperar todas las filas de la consulta, el número de fetches lo podemos minimizar por ejemplo aplicando LIMIT.

Rows=44.471 es el número de filas que devolverá la consulta y width el tamaño medio en bytes de cada fila.

Truco: si vas a realizar análisis de operaciones de escritura, encierralas en un bloque de transacción, para poder ejecutarlas las veces que necesites y que no se produzcan cambios en la base de datos:

begin;
explain analyze insert ...;
rollback;

Indices no utilizados en postgresql

 Consultar los índices de tu bbdd que no están siendo utilizados, para ahorrar espacio o replantearte sus campos:

SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan as index_scans
  FROM pg_stat_user_indexes ui
  JOIN pg_index i ON ui.indexrelid = i.indexrelid
  WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
  ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
  pg_relation_size(i.indexrelid) DESC;