代码之家 › 专栏 › 技术社区 › Jay

postgres中的表连接顺序

postgresql database sql

Jay · 技术社区 · 16 年前

我有一个看起来像这样的问题。我已经删除了真实查询中的一堆东西,但这种简化证明了这个问题。剩下的不应该太隐晦:使用角色/任务安全系统,我试图确定给定的用户是否有权限执行给定的任务。

select task.taskid
from userlogin
join userrole using (userloginid)
join roletask using (roleid)
join task using (taskid)
where loginname='foobar'
and taskfunction='plugh'

select task.taskid
from userrole
join roletask using (roleid)
join task using (taskid)
where userloginid=42
and taskfunction='plugh'

Hash Join  (cost=12.79..140.82 rows=1 width=8) 
  Hash Cond: (roletask.taskid = task.taskid) 
  ->  Nested Loop  (cost=4.51..129.73 rows=748 width=8) 
        ->  Nested Loop  (cost=4.51..101.09 rows=12 width=8) 
              ->  Index Scan using idx_userlogin_loginname on userlogin  (cost=0.00..8.27 rows=1 width=8) 
                    Index Cond: ((loginname)::text = 'foobar'::text) 
              ->  Bitmap Heap Scan on userrole  (cost=4.51..92.41 rows=33 width=16) 
                    Recheck Cond: (userrole.userloginid = userlogin.userloginid) 
                    ->  Bitmap Index Scan on idx_userrole_login  (cost=0.00..4.50 rows=33 width=0) 
                          Index Cond: (userrole.userloginid = userlogin.userloginid) 
        ->  Index Scan using idx_roletask_role on roletask  (cost=0.00..1.50 rows=71 width=16) 
              Index Cond: (roletask.roleid = userrole.roleid) 
  ->  Hash  (cost=8.27..8.27 rows=1 width=8) 
        ->  Index Scan using idx_task_taskfunction on task  (cost=0.00..8.27 rows=1 width=8) 
              Index Cond: ((taskfunction)::text = 'plugh'::text)

Hash Join  (cost=96.58..192.82 rows=4 width=8) 
  Hash Cond: (roletask.roleid = userrole.roleid) 
  ->  Hash Join  (cost=8.28..104.10 rows=9 width=16) 
        Hash Cond: (roletask.taskid = task.taskid) 
        ->  Seq Scan on roletask  (cost=0.00..78.35 rows=4635 width=16) 
        ->  Hash  (cost=8.27..8.27 rows=1 width=8) 
              ->  Index Scan using idx_task_taskfunction on task  (cost=0.00..8.27 rows=1 width=8) 
                    Index Cond: ((taskfunction)::text = 'plugh'::text) 
  ->  Hash  (cost=87.92..87.92 rows=31 width=8) 
        ->  Bitmap Heap Scan on userrole  (cost=4.49..87.92 rows=31 width=8) 
              Recheck Cond: (userloginid = 42) 
              ->  Bitmap Index Scan on idx_userrole_login  (cost=0.00..4.49 rows=31 width=0) 
                    Index Cond: (userloginid = 42)