suma - ¿Cómo configurar de manera eficiente restar una tabla de unión en PostgreSQL?

restar dos consultas sql

Question

Tengo las siguientes tablas:

• work_units - auto explicativo
• workers - auto explicativo
• skills : cada unidad de trabajo requiere una serie de habilidades si desea trabajar en ellas. Cada trabajador es competente en una serie de habilidades.
• work_units_skills - unir tabla
• workers_skills - unirse a la tabla

Un trabajador puede solicitar la siguiente unidad de trabajo de prioridad más alta gratuita (lo que sea que signifique) que se le asignará.

Actualmente tengo:

SELECT work_units.*
FROM work_units
-- some joins
WHERE NOT EXISTS (
        SELECT skill_id
        FROM work_units_skills
        WHERE work_unit_id = work_units.id

        EXCEPT

        SELECT skill_id
        FROM workers_skills
        WHERE worker_id = 1 -- the worker id that made the request
      )
-- AND a bunch of other conditions
-- ORDER BY something complex
LIMIT 1
FOR UPDATE SKIP LOCKED;

Sin embargo, esta condición hace que la consulta sea de 8 a 10 veces más lenta.

¿Hay una mejor manera de expresar que las habilidades de work_units deben ser un subconjunto de las habilidades de los workers o algo para mejorar la consulta actual?

Un poco más de contexto:

• La tabla de skills es bastante pequeña.
• Tanto work_units como los workers tienden a tener muy pocas habilidades asociadas.
• work_units_skills tiene índice en work_unit_id .
• Intenté mover la consulta sobre workers_skills a un CTE. Esto dio una leve mejora (10-15%), pero aún es demasiado lento.
• Cualquier unidad de trabajo sin habilidad puede ser recogida por cualquier usuario. Aka un conjunto vacío es un subconjunto de cada conjunto.

Answer 1

(ver ACTUALIZACIÓN abajo)

Esta consulta encuentra una buena work_unit con un simple IZQUIERDA IZQUIERDA para encontrar una habilidad faltante en la tabla de habilidades más corta que tiene el trabajador solicitante. El truco es que siempre que falte una habilidad, habrá un valor NULO en la unión y esto se traduce a un 1 y la work_unit se elimina al dejar los valores con todos los valores de 0 , es decir, tener un max de 0 .

Siendo SQL clásico, esta sería la consulta más fuertemente dirigida a optimización por el motor:

SELECT work_unit_id
FROM
  work_units_skills s
LEFT JOIN
  (SELECT skill_id FROM workers_skills WHERE worker_id = 1) t
ON (s.skill_id=t.skill_id)
GROUP BY work_unit_id
HAVING max(CASE WHEN t.skill_id IS NULL THEN 1 ELSE 0 END)=0
-- AND a bunch of other conditions
-- ORDER BY something complex
LIMIT 1
FOR UPDATE SKIP LOCKED;

ACTUALIZAR

Para capturar work_units sin habilidades, lanzamos la tabla work_units en JOIN:

SELECT r.id AS work_unit_id
FROM
  work_units r
LEFT JOIN
  work_units_skills s ON (r.id=s.work_unit_id)
LEFT JOIN
  (SELECT skill_id FROM workers_skills WHERE worker_id = 1) t
ON (s.skill_id=t.skill_id)
GROUP BY r.id
HAVING bool_or(s.skill_id IS NULL) OR bool_and(t.skill_id IS NOT NULL)
-- AND a bunch of other conditions
-- ORDER BY something complex
LIMIT 1
FOR UPDATE SKIP LOCKED;

Answer 2

Con Postgres, la división relacional a menudo se puede expresar de manera más eficiente utilizando matrices.

En tu caso creo que lo siguiente hará lo que quieras:

select *
from work_units
where id in (select work_unit_id
             from work_units_skills
             group by work_unit_id
             having array_agg(skill_id) <@ array(select skill_id 
                                                 from workers_skills 
                                                 where worker_id = 6))
