0
177
Vistas¿Cómo puedo acelerar esta ACTUALIZACIÓN de PostgreSQL DESDE la consulta sql? Actualmente tarda días en terminar de ejecutarse
¿Cómo puedo acelerar la consulta UPDATE FROM sql de PostgreSQL a continuación? Actualmente tarda días en terminar de ejecutarse.
UPDATE import_parts ip SET part_part_id = pp.id FROM parts.part_parts pp WHERE pp.upc = ip.upc AND (ip.status is null or ip.status != '6');¿Y por qué tarda días en funcionar en primer lugar?
La mayoría de las veces, elimino manualmente la consulta porque tarda demasiado en ejecutarse, como más de 24 horas. La última vez que terminó de ejecutarse con éxito, tardó casi 38 horas.
La tabla import_parts tiene 971971 filas
La tabla parts.part_parts tiene 2196357 filas
La tabla parts.part_parts tiene un índice en upc e id es la clave principal de la tabla.
Ya intenté ejecutar VACUUM ANALYZE en la tabla import_parts y en la tabla parts.part_parts antes de que se ejecutara la consulta de actualización anterior, pero la consulta aún tarda demasiado en ejecutarse, por lo que la eliminé manualmente después de 30 minutos. Espero poder ejecutar la consulta en menos de 30 minutos.
Este es el resultado de EXPLAIN cuando ejecuto la consulta después de ejecutar VACUUM ANALYZE en las tablas import_parts y parts.part_parts :
ACTUALIZACIÓN 1:
También intenté desactivar enable_nestloop : SET enable_nestloop TO off
Pero la consulta aún tarda demasiado en ejecutarse, así que la eliminé manualmente. Este es el resultado de EXPLAIN cuando enable_nestloop está desactivado:
ACTUALIZACIÓN 2:
Aquí está el resultado de EXPLAIN al usar la consulta sugerida por Abelisto en su respuesta a esta publicación:
Sin embargo, cuando ejecuto la consulta, me encuentro con este error:
ERROR: more than one row returned by a subquery used as an expression
Todavía estoy averiguando cómo corregir el error.
3 Respuestas
Responde la pregunta0
En primer lugar, intente reescribir su consulta como
UPDATE import_parts ip SET part_part_id = ( SELECT pp.id FROM parts.part_parts pp WHERE pp.upc = ip.upc) WHERE status is null or status != '6';Obviamente plantea algo como
ERROR: más de una fila devuelta por una subconsulta utilizada como expresión
Solucionarlo usando condiciones adicionales (la subconsulta debe devolver exactamente una fila o cero para cada fila en la tabla de destino)
0
Por lo que dices, parece que upc no es único en parts_parts . Intenta ejecutar esto:
select upc, count(*) from parts.parts_parts pp group by upc having count(*) > 1;Estos duplicados probablemente estén causando los problemas de rendimiento. Puede evitar esto eligiendo arbitrariamente un valor, como:
UPDATE import_parts ip SET part_part_id = pp.id FROM (SELECT pp.upc, MIN(pp.id) as id FROM parts.part_parts pp GROUP BY pp.upc ) pp WHERE pp.upc = ip.upc AND (ip.status is null or ip.status <> '6');0
Cree un índice en import_parts con columnas: upc,status.
Te recomendaré que lo dividas en dos oraciones:
No sé tu estado, pero supongo que tienes estado: nulo, 1, 2, 3, 4, 5, 6, 7
UPDATE import_parts ip SET part_part_id = pp.id FROM parts.part_parts pp WHERE pp.upc = ip.upc AND ip.status is null ; UPDATE import_parts ip SET part_part_id = pp.id FROM parts.part_parts pp WHERE pp.upc = ip.upc AND ip.status IN(1, 2, 3, 4, 5, 7) ;Por supuesto, debe cambiar 1, 2, 3, 4, 5, 7 por sus valores (diferentes de 6)
También me gusta la respuesta de @Gordon Linoff, pero depende de cuantas filas tengas por upc