Skip to Content
avatar image
Former Member

Hash PushDown Join validity


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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers