Aggarwal-Raghav commented on PR #6831:
URL: https://github.com/apache/hive/pull/6831#issuecomment-5911212540
I ran a small test by runnig the following query with and without patch on
10Mil rows. The last sql query insert overwrite
93.422 seconds -> 29.528 seconds
```sql
set hive.optimize.sort.dynamic.partition.threshold=0;
set hive.cbo.enable=true;
set hive.stats.autogather=false;
set hive.exec.reducers.max=1099;
DROP TABLE IF EXISTS sdpo_nightmare_src;
DROP TABLE IF EXISTS sdpo_nightmare_dest;
CREATE TABLE sdpo_nightmare_src (id string, payload string, category string)
STORED AS PARQUET;
CREATE TABLE sdpo_nightmare_dest (id string, payload string) PARTITIONED BY
(category string) STORED AS PARQUET;
WITH t1000 AS (
SELECT pos FROM (SELECT 1) t LATERAL VIEW posexplode(split(space(999), '
')) dummy AS pos, val
)
INSERT INTO sdpo_nightmare_src
SELECT
reflect("java.util.UUID", "randomUUID"),
repeat('x', 1000),
if(rand() < 0.5, 'A', 'B')
FROM t1000 a
CROSS JOIN t1000 b
CROSS JOIN (SELECT pos FROM t1000 LIMIT 10) c;
EXPLAIN INSERT OVERWRITE TABLE sdpo_nightmare_dest PARTITION (category)
SELECT id, payload, category FROM sdpo_nightmare_src;
INSERT OVERWRITE TABLE sdpo_nightmare_dest PARTITION (category)
SELECT id, payload, category FROM sdpo_nightmare_src;
```
Before Patch Plan:
```
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| Plan optimized by CBO. |
| |
| Vertex dependency in root stage |
| Reducer 2 <- Map 1 (SIMPLE_EDGE) |
| |
| Stage-0 |
| Move Operator |
| table:{"name:":"raghav.sdpo_nightmare_dest"} |
| Stage-2 |
| Dependency Collection{} |
| Stage-1 |
| Reducer 2 vectorized |
| File Output Operator [FS_8] |
| table:{"name:":"raghav.sdpo_nightmare_dest"} |
| Select Operator [SEL_7] |
| Output:["_col0","_col1","_col2"] |
| <-Map 1 [SIMPLE_EDGE] vectorized |
| SHUFFLE [RS_6] |
| PartitionCols:_col2 |
| Select Operator [SEL_5] (rows=73784 width=507) |
| Output:["_col0","_col1","_col2"] |
| TableScan [TS_0] (rows=73784 width=507) |
|
raghav@sdpo_nightmare_src,sdpo_nightmare_src,Tbl:COMPLETE,Col:NONE,Output:["id","payload","category"]
|
| |
+----------------------------------------------------+
```
After Patch Plan:
```
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| Plan optimized by CBO. |
| |
| Stage-0 |
| Move Operator |
| table:{"name:":"raaggarw.sdpo_nightmare_dest"} |
| Stage-2 |
| Dependency Collection{} |
| Stage-1 |
| Map 1 vectorized |
| File Output Operator [FS_4] |
| table:{"name:":"raaggarw.sdpo_nightmare_dest"} |
| Select Operator [SEL_3] (rows=73787 width=507) |
| Output:["_col0","_col1","_col2"] |
| TableScan [TS_0] (rows=73787 width=507) |
|
raaggarw@sdpo_nightmare_src,sdpo_nightmare_src,Tbl:COMPLETE,Col:NONE,Output:["id","payload","category"]
|
| |
+----------------------------------------------------+
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