and ... other conditions here ...
order by ...

array_agg(skill_id) recopila todos los skill_ids para cada work_unit y los compara con las habilidades de un trabajador específico utilizando el operador <@ ("está contenido por"). Esa condición devuelve todos los work_unit_ids donde la lista de skill_ids está contenida en las habilidades para un solo trabajador.

En mi experiencia, este enfoque suele ser más rápido que el equivalente o cruzar soluciones.

Ejemplo en línea: http://rextester.com/WUPA82849

Answer 3

Con la información actual solo puedo responder en una corazonada. Intente eliminar la instrucción EXCEPT y ver si se vuelve significativamente más rápido. Si lo hace, puede agregar esa parte de nuevo, pero usando las condiciones de DÓNDE. En mi experiencia, los operadores de conjuntos (MINUS / EXCEPT, UNION, INTERSECT) son los asesinos de rendimiento.

Answer 4

La subconsulta correlacionada lo está castigando, especialmente con el uso adicional de EXCEPTO.

Parafraseando su consulta, solo le interesa un work_unit_id cuando un trabajador específico tiene TODAS las habilidades de work_unit? (Si una work_unit tiene una habilidad asociada, pero el usuario especificado no tiene esa habilidad, ¿excluir esa work_unit?)

Esto se puede lograr con JOIN y GROUP BY, y no es necesaria la correlación.

SELECT
    work_units.*
FROM
    work_units
--
-- some joins
--
INNER JOIN
(
    SELECT
        wus.work_unit_id
    FROM
        work_unit_skills   wus
    LEFT JOIN
        workers_skills     ws
            ON  ws.skill_id  = wus.skill_id
            AND ws.worker_id = 1
    GROUP BY
        wus.work_unit_id
    HAVING
        COUNT(wus.skill_id) = COUNT(ws.skill_id)
)
     applicable_work_units
         ON  applicable_work_units.work_unit_id = work_units.id
-- AND a bunch of other conditions
-- ORDER BY something complex
LIMIT 1

La subconsulta compara el conjunto de habilidades de un trabajador con el conjunto de habilidades de cada unidad de trabajo. Si hay alguna habilidad que tiene la unidad de trabajo que el trabajador no tiene, entonces ws.skill_id será NULL para esa fila, y como NULL es ignorado por COUNT() esto significa que COUNT(ws.skill_id) será menor que COUNT(wus.skill_id) , y para que work_unit se excluya de los resultados de la work_unit .

Esto supone que la tabla workers_skills es única sobre (work_id, skill_id) y que la tabla work_unit_skills es única sobre (work_unit_id, skill_id) . Si ese no es el caso, es posible que desee jugar con la cláusula HAVING (como COUNT(DISTINT wus.skill_id) , etc.) .

EDITAR:

La consulta anterior asume que solo un número relativamente bajo de unidades de trabajo coincidiría con los criterios de coincidencia con un trabajador específico.

Si asume que una cantidad relativamente grande de unidades de trabajo coincidiría, la lógica opuesta sería más rápida.

(Esencialmente, intente hacer que el número de filas devueltas por la subconsulta sea lo más bajo posible).

SELECT
    work_units.*
FROM
    work_units
--
-- some joins
--
LEFT JOIN
(
    SELECT
        wus.work_unit_id
    FROM
        work_unit_skills   wus
    LEFT JOIN
        workers_skills     ws
            ON  ws.skill_id  = wus.skill_id
            AND ws.worker_id = 1
    WHERE
        ws.skill_id IS NULL
    GROUP BY
        wus.work_unit_id
)
     excluded_work_units
         ON  excluded_work_units.work_unit_id = work_units.id
WHERE
    excluded_work_units.work_unit_id IS NULL
-- AND a bunch of other conditions
-- ORDER BY something complex
LIMIT 1

