Better join ordering #358
Description
Right now join ordering is fixed - determined by the order specified within the query itself. If either the left or the right side of the join is a leaf, that is either a table scan or index scan, we should determine the best order and therefore the best join implementation based on the size of the input tables.
For example, given the following query
select l.* from l join r on l.id = r.id where r.a > 5
if both l and r have primary key indexes on id, and r has a btree index on a, we could generate an index nested loop join with the l side being the index side and the r side being the probe side. However if l only has 10 rows but r has 100,000 rows, if the filter on r is not very selective, this plan will be orders of magnitude slower than if we chose the l side to be the probe side and the r side to be the index side.