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.

Optimización de consulta SQL para eliminar elementos duplicados de una tabla grande.

22 Jul, 2023 Programación

¿Alguien podría ayudarme a optimizar una de las consultas que está tardando más de 20 minutos en ejecutarse contra 3 millones de datos?

Estructura de la tabla

| id [INT Auto Inc] | name_id (uuid) | name (varchar) | city (varchar) | name_type (varchar) |

Consulta

El propósito de la consulta es eliminar los duplicados, donde el duplicado se define como tener el mismo name_id y name.

DELETE
FROM records
WHERE id NOT IN
      (SELECT DISTINCT
          ON (name_id, name) id
       FROM records);
Tags:  , , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Hay varias formas de optimizar esta consulta. Una opción es utilizar una CTE (Expresión de tabla común) para realizar la subconsulta en lugar de una subconsulta en la cláusula WHERE.

    WITH cte AS (
  SELECT DISTINCT ON (name_id, name) id
  FROM records
)
DELETE FROM records
WHERE NOT EXISTS (
  SELECT 1 FROM cte WHERE cte.id = records.id
);

    Este enfoque debería mejorar el rendimiento, ya que realiza la deduplicación en una CTE separada antes de eliminar las filas, lo cual puede ser más rápido que utilizar una subconsulta en la cláusula WHERE.

    Otra opción es crear un índice en las columnas name_id y name para acelerar la operación DISTINCT ON en la subconsulta:

    CREATE INDEX name_idx ON records (name_id, name);

    Este índice también puede ayudar a acelerar otras consultas que involucren estas columnas.

    También se podría considerar partir la tabla si tiene una gran cantidad de datos. Al partir la tabla por name_id u otra columna, las consultas se pueden ejecutar en subconjuntos más pequeños de datos, mejorando el rendimiento.

    Además, asegurarse de que la base de datos y el servidor estén correctamente ajustados también puede ayudar con el rendimiento de la consulta. Esto incluye optimizar configuraciones como la memoria, la E/S de disco y la utilización de la CPU para asegurarse de que la base de datos se esté ejecutando de manera eficiente.

Comments are closed.