Este compara todas las habilidades de la unidad de trabajo con las del trabajador, y solo mantiene filas donde la unidad de trabajo tiene habilidades que el trabajador no tiene.

Luego, GROUP BY la unidad de trabajo para obtener una lista de las unidades de trabajo que deben ignorarse.

Si los une LEFT a los resultados existentes, puede estipular que solo desea incluir una unidad de trabajo si no aparece en la subconsulta especificando excluded_work_units.work_unit_id IS NULL .

Las guías en línea útiles se referirán a anti-join y anti-semi-join .

EDITAR:

En general, recomendaría contra el uso de una máscara de bits.

No porque sea lento, sino porque desafía la normalización. La existencia de un solo campo que representa múltiples elementos de datos es un anti-patrón-código-sql-olor-sql general, ya que los datos ya no son atómicos. (Esto conduce al dolor en el futuro, especialmente si llega a un mundo donde tiene tantas habilidades que ya no todas se ajustan al tipo de datos elegido para la máscara de bits, o cuando se trata de gestionar cambios frecuentes o complejos para los conjuntos de habilidades.)

Dicho esto, si el rendimiento sigue siendo un problema, la des-normalización suele ser una opción muy útil. Recomiendo mantener las máscaras de bits en tablas separadas para que quede claro que son resultados de cálculo de valores normalizados / en caché. En general, sin embargo, tales opciones deberían ser un último recurso en lugar de una primera reacción.

EDITAR: Ejemplo de revisiones para incluir siempre work_units que no tienen habilidades ...

SELECT
    work_units.*
FROM
    work_units
--
-- some joins
--
INNER JOIN
(
    SELECT
        w.id   AS work_unit_id
    FROM
        work_units          w
    LEFT JOIN
        work_units_skills   wus
            ON wus.work_unit_id = w.id
    LEFT JOIN
        workers_skills      ws
            ON  ws.skill_id  = wus.skill_id
            AND ws.worker_id = 1
    GROUP BY
        w.id
    HAVING
        COUNT(wus.skill_id) = COUNT(ws.skill_id)
)
     applicable_work_units
         ON  applicable_work_units.work_unit_id = work_units.id

La versión excluded_work_units del código (la segunda consulta de ejemplo anterior) debería funcionar sin necesidad de modificación para este caso de esquina (y es la que probé inicialmente para las métricas de rendimiento en vivo) .

Answer 5

Puede obtener las unidades de trabajo cubiertas por las habilidades de un trabajador en una agregación, como ya se ha demostrado. Normalmente utilizarías IN en este conjunto de unidades de trabajo.

SELECT wu.*
FROM work_units wu
-- some joins
WHERE wu.id IN
(
  SELECT wus.work_unit_id
  FROM work_units_skills wus
  LEFT JOIN workers_skills ws ON ws.skill_id = wus.skill_id AND ws.worker_id = 1
  GROUP BY wus.work_unit_id
  HAVING COUNT(*) = COUNT(ws.skill_id)
)
-- AND a bunch of other conditions
-- ORDER BY something complex
LIMIT 1
FOR UPDATE SKIP LOCKED;

Sin embargo, cuando se trata de acelerar las consultas, la parte principal es proporcionar los índices apropiados. (Con un optimizador perfecto, volver a escribir una consulta para obtener el mismo resultado no tendría ningún efecto, ya que el optimizador obtendría el mismo plan de ejecución).

Desea los siguientes índices (importa el orden de las columnas):

create index idx_ws on workers_skills (worker_id, skill_id);
create index idx_wus on work_units_skills (skill_id, work_unit_id);

(Léalo así: Venimos con un worker_id , obtenemos los skill_ids para el trabajador, unimos las unidades de trabajo en estos skill_ids y obtenemos así el work_unit_ids ).

Answer 6

Puede que no se aplique a usted, pero tuve un problema similar que resolví simplemente fusionando main y sub en la misma columna usando números para main y letras para sub.

