1

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.

1

Hash joins require an "equijoin predicate". So I rewrote the query as an explicit join (instead of IN...(subselect)), and instead of using A.key = B.key as the join condition, I used A.key > B.key - 1 AND A.key < B.key +1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.