es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Optimizar consulta de actualización SQL

28 Jul, 2023 Programación

Tengo una actualización que tarda mucho en finalizar. Se necesitan actualizar 10 millones de filas. La ejecución finalizó después de 6 horas.

Esta es la consulta:

update A
set a_top = 'N'
where (a_toto, a_num, a_titi) in 
(select a_toto, a_num, a_titi 
 from A
 where a_titi <> 'test' and a_top is null limit 10000000);

Se han creado dos índices:

CREATE UNIQUE INDEX pk_A ON A USING btree (a_toto, a_num, a_titi)
CREATE INDEX id1_A ON A USING btree (a_num)

Estas son las cosas que ya he comprobado:

  • No hay bloqueos
  • No hay desencadenadores en A

El plan de ejecución me muestra que los índices no se están utilizando, ¿cambiaría algo si elimino los índices, actualizo las filas y luego creo los índices después de eso?

¿Hay alguna forma de mejorar la consulta en sí?

Aquí está el plan de ejecución:

Update on A  (cost=3561856.86..10792071.61 rows=80305304 width=200)
->  Hash Join  (cost=3561856.86..10792071.61 rows=80305304 width=200)
Hash Cond: (((A.a_toto)::text = (("ANY_subquery".a_toto)::text)) AND ((A.a_num)::text = (("ANY_subquery".a_num)::text)) AND ((A.a_titi)::text = (("ANY_subquery".a_titi)::text)))
->  Seq Scan on A  (cost=0.00..2509069.04 rows=80305304 width=126)
->  Hash  (cost=3490830.00..3490830.00 rows=2082792 width=108)
->  Unique  (cost=3390830.00..3490830.00 rows=2082792 width=108)
  ->  Sort  (cost=3390830.00..3415830.00 rows=10000000 width=108)
        Sort Key: (("ANY_subquery".a_toto)::text), (("ANY_subquery".a_num)::text), (("ANY_subquery".a_titi)::text)
        ->  Subquery Scan on "ANY_subquery"  (cost=0.00..484987.17 rows=10000000 width=108)
              ->  Limit  (cost=0.00..384987.17 rows=10000000 width=42)
                    ->  Seq Scan on A A_1  (cost=0.00..2709832.30 rows=70387600 width=42)
                          Filter: ((a_top IS NULL) AND ((a_titi)::text <> 'test'::text))
(12 rows)

Gracias por tu ayuda.

Tags:  , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Según el plan de ejecución, parece que la consulta está realizando un escaneo secuencial en la tabla A, lo cual no es ideal para una cantidad tan grande de datos. Además, la subconsulta está realizando una operación de límite que también podría afectar el rendimiento de la consulta.

    Aquí hay algunas posibles soluciones:

    1. Utilizar la palabra clave EXISTS en lugar de la cláusula IN:
    update A
set a_top = 'N'
where exists (select 1 from A as B where B.a_toto = A.a_toto and B.a_num = A.a_num and B.a_titi = A.a_titi
              and B.a_titi <> 'test' and B.a_top is null)

    Esto debería tener un mejor rendimiento que la consulta original, ya que evita la operación LIMIT y utilizará los índices existentes.

    1. Utilizar una tabla temporal para almacenar los valores que se van a actualizar y luego hacer unir con la tabla original:
    create temporary table temp_a as
select a_toto, a_num, a_titi 
from A
where a_titi <> 'test' and a_top is null limit 10000000;

update A
set a_top = 'N'
from temp_a
where A.a_toto = temp_a.a_toto and A.a_num = temp_a.a_num and A.a_titi = temp_a.a_titi;

drop table temp_a;

    Esto puede ser útil si la primera solución no proporciona un impulso significativo en el rendimiento. Evita la subconsulta y realiza la actualización utilizando una unión, lo cual debería ser más rápido.

    En cuanto a eliminar los índices, no se recomienda, ya que podría afectar el rendimiento de otras consultas que los utilicen. Además, el tiempo extra requerido para recrear los índices podría compensar cualquier posible ganancia al eliminarlos temporalmente.

    En cualquier caso, se recomienda probar la consulta con un subconjunto más pequeño de los datos para evaluar su rendimiento y evaluar el impacto de cualquier cambio.

Comments are closed.