Por cierto, ¿están todas las columnas involucradas en las uniones indexadas? Mi servidor pasa de una consulta de 2 a 3 segundos en tablas de 500 k + para bloquearse en tablas de 10 k si olvido

Answer 7

Puede utilizar la siguiente consulta

SELECT wu.*
FROM work_units wu
LEFT JOIN work_units_skills wus ON wus.work_unit_id = wu.id and wus.skill_id IN (
    SELECT id
    FROM skills
    EXCEPT
    SELECT skill_id
    FROM workers_skills
    WHERE worker_id = 1 -- the worker id that made the request
)
WHERE wus.work_unit_id IS NULL;  

demo (gracias, Steve Chambers por la mayoría de los datos)

Definitivamente debería tener un índice en work_units_skills(skill_id) , workers_skills(worker_id) y work_units(id) . Si desea acelerarlo, aún más, cree índices work_units_skills(skill_id, work_unit_id) y workers_skills(worker_id, skill_id) que evitan acceder a esas tablas.

La subconsulta es independiente y la unión externa debe ser relativamente rápida si el resultado no es grande.

Answer 8

Una simple aceleración sería usar EXCEPT ALL lugar de EXCEPT . Este último elimina duplicados, lo cual es innecesario aquí y puede ser lento.

Una alternativa que probablemente sería más rápida es usar un NOT EXISTS adicional en lugar del EXCEPT :

...
WHERE NOT EXISTS (
        SELECT skill_id
        FROM work_units_skills wus
        WHERE work_unit_id = work_units.id
        AND NOT EXISTS (
            SELECT skill_id
            FROM workers_skills ws
            WHERE worker_id = 1 -- the worker id that made the request
              AND ws.skill_id = wus.skill_id
        )
      )

Manifestación

http://rextester.com/AGEIS52439 - con el LIMIT eliminado para la prueba

Answer 9

Solución de máscara de bits
Sin ningún cambio en el diseño de su base de datos anterior, solo agregue 2 campos.
Primero: un largo o bigint (relacionado con su DBMS) en Trabajadores
Segundo: otro largo o bigint en Work_Units

Estos campos muestran habilidades de trabajo y unidades de trabajo. Por ejemplo, suponga que tiene 8 registros en la tabla de habilidades. (aviso que registra de habilidad en pequeño)
1- alguna habilidad 1
2- alguna habilidad 2
...
8- alguna habilidad 8

Entonces, si queremos establecer habilidades 1,3,6,7 en una work_unit, solo use este número 01100101.
(Ofrezco usar una versión invertida de la colocación binaria 0,1 para apoyar habilidades adicionales en el futuro).

En la práctica, puede usar 10 números base para agregar en la base de datos (101 en lugar de 01100101)

Número similar puede ser generado a los trabajadores. Cualquier trabajador elige algunas habilidades. Por lo tanto, podemos convertir los elementos seleccionados en un número y guardarlos en un campo adicional en la tabla Trabajador.

Finalmente , para encontrar el subconjunto de work_units apropiado para cualquier trabajador, SOLO seleccione de work_units y use bitwise Y como abajo.
R: new_field_of_specific_worker (muestra las habilidades de cada trabajador) que estamos buscando en las unidades de trabajo relacionadas con él en este momento.
B: new_field_of_work_units que muestra las habilidades de cada work_unit

select * from work_units
where A & B  = B

Darse cuenta:
1: absolutamente, esta es la manera más rápida pero tiene algunas dificultades.
2: tenemos algunas dificultades adicionales cuando se agrega una nueva habilidad o para eliminarla. Pero esto es una compensación. Agregar o eliminar nuevas habilidades ocurre menos.
3: deberíamos usar skills y work_unit_skills y workers_skills también. Pero en la búsqueda, solo usamos nuevos campos.

Además, este enfoque se puede utilizar para sistemas de gestión de TAG como los TAG de desbordamiento de pila.