spark-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hemant Bhanawat <hemant9...@gmail.com>
Subject Re: SPARK-13900 - Join with simple OR conditions take too long
Date Fri, 01 Apr 2016 09:46:20 GMT
As Mich has already noticed, Spark defaults to NL join if there are more
than one condition. Oracle is probably doing cost-based optimizations in
this scenario. You can call it a bug but in my opinion it is an area where
Spark is still evolving.

>> Hemant has mentioned the nested loop time will be very little.
I had mentioned that NL time will *vary *little with more number of
conditions.  What I meant was that instead of 3 conditions if you would
have 15 conditions, the NL loop would still take 13-15 mins while the hash
join would take more than that.

Hemant

Hemant Bhanawat <https://www.linkedin.com/in/hemant-bhanawat-92a3811>
www.snappydata.io

On Fri, Apr 1, 2016 at 3:08 PM, ashokkumar rajendran <
ashokkumar.rajendran@gmail.com> wrote:

> Hi Mich,
>
> Thanks for the input.
>
> Yes, it seems to be a bug. Is it possible to fix this in next release?
>
> Regards
> Ashok
>
> On Fri, Apr 1, 2016 at 2:06 PM, Mich Talebzadeh <mich.talebzadeh@gmail.com
> > wrote:
>
>> hm.
>>
>> Sounds like it ends up in Nested Loop Join (NLJ) as opposed to Hash Join
>> (HJ) when OR  is used for more than one predicate comparison.
>>
>> In below I have a table dummy created as ORC with 1 billion rows. Just
>> created another one called dummy1 with 60K rows
>>
>> A simple join results in Hash Join good!
>>
>> scala> sql("select d.id, d1.id from dummy d, dummy2 d1 where
>> d.random_string = d1.random_string").explain(true)
>>
>> == Physical Plan ==
>> Project [id#212,id#219]
>>
>> *+- BroadcastHashJoin [random_string#216], [random_string#223],
>> BuildRight*   :- ConvertToUnsafe
>>    :  +- HiveTableScan [id#212,random_string#216], MetastoreRelation
>> test, dummy, Some(d)
>>    +- ConvertToUnsafe
>>       +- HiveTableScan [id#219,random_string#223], MetastoreRelation
>> test, dummy2, Some(d1)
>>
>> When the join is done using OR on other predicates I see it starts doing
>> NLJ
>>
>> scala> sql("select d.id, d1.id from dummy d, dummy2 d1 where
>> d.random_string = d1.random_string OR d.small_vc =
>> d1.small_vc").explain(true)
>>
>> == Physical Plan ==
>> Project [id#241,id#248]
>> +- B*roadcastNestedLoopJoin *BuildRight, Inner, Some(((random_string#245
>> = random_string#252) || (small_vc#246 = small_vc#253)))
>>    :- HiveTableScan [small_vc#246,id#241,random_string#245],
>> MetastoreRelation test, dummy, Some(d)
>>    +- HiveTableScan [id#248,random_string#252,small_vc#253],
>> MetastoreRelation test, dummy2, Some(d1)
>>
>> in contrast the same identical tables in Oracle use Hash Join with OR
>> which is expected
>>
>> scratchpad@MYDB.MICH.LOCAL> select d.id, d1.id from dummy d, dummy2 d1
>> where d.random_string = d1.random_string OR d.small_vc = d1.small_vc;
>>
>> Execution Plan
>> ----------------------------------------------------------
>> Plan hash value: 4163534687
>>
>> --------------------------------------------------------------------------------------
>> | Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost
>> (%CPU)| Time     |
>>
>> --------------------------------------------------------------------------------------
>> |   0 | SELECT STATEMENT    |        | 63207 |  8332K|       |  1280K
>> (1)| 04:16:05 |
>> |   1 |  CONCATENATION      |        |       |       |       |
>> |          |
>> |*  2 |  * HASH JOIN         *|        | 60183 |  7934K|  4632K|   640K
>> (1)| 02:08:03 |
>> |   3 |    TABLE ACCESS FULL| DUMMY2 | 60000 |  3925K|       |   157
>> (1)| 00:00:02 |
>> |   4 |    TABLE ACCESS FULL| DUMMY  |   100M|  6484M|       |   261K
>> (1)| 00:52:13 |
>> |*  5 |   *HASH JOIN         *|        |  3024 |   398K|  4632K|   640K
>> (1)| 02:08:03 |
>> |   6 |    TABLE ACCESS FULL| DUMMY2 | 60000 |  3925K|       |   157
>> (1)| 00:00:02 |
>> |   7 |    TABLE ACCESS FULL| DUMMY  |   100M|  6484M|       |   261K
>> (1)| 00:52:13 |
>>
>> --------------------------------------------------------------------------------------
>>
>> So this looks like a bug!
>>
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>>
>> On 1 April 2016 at 04:53, ashokkumar rajendran <
>> ashokkumar.rajendran@gmail.com> wrote:
>>
>>> Thanks for the reply everyone.
>>>
>>> Let me provide more detail on the dataset as well.
>>> 1. The big table (A) contains more than 3 billion records in parquet
>>> format, which is few TBs.
>>> 2. The second table (B) is only of 60K rows which is less than 10MB.
>>> 3. The column on which I perform JOIN is mostly on the String datatype
>>> columns.
>>> 4. I used 20 machines that were of 16 cores each and 120GB RAMs for
>>> testing this.
>>>
>>> The pseudo OR query is as below.
>>>
>>> sql(Select field1, field2 from A, B where A.dimension1 = B.dimension1 OR A.dimension2
= B.dimension2 OR A.dimension3 = B.dimension3 OR A.dimension4 = B.dimension4).explain(true)
>>>
>>>
>>> Pseudo union query is as below.
>>>
>>> sql{Select field1, field2 from A, B where A.dimension1 = B.dimension1
>>> UNION ALL
>>> Select field1, field2 from A, B where A.dimension2 = B.dimension2
>>> UNION ALL
>>> Select field1, field2 from A, B where A.dimension3 = B.dimension3
>>> UNION ALL
>>> Select field1, field2 from A, B where A.dimension4 = B.dimension4}.explain(true)
>>>
>>> You can look at the explain plan in the ticket
>>> https://issues.apache.org/jira/browse/SPARK-13900
>>>
>>> Hemant has mentioned the nested loop time will be very little. But the
>>> time taken by inner query for this kind of join is too long. Union of 3
>>> HashJoins take only 3 minutes (each dimension hashjoin takes 1 minute),
>>> nested loop join takes nearly 13 mins. I agree that the performance of
>>> HashedJoin on OR condition will be linear but that will be very optimized
>>> comparing to the nested loop join.
>>>
>>> As Yong pointed out, if we can provide hints in SQL engine, it will be
>>> awesome for these kind of cases.
>>>
>>> Any idea on how we can optimize this will be helpful. Please let me know
>>> if any other detail is needed to provide input.
>>>
>>> Regards
>>> Ashok
>>>
>>> On Thu, Mar 31, 2016 at 8:32 PM, Mich Talebzadeh <
>>> mich.talebzadeh@gmail.com> wrote:
>>>
>>>> a hash join come into play when you are joining a large table with
>>>> a small table (large being a relative term)..Hash join by definition works
>>>> on equality condition through hash bucketing.
>>>>
>>>> Can you provide pseudo code for your case please?
>>>>
>>>> HTH
>>>>
>>>> Dr Mich Talebzadeh
>>>>
>>>>
>>>>
>>>> LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>
>>>>
>>>>
>>>> http://talebzadehmich.wordpress.com
>>>>
>>>>
>>>>
>>>> On 31 March 2016 at 09:58, ashokkumar rajendran <
>>>> ashokkumar.rajendran@gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I have filed ticket SPARK-13900. There was an initial reply from a
>>>>> developer but did not get any reply on this. How can we do multiple hash
>>>>> joins together for OR conditions based joins? Could someone please guide
on
>>>>> how can we fix this?
>>>>>
>>>>> Regards
>>>>> Ashok
>>>>>
>>>>
>>>>
>>>
>>
>

Mime
View raw message