Pranay Singh created IMPALA-7462:
------------------------------------
Summary: HASH JOIN takes lot of memory
Key: IMPALA-7462
URL: https://issues.apache.org/jira/browse/IMPALA-7462
Project: IMPALA
Issue Type: Bug
Components: Frontend
Affects Versions: Impala 2.12.0, Impala 2.13.0
Reporter: Pranay Singh
While running .a SQL query the HASH JOIN node is consuming 270.38 GB of memory when the same query is run with STRAIGHT JOIN hint it consumes much less memory.
Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem
------------------------------------------------------------------------------------------------------------
25:EXCHANGE 1 0.000ns 0.000ns 0 7.72K 0 0
24:AGGREGATE 4 255.368us 276.468us 0 7.72K 221.12 KB 10.00 MB
23:EXCHANGE 4 0.000ns 0.000ns 0 7.72K 0 0
15:AGGREGATE 4 0.000ns 0.000ns 0 7.72K 221.12 KB 10.00 MB
14:HASH JOIN 4 260.644us 323.185us 0 7.72K 4.10 MB 1.94 MB
|--22:EXCHANGE 4 788.270us 1.013ms 39.41K 39.41K 0 0
| 08:SCAN HDFS 1 27.433ms 27.433ms 39.41K 39.41K 1.96 MB 32.00 MB
13:HASH JOIN 4 422.098us 473.574us 0 460.28K 24.09 MB 8.74 MB
|--21:EXCHANGE 4 109.823ms 183.404ms 347.14K 347.14K 0 0
| 07:SCAN HDFS 1 980.437ms 980.437ms 347.14K 347.14K 9.33 MB 32.00 MB
12:HASH JOIN 4 0.000ns 0.000ns 0 27.89M 270.38 GB 34.00 MB ====>
|--20:EXCHANGE 4 13m45s 16m52s 888.87M 68.40K 0 0
| 11:HASH JOIN 6 2s464ms 2s732ms 888.03M 68.40K 2.66 MB 1.94 MB
| |--19:EXCHANGE 6 365.621ms 429.889ms 301 1 0 0
| | 09:SCAN HDFS 1 917.603ms 917.603ms 301 1 1.28 MB 48.00 MB
| 10:HASH JOIN 6 29.824ms 62.248ms 2.95M 4.51M 2.65 MB 1.94 MB
| |--18:EXCHANGE 6 401.490ms 462.427ms 1 1 0 0
| | 00:SCAN HDFS 1 1s018ms 1s018ms 1 1 97.01 KB 16.00 MB
| 06:NESTED LOOP JOIN 6 34.919ms 63.736ms 1.51B 4.26M 714.00 KB 48.00 B
| |--17:EXCHANGE 6 6.812us 9.223us 1 1 0 0
| | 04:SCAN HDFS 1 14.821ms 14.821ms 1 1 289.08 KB 48.00 MB
| 05:HASH JOIN 6 260.485ms 340.747ms 2.95M 4.26M 2.67 MB 1.94 MB
| |--16:EXCHANGE 6 2.445us 3.322us 0 5 0 0
| | 03:SCAN HDFS 6 30.195ms 36.123ms 0 5 777.16 KB 80.00 MB
| 02:SCAN HDFS 6 616.372ms 721.128ms 2.95M 4.26M 175.03 MB 1.38 GB
01:SCAN HDFS 4 316.358us 395.948us 0 36.27M 4.00 KB 440.00 MB
With STRAIGHT_JOIN hint the query plan looks as follows
Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem
--------------------------------------------------------------------------------------------------------
25:EXCHANGE 1 16s983ms 16s983ms 1.02K 1 0 0
24:AGGREGATE 1 381.264ms 381.264ms 31.77K 1 106.31 MB 10.00 MB
23:EXCHANGE 1 16s154ms 16s154ms 320.86K 1 0 0
15:AGGREGATE 1 545.162ms 545.162ms 320.86K 1 35.41 MB 10.00 MB
14:HASH JOIN 1 75.456ms 75.456ms 345.04K 1 2.06 MB 1.94 MB
|--22:EXCHANGE 1 9.060us 9.060us 301 1 0 0
| 09:SCAN HDFS 1 24.245ms 24.245ms 301 1 1.28 MB 48.00 MB
13:HASH JOIN 1 105.532ms 105.532ms 1.52M 71 4.09 MB 1.94 MB
|--21:EXCHANGE 1 1.000ms 1.000ms 39.41K 39.41K 0 0
| 08:SCAN HDFS 1 981.664ms 981.664ms 39.41K 39.41K 1.89 MB 32.00 MB
12:HASH JOIN 1 129.551ms 129.551ms 1.52M 4.25K 24.08 MB 8.74 MB
|--20:EXCHANGE 1 10.809ms 10.809ms 347.14K 347.14K 0 0
| 07:SCAN HDFS 1 967.737ms 967.737ms 347.14K 347.14K 9.01 MB 32.00 MB
11:HASH JOIN 1 10s171ms 10s171ms 1.55M 262.03K 3.25 GB 1.60 GB ===>
|--19:EXCHANGE 1 1s735ms 1s735ms 9.39M 4.26M 0 0
| 06:NESTED LOOP JOIN 4 158.259ms 230.376ms 4.81B 4.26M 5.43 MB 48.00 B
| |--18:EXCHANGE 4 2.900us 3.643us 0 1 0 0
| | 04:SCAN HDFS 1 10.256ms 10.256ms 0 1 96.08 KB 48.00 MB
| 05:HASH JOIN 4 314.641ms 490.798ms 9.39M 4.26M 7.07 MB 1.94 MB
| |--17:EXCHANGE 4 2.778us 3.097us 0 5 0 0
| | 03:SCAN HDFS 7 49.655ms 120.168ms 0 5 445.08 KB 80.00 MB
| 02:SCAN HDFS 4 655.182ms 768.795ms 9.39M 4.26M 289.88 MB 1.38 GB
10:HASH JOIN 1 266.137ms 266.137ms 1.55M 32.97M 162.04 MB 1.49 GB
|--16:EXCHANGE 1 1s101ms 1s101ms 1.55M 36.29M 0 0
| 01:SCAN HDFS 4 1s873ms 1s972ms 1.55M 36.29M 118.23 MB 440.00 MB
00:SCAN HDFS 1 38.548ms 38.548ms 1 1 97.01 KB 16.00 MB
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org