drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul Rogers (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DRILL-5235) Column or table alias doubles sort data size when reading a text file
Date Sat, 18 Feb 2017 20:33:44 GMT

    [ https://issues.apache.org/jira/browse/DRILL-5235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15873322#comment-15873322
] 

Paul Rogers edited comment on DRILL-5235 at 2/18/17 8:33 PM:
-------------------------------------------------------------

Example case. Query:

{code}
select * from (select * from dfs.`/drill/testdata/250wide.tbl`
    order by columns[0])d where d.columns[0] = 'ljdfhwuehnoiueyf';
{code}

Note that here we have a table alias: d. This seems to cause the doubling in size.

The data file is a large file consisting of a single column 250 characters wide.

Batch as seen by the external sort:

{code}
ExternalSortBatch - Actual batch schema & sizes {
  T0¦¦columns(std col. size: 54, actual col. size: 0, total size: 263942, vector size: 0,
data size: 0, row capacity: 1023, density: 0)
  EXPR$1(std col. size: 54, actual col. size: 250, total size: 282624, vector size: 278528,
data size: 255750, row capacity: 4095, density: 92)
  Records: 1023, Total size: 548614, Row width:538, Density:92}
{code}

The result is that the sort must buffer two copies of the data. Since this is an 18 GB input
file, the sort must buffer (then spill) 36 GB of data. Note that there is actually no column
alias or expression on the column.


was (Author: paul-rogers):
Example case. Query:

{code}
select * from (select * from dfs.`/drill/testdata/250wide.tbl`
    order by columns[0])d where d.columns[0] = 'ljdfhwuehnoiueyf';
{code}

The data file is a large file consisting of a single column 250 characters wide.

Batch as seen by the external sort:

{code}
ExternalSortBatch - Actual batch schema & sizes {
  T0¦¦columns(std col. size: 54, actual col. size: 0, total size: 263942, vector size: 0,
data size: 0, row capacity: 1023, density: 0)
  EXPR$1(std col. size: 54, actual col. size: 250, total size: 282624, vector size: 278528,
data size: 255750, row capacity: 4095, density: 92)
  Records: 1023, Total size: 548614, Row width:538, Density:92}
{code}

The result is that the sort must buffer two copies of the data. Since this is an 18 GB input
file, the sort must buffer (then spill) 36 GB of data. Note that there is actually no column
alias or expression on the column.

> Column or table alias doubles sort data size when reading a text file
> ---------------------------------------------------------------------
>
>                 Key: DRILL-5235
>                 URL: https://issues.apache.org/jira/browse/DRILL-5235
>             Project: Apache Drill
>          Issue Type: Improvement
>    Affects Versions: 1.9.0
>            Reporter: Paul Rogers
>            Priority: Minor
>
> Consider a simple query that reads data from a pipe-separated-value file and sorts it.
The file has just one column. The query looks something like this:
> {code}
> SELECT columns[0] col1 FROM `dfs.data`.`input-file.tbl` ORDER BY col1
> {code}
> Looking at the query plan, we see that a project operator not just creates an alias {{col1}}
for {{column\[0]}}, it also makes a *copy*.
> The particular input file is 20 GB in size and contains just one column. As a result
of materializing the alias, data size to the sort doubles to 40 GB. This results in doubling
query run time. If the sort must spill to disk, run times increases by a much larger factor.
> The fix is to treat the alias as an alias, not a materialized copy.
> {code}
> {
>   "graph" : [ {
>     "pop" : "fs-scan",
>     "columns" : [ "`columns`[0]" ],
>   }, {
>     "pop" : "project",
>     "@id" : 4,
>     "exprs" : [ {
>       "ref" : "`col1`",
>       "expr" : "`columns`[0]"
>     } ],
>   }, {
>     "pop" : "external-sort",
>     "orderings" : [ {
>       "order" : "ASC",
>       "expr" : "`col1`",
>       "nullDirection" : "UNSPECIFIED"
>     } ],
>   }, {
>     "pop" : "selection-vector-remover",
>   }, {
>     "pop" : "project",
>   }, {
>     "pop" : "screen",
>   } ]
> }
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message