Skip to Content
0

Hash PushDown Join validity

Dec 20, 2016 at 08:17 PM

81

avatar image
Former Member

Hi

I have faced with an optimizer problem during the migration from IQ 15.2 GA to IQ 16 SP11 PL04. A number of queries work slower IQ 16, mostly because in some cases the query optimizer switched from Hash PushDown Join (HPDJ) to Sort Merge Join (SMJ). In fact, HPDJ is not even considered as a valid join algorithm anymore in IQ 16.

After a number of tests and a major simplification of the problem, I discovered that the problem is somehow dependent on the data distribution in involved tables. I prepared two test data sets. With one data set the optimizer chooses HPDJ and the query runs very fast. However, with another data set, the optimizer doesn't even consider HPDJ, as well as other pushdown algorithms, to be a valid join algorithm and chooses SMJ as result. Does somebody knows why?

What a necessary conditions for HPDJ to be valid? I attach query plans to demonstrate the problem? Just to be sure, the only different factor between the two test cases is the data itself, table and index structures are exactly the same.

Thanks in advance
Leonid Gvirtz

10 |10000 characters needed characters left characters exceeded
Former Member

Since this website doesn't allow to attach html files, I put links to to query plans below:

Hash PushDown Join

Sort Merge Join

0
* Please Login or Register to Answer, Follow or Comment.

0 Answers