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

Ответ 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
        )
      )

Demo

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

Ответ 2

(см. ОБНОВЛЕНИЕ)

Этот запрос находит хороший work_unit, используя простой LEFT JOIN, чтобы найти недостающий навык в более короткой таблице навыков, которые имеет запрашивающий рабочий. Фокус в том, что когда есть недостающее умение, в соединении будет значение NULL, и это будет переведено на 1, а work_unit будет удалено, оставив те, у которых есть все значения 0, т.е. имеющие max of 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;

UPDATE

Чтобы поймать 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;

Ответ 3

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

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), которые избегают доступа к этим таблицам.

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

Ответ 4

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

В этих полях показаны навыки рабочих и умений рабочих. Например, предположим, что у вас есть 8 записей в таблице Skills. (обратите внимание, что записи умения в малом)
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 и workers_skills. Но в поиске мы просто используем новые поля

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

Ответ 5

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

Ответ 6

Корреляционный подзапрос наказывает вас, особенно при дополнительном использовании EXCEPT.

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

Это может быть достигнуто с помощью JOINs и 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) и т.д.).

EDIT:

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

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

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

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.

EDIT:

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

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

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

РЕДАКТИРОВАТЬ: Примеры ревизий, которые всегда включают в себя 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 (второй пример запроса выше) должна работать без необходимости изменения для этого углового случая (и это тот, который я первоначально тестировал для показателей живой производительности).

Ответ 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.)

Ответ 8

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

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

Ответ 9

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

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

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