Как эффективно установить вычитание таблицы соединений в PostgreSQL?

Question

У меня есть следующие таблицы:

• work_units - понятно
• workers - понятно
• skills - каждый рабочий блок требует определенных навыков, если вы хотите работать над ним. Каждый работник владеет рядом навыков.
• work_units_skills - присоединиться к столу
• workers_skills - присоединиться к столу

Работница может запросить следующую подходящую бесплатную наивысшую приоритетную (независимо от того, что это означает) единицу работы, которая будет ей назначена.

---

В настоящее время у меня есть:

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;

Это условие делает запрос в 8-10 раз медленнее.

Есть ли лучший способ выразить это work_unitsнавыки должны быть подмножеством workersнавыки или что-то для улучшения текущего запроса?

---

Еще немного контекста:

• skills стол довольно маленький.
• И то и другое work_units а также workers как правило, имеют очень мало связанных навыков.
• work_units_skills имеет индекс на work_unit_id,
• Я пытался переместить запрос на workers_skills в CTE. Это дало небольшое улучшение (10-15%), но все еще слишком медленно.
• Любой пользователь может выбрать рабочую единицу без навыков. Ака пустой набор является подмножеством каждого набора.

Answer 1

Одно простое ускорение будет использовать EXCEPT ALLвместоEXCEPT, Последний удаляет дубликаты, которые здесь не нужны и могут быть медленными.

Альтернативой, которая, вероятно, будет быстрее, является использованиеNOT EXISTSвместо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
        )
      )

демонстрация

http://rextester.com/AGEIS52439- с LIMITудален для тестирования

Answer 2

(см. ОБНОВЛЕНИЕ ниже)

Этот запрос находит хороший work_unit используя простое ЛЕВОЕ СОЕДИНЕНИЕ, чтобы найти недостающий навык в более короткой таблице навыков, которыми обладает запрашивающий работник. Хитрость заключается в том, что когда пропущен навык, в соединении будет указано значение NULL, и оно преобразуется в 1 и work_unit удаляется, оставляя те со всеми 0 значения, т.е. имеющие max из 0,

Будучи классическим SQL, это был бы наиболее направленный запрос для оптимизации движком:

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;

ОБНОВИТЬ

Для того, чтобы поймать work_units без навыков, мы бросаем work_units стол в 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 3

Решение битовой маски
Без каких-либо изменений в вашем предыдущем дизайне базы данных, просто добавьте 2 поля.
Первый: длинный или большой (связанный с вашей СУБД) на рабочих
Второе: еще один длинный или bigint в Work_Units

В этих полях отображаются навыки рабочих единиц и навыки работников. Например, предположим, что у вас есть 8 записей в таблице навыков. (обратите внимание, что записи мастерства в малом)
1 - некоторый навык 1
2 - некоторый навык 2
...
8- некоторый навык 8

затем, если мы хотим установить скиллы 1,3,6,7 для одного work_unit, просто используйте этот номер 01100101.
(Я предлагаю использовать обратную версию бинарного размещения 0,1 для поддержки дополнительных навыков в будущем.)

На практике вы можете использовать 10 базовых номеров для добавления в базу данных (101 вместо 01100101)

Подобное число может быть сгенерировано для работников. Любой работник выбирает некоторые навыки. Таким образом, мы можем превратить выбранные элементы в число и сохранить их в дополнительном поле в рабочей таблице.

Наконец, чтобы найти подходящее подмножество work_units для любого рабочего, просто выберите из work_units и используйте побитовое И, как показано ниже.
A: new_field_of_specific_worker (показывает навыки каждого работника), что мы ищем связанные с ним works_units прямо сейчас.
B: new_field_of_work_units, который показывает навыки каждого work_unit

select * from work_units
where A & B  = B

Обратите внимание:
1: безусловно, это самый быстрый способ, но у него есть некоторые трудности.
2: у нас есть некоторые дополнительные трудности, когда новый навык добавлен или будет удален. Но это компромисс. Добавление или удаление новых навыков происходит меньше.
3: мы должны использовать навыки и work_unit_skills и worker_skills тоже. Но в поиске мы просто используем новые поля

Кроме того, этот подход может использоваться для систем управления TAG, таких как TAG с переполнением стека.

Answer 4

Вы можете использовать следующий запрос

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;  

демо (спасибо, Стив Чамберс за большую часть данных)

Вы должны определенно иметь индекс на work_units_skills(skill_id), workers_skills(worker_id) а также work_units(id), Если вы хотите ускорить, даже больше, создайте индексы work_units_skills(skill_id, work_unit_id) а также workers_skills(worker_id, skill_id) которые избегают доступа к этим таблицам.

Подзапрос независим, и внешнее соединение должно быть относительно быстрым, если результат невелик.

Answer 5

Соответствующий подзапрос наказывает вас, особенно с дополнительным использованием EXCEPT.

Перефразируя ваш запрос, вы заинтересованы только в work_unit_id когда указанный работник имеет ВСЕ навыки этого work_unit? (Если с work_unit связан навык, но указанный пользователь не имеет этого навыка, исключить этот work_unit?)

