A veces una base de datos bien pensada nos viene a mostrar que en realidad no la pensamos bien.

Síntomas de estas diferencias entre lo planeado y la realidad, es el uso efectivo que se le dá a los índices que creamos en las tablas. Quiero decir, si creemos que al crear un índice estamos ayudando al optimizador a realizar la tarea, pero en realidad el optimizador elije otro camino para realizarla.

Esto puede suceder en índices de tablas pequeñas (donde un secuencial scan es más barato que buscar primero en el índice) o en queries donde a la tabla se entra por otro criterio, anulando la columna indizada.

Ahora bien, tener índices que no usamos puede ser malo: todo insert o delete será penalizado por el hecho de tener índices, y si además hacemos updates sobre el campo indizado…

Planteado el problema, el administrador prevenido tiene también una solución: medir el uso de los índices de las tablas.

El siguiente query presenta los índices (mayores a 1mb) que estén en nuestra base de datos, ordenándolos por número de uso creciente, por lo que podremos ver fácilmente si hay índices con valores en 0.

-- query para ver el uso de indices

select
  s.schemaname as sch,
  s.relname as rel,
  s.indexrelname as idx,
  s.idx_scan as scans,
  pg_size_pretty(pg_relation_size(s.relid)) as ts,
  pg_size_pretty(pg_relation_size(s.indexrelid)) as "is"
from
  pg_stat_user_indexes s
  join pg_index i on i.indexrelid=s.indexrelid
  left join pg_constraint c on i.indrelid=c.conrelid
  and array_to_string(i.indkey, ' ') = array_to_string(c.conkey, ' ')
where
  i.indisunique is false and
  pg_relation_size(s.relid) > 1000000 and
  s.idx_scan < 100000 and c.confrelid is null
order by
  s.idx_scan asc, pg_relation_size(s.relid) desc
--  limit 5