sql - 如何在 PostgreSQL 中有效地设置减法连接表？

Question

我有以下表格:

• work_units - 不言自明
• workers - 不言自明
• skills - 每个工作单元都需要一些技能才能工作。每个 worker 都精通多项技能。
• 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 以进行测试