Indices en Postgresql

Hay una serie de índices aparte de los convencionales, que son poco conocidos, pero no por ello dejan de ser muy útiles.

GIN - índices optimizados para la búsqueda de subelementos, dentro de una columna, por ejemplo columnas de tipo Array, columnas que almacenen JSON o campos de texto para hacer búsquedas FTS (Full Text Search).

GIST - índices para búsquedas de datos geolocalizados.

BRIN - índices que ahorra espacio de almacenamiento para aquellos datos que se encuentran ordenados de forma natural. Por ejemplo las entradas de un fichero log, siempre se generan de forma secuencial en base al campo de fecha de la entrada.

HASH - indice optimizado en velocidad para la búsqueda de elementos por igualdad. Por ejemplo la dirección de email de usuarios. Usando este índice tendríamos máxima velocidad de acceso para buscar a un usuario por su email.

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;

Usuario de solo lectura

A veces puedes necesitar dar acceso a tu base de datos a una herramienta de terceros, de la cual no te fias o solo quieres que haga consultas pero no toque nada. Una forma rápida es crear un usuario de solo lectura:

create user 'readonly' password 'laquesea';
alter user readonly set default_transaction_read_only = on;

Luego puedes darle permiso sobre todas las tablas de los esquemas que necesites:






select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema1');
select 'grant select on ' || a.schemaname || '.' || a.tablename || ' to readonly;' from pg_tables a where schemaname in ('esquema2');

Y por último dale permisos de acceso a los esquemas:

grant usage on schema cromos to readonly;
grant usage on schema coleccion to readonly;

Bloque anónimo

A veces necesitas hacer uso de las estructuras de plpgsql, por ejemplo, en scripts .sql que llamas desde tareas del cron, etc. Para ello tenemos los bloques DO.

A continuación un bloque sencillo, que comprueba si es primeros de mes y añade un mes de antigüedad a los usuarios registrados. Como puedes ver podemos utilizar variables y ejecutar comandos de los cuales no necesito conocer el resultado (perform).

do $$
declare
  dia integer;
begin
  select extract ('day' from current_date) into dia from editorial;
  if dia = 1 then
    perform 'update usuario set months=months+1';
  else
    raise notice 'Hoy no es día 1';
  end if;
end$$;

Cambiar atributos default o not null a una columna existente

Si quieres quitar la propiedad default o not null a una columna existente;

alter table  alter column  drop not null;

alter table  alter column  drop default;


Por el contrario, si quieres añadir el atributo not null o la propiedad default:

alter table  alter column  set not null;

alter table  alter column  set default current_timestamp;

Ultimo id insertado

Cuando queremos recuperar el último id insertado en una tabla, para por ejemplo asignarlo en los registros de sus hijas. Lo habitual es consultar el último valor de la secuencia asociada al campo clave. Por ejemplo:
 
select currval('cambio_id_seq');
 
En este caso tenemos que conocer el nombre de la secuencia, si lo desconociésemos podríamos utilizar la función pg_get_serial_sequence que nos proporcionaría el nombre de la secuencia asociado a un campo determinado de una tabla:

select currval(pg_get_serial_squence('cambio','id'));

Esta opción tiene un problema. Solo nos funcionará si nadie más utiliza dicha secuencia en otra sesión. Es decir en entorno de multiples conexiones no es recomendable.

Por el contrario LASTVAL(), está aislado a nivel de sesión, por lo que no tiene dichos efectos indeseados

Pero si la tabla tuviese un trigger que insertará nuevos registros o modificara la secuencia por algún motivo, LASTVAL() no funcionaría correctamente. 

Por todo lo anterior, el mejor método posible es utilizar la clausula RETURNING después de la inserción. Por ejemplo:

    insert into cambio (id,usuario1,usuario2)
    values (DEFAULT,usuario1_id,usuario2_id)
    RETURNING id into cambio_id;

En la variable cambio_id tendremos almacenado seguro el valor de la inserción que estamos haciendo en nuestra sesión, sin efecto colateral alguno.

Script migración tablas sueltas

Script de apoyo para mover el contenido una serie de tablas de una base de datos a otra.
Toma el contenido de las tablas tabla1..tabla4 y lo vuelca en /tmp/fichero.dump sin tomar la estructura de la tabla.
Utiliza unas tablas auxiliares para guardar el contenido de las tablas que tienen relaciones con tabla1..tabla4.
Vacía las tablas tabla2_related1..tabla2_related3 con relaciones sobre las tablas que estamos moviendo.
Posteriormente vacía las tablas destino tabla1..tabla4 y despues las rellena con el contenido del fichero /tmp/fichero.dump
Por último restaura el contenido de las tablas con relaciones, utilizando la cópia almacenada en las tablas auxiliares.
Espero que os pueda servir de ayuda en alguna ocasión.


