Suppose I have two tables, A and B. They "share" the same primary key, which is indexed in both tables. Table B also has a timestamp column which is separately indexed.
I do a subselect on B using the timestamp column (I give it a 24-hour interval for the timestamp to belong to), and inner join the results against A using the primary keys.
A and B are huge (O(10^9) rows). When I give the subselect a recent 24-hour period, it seems that the statistics are poor and the planner underestimates the number of rows coming out of the subselect (say, anywhere between O(1) and O(10^5) rows, while there are actually 6M such rows for the queries I run). It picks a nested loop join, with the subselect output as the "outer" table and A as the "inner" table, accessed via its index. While NL joins have a bad rep, it only takes a few minutes to complete.
When I tell it to look for older timestamps ("old" 24-hour periods), it does understand that there are about 6M rows coming out of the subselect. It picks a hash join, with A as the outer/probe table, and the subselect output as the inner/build table. It does a table scan on A as part of the plan. It doesn't complete even after 48 hours.
Is there a way to force or hint to the planner that it should use the nested loop join?
While I'd be interested in hearing about DB tuning as a solution, this is a box I have no authorization to tune.
...more information: the join switches from nested loop to hash when the number of rows returned by the subselect increases beyond about 1.012*10^6.
