RE: Bajo rendimiento en postgresql cuando se lanza un delete

Hola,

Ante todo agradecer la colaboración que me han prestado en el foro.

Por fin, la consulta,
"delete from observation
where observation_id in (select observation_id from observation EXCEPT select observation_id from quality)",
terminó no sin antes demorarse 25 horas.

Tareas por hacer:
- Vacumm.
- Crear las reglas eliminadas.
- Crear la constraint en la tabla quality sobre el campo observation_id(clave ajena que referencia a la tabla observation).
- Crear un índice en la tabla quality sobre el campo observation_id.
- Seguir insertando datos (todo en este estricto orden porque los datos no están validados ya se sabe claves duplicadas, etc... quizás las reglas no las genere ya que puedo realizarlo al final).

- Pasar todo al servidor.
- Optimizar la base de datos en el servidor.

Saludos, Oliver

P.D. ya les contaré sobre esta odisea y que tal va postgre con semejante cantidad de datos.

-----Mensaje original-----
De: Fernando Hevia [mailto:fhevia(at)ip-tel(dot)com(dot)ar]
Enviado el: viernes, 31 de julio de 2009 22:53
Para: Francisco Manuel Quintana Trujillo
CC: pgsql-es-ayuda(at)postgresql(dot)org
Asunto: RE: [pgsql-es-ayuda] Bajo rendimiento en postgresql cuando se lanza un delete

> -----Mensaje original-----
> De: Francisco Manuel Quintana Trujillo
>
...

> Realicé los siguientes cambios:
>
> En la tabla "observation" eliminé temporalmente las reglas
> que estaban creadas
> -- Rule: "offering_delete_actualization ON observation"
> -- Rule: "offering_insert_actualization ON observation" No
> afecta al delete
> -- Rule: "offering_update_actualization ON observation" No
> afecta al delete
>
> Además, modifiqué la consulta
> delete from observation
> where observation_id in (select observation_id from
> observation EXCEPT select observation_id from quality);
>
> ni que decir tiene que la mejora ha sido bestial. Resultado
> del explain
>
> http://explain.depesz.com/s/llp
>
> El problema que estoy encontrando ahora es que el micro no
> está trabajando ni al 10% y en la carpeta pgsql_tmp se han
> creado unos 70 ficheros que no paran de crecer.
>

Los archivos que observas son archivos temporales que crea postgres para
poder completar el sort que conlleva la operación.
Serán eliminados al concluir la tarea.
Esto se debe a la naturaleza del nuevo query que requiere un sort de varios
millones de registros.
Son demasiados datos como para que entren en la memoria disponible para
sorts, por lo que se debe recurrir a swapeo en disco.

Puedes controlar la memoria que utiliza postgres en los sorts mediante el
parámetro work_mem.
Revisa en cuanto lo tienes seteado con:

show work_mem;

Por lo general work_mem debiera ser bajo, entre 1 y 16 MB y depende mucho
del tipo de consultas que tengas.
De todas maneras para esta consulta en particular modifica el valor antes de
ejecutar el delete:

set work_mem = 1GB;

- Ahora tira un explain sobre la consulta con el except.

- Ahora tira un explain sobre tu query original:
EXPLAIN DELETE FROM observation
WHERE observation_id NOT IN
(SELECT observation_id FROM quality)

Dependiendo de la memoria disponible, el segundo query puede llegar a ser
mucho más rápido.

Contanos como te fue.

Saludos,
Fernando.