Этого можно достичь с помощью JOIN и GROUP BY, и нет необходимости в корреляции вообще.

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

Подзапрос сравнивает набор навыков рабочего с набором навыков каждого рабочего блока. Если у рабочей единицы есть какие-то навыки, которых у работника нет ws.skill_id будет NULL для этого ряда, и как NULL игнорируется COUNT() это означает, что COUNT(ws.skill_id) будет ниже, чем COUNT(wus.skill_id) и так что work_unit будет исключен из результатов подзапроса.

Это предполагает, что workers_skills стол уникален (work_id, skill_id) и что work_unit_skills стол уникален (work_unit_id, skill_id), Если это не так, то вы можете повозиться с HAVING пункт (такой как COUNT(DISTINT wus.skill_id) и т. д.).

РЕДАКТИРОВАТЬ:

Приведенный выше запрос предполагает, что только сравнительно небольшое количество рабочих единиц будет соответствовать критериям соответствия конкретного работника.

Если вы предполагаете, что сравнительно большое количество рабочих единиц будет соответствовать, противоположная логика будет быстрее.

(По сути, попытайтесь сделать количество возвращаемых строк подзапросом как можно меньше.)

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

Он сравнивает все навыки рабочего подразделения с навыками рабочего и сохраняет только те строки, в которых рабочее подразделение имеет навыки, которых у рабочего нет.

Затем, GROUP BY рабочий блок, чтобы получить список рабочих блоков, которые необходимо игнорировать.

От LEFT объединяя их с существующими результатами, вы можете указать, что хотите включить рабочую единицу, только если она не отображается в подзапросе, указав excluded_work_units.work_unit_id IS NULL,

Полезные онлайн-руководства будут ссылаться на anti-join а также anti-semi-join,

РЕДАКТИРОВАТЬ:

В общем, я бы рекомендовал против использования бит-маски.

Не потому что он медленный, а потому что он не поддается нормализации. Существование одного поля, представляющего несколько элементов данных, является общим шаблоном sql-code-запах / sql-анти-шаблон, поскольку данные больше не являются атомарными. (Это приводит к дальнейшим трудностям, особенно если вы попадаете в мир, где у вас так много навыков, что они больше не вписываются в тип данных, выбранный для битовой маски, или когда речь идет об управлении частыми или сложными изменениями в наборы навыков.)

Тем не менее, если производительность по-прежнему является проблемой, ненормализация часто является очень полезным вариантом. Я бы рекомендовал хранить битовые маски в отдельных таблицах, чтобы было ясно, что они не нормализованы / кэшированы результаты вычислений. В общем, такие варианты должны быть последним средством, а не первой реакцией.

РЕДАКТИРОВАТЬ: Пример ревизии, чтобы всегда включать work_units, которые не имеют навыков...

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

excluded_work_units версия кода (второй пример запроса выше) должна работать без необходимости модификации для этого углового случая (и это та версия, которую я бы изначально испытал для показателей производительности в реальном времени).

Answer 6

С текущей информацией я могу ответить только на догадку. Попробуйте удалить EXCEPT-оператор и посмотрите, станет ли он значительно быстрее. Если это так, вы можете добавить эту часть еще раз, но с использованием WHERE-условий. По моему опыту операторы наборов (MINUS/EXCEPT, UNION, INTERSECT) являются убийцами производительности.

Answer 7

Вы можете получить рабочие единицы, охватываемые навыками работника, в совокупности, как уже было показано. Вы обычно используете IN на этом наборе рабочих единиц тогда.

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;

Тем не менее, когда речь идет об ускорении запросов, основная часть заключается в предоставлении соответствующих индексов. (При идеальном оптимизаторе переписывание запроса для получения того же результата вообще не будет иметь никакого эффекта, потому что оптимизатор получит тот же план выполнения.)

Вы хотите следующие индексы (порядок столбцов имеет значение):

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

(Прочитайте это так: мы идем с worker_id, получить skill_ids для работника, присоединяйтесь к рабочим подразделениям skill_ids и получить таким образом work_unit_ids.)

Answer 8

С Postgres реляционное деление часто может быть выражено более эффективно с использованием массивов.

В вашем случае я думаю, что следующее будет делать то, что вы хотите:

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) собирает все skill_id для каждого work_unit и сравнивает их с навыками конкретного работника, использующего <@ оператор ("содержится"). Это условие возвращает все work_unit_ids, где список skill_ids содержится в навыках для одного работника.

По моему опыту этот подход обычно быстрее, чем эквивалентные существуют или пересекаются решения.

Пример в сети: http://rextester.com/WUPA82849

Answer 9

Может не относиться к вам, но у меня была похожая проблема, которую я решил, просто объединив основной и вспомогательный элементы в один столбец, используя цифры для основного и буквы для вспомогательного элемента.

Кстати, все ли столбцы, включенные в объединения, проиндексированы? Мой сервер переходит с 2-3-секундного запроса на 500k+ таблиц к сбоям на 10k таблиц, если я забуду