If you specify joining condition other than equality then nested loop join is invoked.
Example Query : explain analyze select name from student,takes where student.id > takes.id
If both the input to join is sorted on joining condition then merge join is invoked.
Example Query: explain analyze select stud.name from (select id, name from student order by id) stud,(select id,year from takes order by id) t where stud.id = t.id
I am trying to analyze join performance. But I see that even for a table having 100,000 rows and join attribute as primary key, postgres always performs hash join.
Can anyone please tell me under which conditions merge join or nested loop join is invoked?