title: Avoid nested loop in PostgreSQL
body: <p>See query below</p>
  
  <pre><code>Select count(*) FROM
  (Select distinct Student_ID, Name, Student_Age, CourseID from student) a1
  JOIN
  (Select distinct CourseID, CourseName, TeacherID from courses) a2
  ON a1.CourseID=a2.CourseID
  JOIN 
  (Select distinct TeacherID, TeacherName, Teacher_Age from teachers) a3
  ON a2.TeacherID=a3.TeacherID
  </code></pre>
  
  <p>The subqueries must be used for deduping purpose. </p>
  
  <p>This query run fine in PostgreSQL. However, if I add a condition between the student and teacher table, according to the execution plan, Postgres will wrongly nested loop join the student and teach tables which have no direct relationship. For example:</p>
  
  <pre><code>Select count(*) FROM
  (Select distinct Student_ID, Name, Student_Age, CourseID from student) a1
  JOIN
  (Select distinct CourseID, CourseName, TeacherID from courses) a2
  ON a1.CourseID=a2.CourseID
  JOIN 
  (Select distinct TeacherID, TeacherName, Teacher_Age from teachers) a3 ON
   a2.TeacherID=a3.TeacherID
  WHERE Teacher_Age&gt;=Student_Age
  </code></pre>
  
  <p>This query will take forever to run. However, if I replace the subqueries with the tables, it'll run very fast. Without using temp tables to store the deduping result, is there a way to to avoid the nested loop in this situation?</p>
  
  <p>Thank you for your help.</p>