Re: Proposal for Merge Join for Non '=' Operators

On 10 April 2014 14:21, I wrote

>
> I shall perform some more test, for that I need to do some more hack in
> the code and I will post them soon..
>
> Test Scenario:
>     Create table t1 (a int, b int);
>     Create table t2 (a int, b int);
>
>     Random record inserted in t1 and t2, as per attached files. (10K
> records are inserted in both the tables)
>
>     Performance is taken for the query : select count(*) from t1,t2
> where t1.b < t2.b;
>
> Test Result:
>     Nest Loop Join :     Time: 36038.842 ms
>     Merge Join     :  Time: 19774.975 ms

>     Number of record selected: 42291979
I have some more testing with index and multiple conditions..
Test Scenario:    Create table t1 (a int, b int);    Create table t2 (a int, b int);
Create index t1_idx t1(b);Create index t1_idx t1(b);
Query: select count(*) from t1,t2 where t1.b<t2.b and t1.b > 12000;
Test Result:     Nest Loop Join with Index Scan   : 1653.506 ms    Sort Merge Join for (seq scan)   : 610.257ms

From above both the scenario Sort merge join for < operator is faster than NLJ (using seq scan or index scan).

Any suggestion for other performance scenarios are welcome..
Thanks & Regards,
Dilip Kumar