#Volcado de las tablas originales deseadas (primero las maestras luego las dependientes)
pg_dump -U -h -p --inserts --data-only -t tabla1  >  /tmp/fichero.dump
pg_dump -U -h -p --inserts --data-only -t tabla2 >> /tmp/fichero.dump
pg_dump -U -h -p --inserts --data-only -t tabla3 >> /tmp/fichero.dump
pg_dump -U -h -p --inserts --data-only -t tabla4 >> /tmp/fichero.dump
#Borrado de las auxiliares
psql -h -U -p -c "drop table schema.aux_tabla2_related1"
psql -h -U -p -c "drop table schema.aux_tabla2_related2"
psql -h -U -p -c "drop table schema.aux_tabla2_related3"
#Relleno de las auxiliares
psql -h -U -p -c "create table schema.aux_tabla2_related1 as select * from schema.tabla2_related1"
psql -h -U -p -c "create table schema.aux_tabla2_related2 as select * from schema.tabla2_related2"
psql -h -U -p -c "create table schema.aux_tabla2_related3 as select * from schema.tabla2_related3"
#Borrado de las relacionadas
psql -h -U -p -c "delete from schema.tabla2_related1"
psql -h -U -p -c "delete from schema.tabla2_related2"
psql -h -U -p -c "delete from schema.tabla2_related3"
#Borramos el contenido de las tablas deseadas
psql -h -U -p -c "delete from schema.dictionary"
psql -h -U -p -c "delete from schema.tabla4"
psql -h -U -p -c "delete from schema.tabla2"
psql -h -U -p -c "delete from schema.tabla1"
#Rellenamos las tablas deseadas
psql -h -U -p <  /tmp/fichero.dump
#Restauramos las relacionadas
psql -h -U -p -c "insert into schema.tabla2_related1 select * from schema.aux_tabla2_related1"
psql -h -U -p -c "insert into schema.tabla2_related2 select * from schema.aux_tabla2_related2"
psql -h -U -p -c "insert into schema.tabla2_related3 select * from schema.aux_tabla2_related3"

Busqueda de Esquemas en Postgres por defecto

Si teneis una base de datos con varios esquemas, llega un momento en que es complicado encontrar una tabla y es farragoso tener que escribir el nombre del esquema delante de cada tabla para acceder al contenido.

Una solución es modificar el search_path del usuario con el que te conectas a la base de datos, por ejemplo del usuario user_connect, conectado como superusuario:

alter user "user_connect" set search_path to "$user",public,master,soccer;

Pero es mucho mejor indicar el camino de acceso a nivel de base de datos, en lugar de hacerlo a nivel de usuario.

alter database "league" set search_path to "$user",public,master,soccer;

De esta forma todos los usuarios que se conecten a esta base de datos, podrán encontrar las tablas de forma cómoda, en los esquemas public, master, soccer y en un esquema con su propio nombre de usuario con el que se ha conectado.

Bloque de comandos sin crear una función

Si necesitas hacer pruebas con un bloque de comandos de pgsql, sin tener que crear una función, esta es la forma:

do
$$declare
intervalo varchar;
fecha     timestamp;
begin
--fecha := ;
intervalo:= 1 || ' days';
fecha := current_timestamp + intervalo::interval;

insert into acumulado(usuario_id,f_fin,texto) values ( 1, fecha, 'texto'  );

end$$;

Consulta de Bloqueos en PostgreSql > 9.2 y como matar procesos

Para las versiones de Postgres 9.2 o superiores podéis crear una vista que facilitará la consulta de los procesos que actualmente tiene bloqueos en vuestro sistema:

CREATE OR REPLACE VIEW public.procesos_bloqueantes(
    blocking_pid,
    blocking_user,
    blocking_query,
    blocked_pid,
    blocked_user,
    blocked_query,
    age)
AS
  SELECT kl.pid AS blocking_pid,
         ka.usename AS blocking_user,
         ka.query AS blocking_query,
         bl.pid AS blocked_pid,
         a.usename AS blocked_user,
         a.query AS blocked_query,
         to_char(age(now(), a.query_start), 'HH24h:MIm:SSs' ::text) AS age
  FROM pg_locks bl
       JOIN pg_stat_activity a ON bl.pid = a.pid
       JOIN pg_locks kl ON bl.locktype = kl.locktype AND NOT bl.database IS
         DISTINCT
  FROM kl.database AND NOT bl.relation IS DISTINCT
  FROM kl.relation AND NOT bl.page IS DISTINCT
  FROM kl.page AND NOT bl.tuple IS DISTINCT
  FROM kl.tuple AND NOT bl.virtualxid IS DISTINCT
  FROM kl.virtualxid AND NOT bl.transactionid IS DISTINCT
  FROM kl.transactionid AND NOT bl.classid IS DISTINCT
  FROM kl.classid AND NOT bl.objid IS DISTINCT
  FROM kl.objid AND NOT bl.objsubid IS DISTINCT
  FROM kl.objsubid AND bl.pid <> kl.pid
       JOIN pg_stat_activity ka ON kl.pid = ka.pid
  WHERE kl.granted AND
        NOT bl.granted
  ORDER BY a.query_start;

Una vez localizados los procesos que están produciendo el bloqueo y que sentencia están ejecutando, podemos tomar la decisión de eliminarlos si llevan demasiado tiempo en ejecución y no nos importa perder él resultado de la acción que estaban ejecutando. El identificador de proceso a matar es el "blocking_id" de la vista:

select pg_cancel_backend ( blocking_pid );

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;

Consulta de Bloqueos en PostgresSql

Si tienes problemas de registros bloqueados en la base de datos ejecuta la siguiente sentencia para visualizar que sentencia/s lo están provocando:

SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,

kl.pid AS blocking_pid, ka.usename AS blocking_user, a.query AS blocked_statement

FROM pg_catalog.pg_locks bl

JOIN pg_catalog.pg_stat_activity a

ON bl.pid = a.pid

JOIN pg_catalog.pg_locks kl

JOIN pg_catalog.pg_stat_activity ka

ON kl.pid = ka.pid

ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid

WHERE NOT bl.granted;