我有以下表格:
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_unitsworkers都很少有相关技能。
work_units_skillswork_unit_id上有索引。
我试着将workers_skills上的查询移动到CTE中。这给了一个轻微的改善(10-15%),但它仍然太慢。
一个没有技能的工作单元是任何用户都可以选择的。又名空集是每个集的子集。

最佳答案

一个简单的加速方法是使用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进行测试

关于sql - 如何在PostgreSQL中有效地设置减除联接表?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47440855/

10-12 03:06