title: Table join order in postgres
body: <p>Is there a way for me to force a specific join order in Postgres?</p>
  
  <p>I've got a query that looks like this. I've eliminated a bunch of stuff that was in the real query, but this simplification demonstrates the issue. What's left shouldn't be too cryptic: Using a role/task security system, I'm trying to determine whether a given user has privileges to perform a given task.</p>
  
  <pre><code>select task.taskid
  from userlogin
  join userrole using (userloginid)
  join roletask using (roleid)
  join task using (taskid)
  where loginname='foobar'
  and taskfunction='plugh'
  </code></pre>
  
  <p>But I realized that the program already knows the value of userlogin, so it seemed the query could be made more efficient by skipping the lookup on userlogin and just filling in the userloginid, like this:</p>
  
  <pre><code>select task.taskid
  from userrole
  join roletask using (roleid)
  join task using (taskid)
  where userloginid=42
  and taskfunction='plugh'
  </code></pre>
  
  <p>When I did that -- eliminating a table from the query and hard-coding the value retrieved from that table instead -- the explain plan time went up! In the original query, Postgres read userlogin then userrole then roletask then task. But in the new query, it decided to read roletask first, and then join to userrole, even though this required doing a full-file scan on roletask.</p>
  
  <p>Full explain plans are:</p>
  
  <p>Version 1:</p>
  
  <pre><code>Hash Join  (cost=12.79..140.82 rows=1 width=8) 
    Hash Cond: (roletask.taskid = task.taskid) 
    -&gt;  Nested Loop  (cost=4.51..129.73 rows=748 width=8) 
          -&gt;  Nested Loop  (cost=4.51..101.09 rows=12 width=8) 
                -&gt;  Index Scan using idx_userlogin_loginname on userlogin  (cost=0.00..8.27 rows=1 width=8) 
                      Index Cond: ((loginname)::text = 'foobar'::text) 
                -&gt;  Bitmap Heap Scan on userrole  (cost=4.51..92.41 rows=33 width=16) 
                      Recheck Cond: (userrole.userloginid = userlogin.userloginid) 
                      -&gt;  Bitmap Index Scan on idx_userrole_login  (cost=0.00..4.50 rows=33 width=0) 
                            Index Cond: (userrole.userloginid = userlogin.userloginid) 
          -&gt;  Index Scan using idx_roletask_role on roletask  (cost=0.00..1.50 rows=71 width=16) 
                Index Cond: (roletask.roleid = userrole.roleid) 
    -&gt;  Hash  (cost=8.27..8.27 rows=1 width=8) 
          -&gt;  Index Scan using idx_task_taskfunction on task  (cost=0.00..8.27 rows=1 width=8) 
                Index Cond: ((taskfunction)::text = 'plugh'::text) 
  </code></pre>
  
  <p>Version 2:</p>
  
  <pre><code>Hash Join  (cost=96.58..192.82 rows=4 width=8) 
    Hash Cond: (roletask.roleid = userrole.roleid) 
    -&gt;  Hash Join  (cost=8.28..104.10 rows=9 width=16) 
          Hash Cond: (roletask.taskid = task.taskid) 
          -&gt;  Seq Scan on roletask  (cost=0.00..78.35 rows=4635 width=16) 
          -&gt;  Hash  (cost=8.27..8.27 rows=1 width=8) 
                -&gt;  Index Scan using idx_task_taskfunction on task  (cost=0.00..8.27 rows=1 width=8) 
                      Index Cond: ((taskfunction)::text = 'plugh'::text) 
    -&gt;  Hash  (cost=87.92..87.92 rows=31 width=8) 
          -&gt;  Bitmap Heap Scan on userrole  (cost=4.49..87.92 rows=31 width=8) 
                Recheck Cond: (userloginid = 42) 
                -&gt;  Bitmap Index Scan on idx_userrole_login  (cost=0.00..4.49 rows=31 width=0) 
                      Index Cond: (userloginid = 42) 
  </code></pre>
  
  <p>(Yes, I know that in both cases the costs are low and the difference doesn't look like it would matter. But this is after I eliminated a bunch of additional work from the query to simplify what I have to post. The real query still isn't outrageous, but I'm more interested in the principle.)</p>