From: | Dilip kumar <dilip(dot)kumar(at)huawei(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Cc: | Nicolas Barbier <nicolas(dot)barbier(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Proposal for Merge Join for Non '=' Operators |
Date: | 2014-04-29 05:49:30 |
Message-ID: | 4205E661176A124FAF891E0A6BA9135266305098@szxeml509-mbs.china.huawei.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2014-04-29 06:56:28 | Re: includedir_internal headers are not self-contained |
Previous Message | amul sul | 2014-04-29 05:47:46 | Re: Different behaviour of concate() and concate operator || |