From issues-return-48451-archive-asf-public=cust-asf.ponee.io@drill.apache.org Thu Jan 4 15:57:11 2018 Return-Path: X-Original-To: archive-asf-public@eu.ponee.io Delivered-To: archive-asf-public@eu.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by mx-eu-01.ponee.io (Postfix) with ESMTP id 23279180657 for ; Thu, 4 Jan 2018 15:57:11 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 12AA7160C2B; Thu, 4 Jan 2018 14:57:11 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id D03DA160C28 for ; Thu, 4 Jan 2018 15:57:08 +0100 (CET) Received: (qmail 50182 invoked by uid 500); 4 Jan 2018 14:57:08 -0000 Mailing-List: contact issues-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list issues@drill.apache.org Received: (qmail 50173 invoked by uid 99); 4 Jan 2018 14:57:08 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 04 Jan 2018 14:57:08 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 4E0DCC3978 for ; Thu, 4 Jan 2018 14:57:07 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -100.011 X-Spam-Level: X-Spam-Status: No, score=-100.011 tagged_above=-999 required=6.31 tests=[RCVD_IN_DNSWL_NONE=-0.0001, SPF_PASS=-0.001, T_RP_MATCHES_RCVD=-0.01, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-us.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id gBDbgKmRwXow for ; Thu, 4 Jan 2018 14:57:01 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-us.apache.org (ASF Mail Server at mx1-lw-us.apache.org) with ESMTP id 3BC4D5F635 for ; Thu, 4 Jan 2018 14:57:01 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 648CCE002B for ; Thu, 4 Jan 2018 14:57:00 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 126E4212F8 for ; Thu, 4 Jan 2018 14:57:00 +0000 (UTC) Date: Thu, 4 Jan 2018 14:57:00 +0000 (UTC) From: "Volodymyr Vysotskyi (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (DRILL-6069) Hash agg operator requires large memory amount when planner.width.max_per_node is large MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 Volodymyr Vysotskyi created DRILL-6069: ------------------------------------------ Summary: Hash agg operator requires large memory amount when planner.width.max_per_node is large Key: DRILL-6069 URL: https://issues.apache.org/jira/browse/DRILL-6069 Project: Apache Drill Issue Type: Bug Reporter: Volodymyr Vysotskyi Queries, whose plan contains few HashAgg operators, requires large memory amount when planner.width.max_per_node is large. It may be observed using this physical plan: {code:xml} { "head" : { "version" : 1, "generator" : { "type" : "DefaultSqlHandler", "info" : "" }, "type" : "APACHE_DRILL_PHYSICAL", "options" : [ { "kind" : "BOOLEAN", "accessibleScopes" : "ALL", "name" : "planner.enable_decimal_data_type", "bool_val" : true, "scope" : "SESSION" }, { "kind" : "LONG", "accessibleScopes" : "ALL", "name" : "exec.hashagg.min_batches_per_partition", "num_val" : 1, "scope" : "SESSION" }, { "kind" : "LONG", "accessibleScopes" : "ALL", "name" : "planner.width.max_per_node", "num_val" : 2, "scope" : "SESSION" }, { "kind" : "BOOLEAN", "accessibleScopes" : "ALL", "name" : "exec.errors.verbose", "bool_val" : true, "scope" : "SESSION" } ], "queue" : 0, "hasResourcePlan" : false, "resultMode" : "EXEC" }, "graph" : [ { "pop" : "parquet-scan", "@id" : 131093, "entries" : [ { "path" : "file:/tmp/parquet/ship_mode" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`sm_ship_mode_sk`", "`sm_carrier`" ], "selectionRoot" : "file:/tmp/parquet/ship_mode", "filter" : "true", "fileSet" : [ "/tmp/parquet/ship_mode/0_0_0.parquet" ], "cost" : 20.0 }, { "pop" : "filter", "@id" : 131090, "child" : 131093, "expr" : "booleanOr(equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZOUROS') , equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZHOU') ) ", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "selection-vector-remover", "@id" : 131088, "child" : 131090, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "project", "@id" : 131086, "exprs" : [ { "ref" : "`sm_ship_mode_sk`", "expr" : "cast( (`sm_ship_mode_sk` ) as INT )" } ], "child" : 131088, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "parquet-scan", "@id" : 131107, "entries" : [ { "path" : "file:/tmp/parquet/date_dim" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`d_date_sk`", "`d_year`", "`d_moy`" ], "selectionRoot" : "file:/tmp/parquet/date_dim", "filter" : "true", "fileSet" : [ "/tmp/parquet/date_dim/0_0_0.parquet" ], "cost" : 73049.0 }, { "pop" : "filter", "@id" : 131106, "child" : 131107, "expr" : "equal(cast( (`d_year` ) as INT ), 1998) ", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 10957.35 }, { "pop" : "selection-vector-remover", "@id" : 131104, "child" : 131106, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 10957.35 }, { "pop" : "project", "@id" : 131102, "exprs" : [ { "ref" : "`d_date_sk`", "expr" : "cast( (`d_date_sk` ) as INT )" }, { "ref" : "`d_year`", "expr" : "cast( (`d_year` ) as INT )" }, { "ref" : "`d_moy`", "expr" : "cast( (`d_moy` ) as INT )" } ], "child" : 131104, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 10957.35 }, { "pop" : "parquet-scan", "@id" : 524292, "entries" : [ { "path" : "file:/tmp/parquet/ship_mode" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`sm_ship_mode_sk`", "`sm_carrier`" ], "selectionRoot" : "file:/tmp/parquet/ship_mode", "filter" : "true", "fileSet" : [ "/tmp/parquet/ship_mode/0_0_0.parquet" ], "cost" : 20.0 }, { "pop" : "filter", "@id" : 524291, "child" : 524292, "expr" : "booleanOr(equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZOUROS') , equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZHOU') ) ", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "selection-vector-remover", "@id" : 524290, "child" : 524291, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "project", "@id" : 524289, "exprs" : [ { "ref" : "`sm_ship_mode_sk`", "expr" : "cast( (`sm_ship_mode_sk` ) as INT )" } ], "child" : 524290, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "broadcast-exchange", "@id" : 262149, "child" : 524289, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "parquet-scan", "@id" : 131101, "entries" : [ { "path" : "file:/tmp/parquet/warehouse" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`w_warehouse_sk`", "`w_warehouse_name`", "`w_warehouse_sq_ft`", "`w_city`", "`w_county`", "`w_state`", "`w_country`" ], "selectionRoot" : "file:/tmp/parquet/warehouse", "filter" : "true", "fileSet" : [ "/tmp/parquet/warehouse/0_0_0.parquet" ], "cost" : 5.0 }, { "pop" : "project", "@id" : 131099, "exprs" : [ { "ref" : "`w_warehouse_sk`", "expr" : "cast( (`w_warehouse_sk` ) as INT )" }, { "ref" : "`w_warehouse_name`", "expr" : "cast( (`w_warehouse_name` ) as VARCHAR(200) )" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "cast( (`w_warehouse_sq_ft` ) as INT )" }, { "ref" : "`w_city`", "expr" : "cast( (`w_city` ) as VARCHAR(200) )" }, { "ref" : "`w_county`", "expr" : "cast( (`w_county` ) as VARCHAR(200) )" }, { "ref" : "`w_state`", "expr" : "cast( (`w_state` ) as VARCHAR(200) )" }, { "ref" : "`w_country`", "expr" : "cast( (`w_country` ) as VARCHAR(200) )" } ], "child" : 131101, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "parquet-scan", "@id" : 458756, "entries" : [ { "path" : "file:/tmp/parquet/time_dim" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`t_time_sk`", "`t_time`" ], "selectionRoot" : "file:/tmp/parquet/time_dim", "filter" : "true", "fileSet" : [ "/tmp/parquet/time_dim/0_0_0.parquet" ], "cost" : 86400.0 }, { "pop" : "filter", "@id" : 458755, "child" : 458756, "expr" : "booleanAnd(greater_than_or_equal_to(cast( (`t_time` ) as INT ), 7249) , less_than_or_equal_to(cast( (`t_time` ) as INT ), 36049) ) ", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21600.0 }, { "pop" : "selection-vector-remover", "@id" : 458754, "child" : 458755, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21600.0 }, { "pop" : "project", "@id" : 458753, "exprs" : [ { "ref" : "`t_time_sk`", "expr" : "cast( (`t_time_sk` ) as INT )" } ], "child" : 458754, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21600.0 }, { "pop" : "broadcast-exchange", "@id" : 262152, "child" : 458753, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21600.0 }, { "pop" : "parquet-scan", "@id" : 131098, "entries" : [ { "path" : "file:/tmp/parquet/time_dim" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`t_time_sk`", "`t_time`" ], "selectionRoot" : "file:/tmp/parquet/time_dim", "filter" : "true", "fileSet" : [ "/tmp/parquet/time_dim/0_0_0.parquet" ], "cost" : 86400.0 }, { "pop" : "filter", "@id" : 131096, "child" : 131098, "expr" : "booleanAnd(greater_than_or_equal_to(cast( (`t_time` ) as INT ), 7249) , less_than_or_equal_to(cast( (`t_time` ) as INT ), 36049) ) ", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21600.0 }, { "pop" : "selection-vector-remover", "@id" : 131094, "child" : 131096, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21600.0 }, { "pop" : "project", "@id" : 131091, "exprs" : [ { "ref" : "`t_time_sk`", "expr" : "cast( (`t_time_sk` ) as INT )" } ], "child" : 131094, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21600.0 }, { "pop" : "parquet-scan", "@id" : 262160, "entries" : [ { "path" : "file:/tmp/parquet/catalog_sales" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`cs_sold_date_sk`", "`cs_sold_time_sk`", "`cs_ship_mode_sk`", "`cs_warehouse_sk`", "`cs_quantity`", "`cs_ext_sales_price`", "`cs_net_paid`" ], "selectionRoot" : "file:/tmp/parquet/catalog_sales", "filter" : "true", "fileSet" : [ "/tmp/parquet/catalog_sales/1_1_0.parquet", "/tmp/parquet/catalog_sales/1_0_0.parquet" ], "cost" : 1441548.0 }, { "pop" : "project", "@id" : 262159, "exprs" : [ { "ref" : "`cs_sold_date_sk`", "expr" : "cast( (`cs_sold_date_sk` ) as INT )" }, { "ref" : "`cs_sold_time_sk`", "expr" : "cast( (`cs_sold_time_sk` ) as INT )" }, { "ref" : "`cs_ship_mode_sk`", "expr" : "cast( (`cs_ship_mode_sk` ) as INT )" }, { "ref" : "`cs_warehouse_sk`", "expr" : "cast( (`cs_warehouse_sk` ) as INT )" }, { "ref" : "`cs_quantity`", "expr" : "cast( (`cs_quantity` ) as INT )" }, { "ref" : "`cs_ext_sales_price`", "expr" : "cast( (`cs_ext_sales_price` ) as FLOAT8 )" }, { "ref" : "`cs_net_paid`", "expr" : "cast( (`cs_net_paid` ) as FLOAT8 )" } ], "child" : 262160, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "parquet-scan", "@id" : 327684, "entries" : [ { "path" : "file:/tmp/parquet/date_dim" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`d_date_sk`", "`d_year`", "`d_moy`" ], "selectionRoot" : "file:/tmp/parquet/date_dim", "filter" : "true", "fileSet" : [ "/tmp/parquet/date_dim/0_0_0.parquet" ], "cost" : 73049.0 }, { "pop" : "filter", "@id" : 327683, "child" : 327684, "expr" : "equal(cast( (`d_year` ) as INT ), 1998) ", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 10957.35 }, { "pop" : "selection-vector-remover", "@id" : 327682, "child" : 327683, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 10957.35 }, { "pop" : "project", "@id" : 327681, "exprs" : [ { "ref" : "`d_date_sk`", "expr" : "cast( (`d_date_sk` ) as INT )" }, { "ref" : "`d_year`", "expr" : "cast( (`d_year` ) as INT )" }, { "ref" : "`d_moy`", "expr" : "cast( (`d_moy` ) as INT )" } ], "child" : 327682, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 10957.35 }, { "pop" : "broadcast-exchange", "@id" : 262158, "child" : 327681, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 10957.35 }, { "pop" : "hash-join", "@id" : 262157, "left" : 262159, "right" : 262158, "conditions" : [ { "relationship" : "EQUALS", "left" : "`cs_sold_date_sk`", "right" : "`d_date_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "parquet-scan", "@id" : 131105, "entries" : [ { "path" : "file:/tmp/parquet/web_sales" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`ws_sold_date_sk`", "`ws_sold_time_sk`", "`ws_ship_mode_sk`", "`ws_warehouse_sk`", "`ws_quantity`", "`ws_ext_sales_price`", "`ws_net_paid_inc_ship`" ], "selectionRoot" : "file:/tmp/parquet/web_sales", "filter" : "true", "fileSet" : [ "/tmp/parquet/web_sales/0_0_0.parquet" ], "cost" : 719384.0 }, { "pop" : "project", "@id" : 131103, "exprs" : [ { "ref" : "`ws_sold_date_sk`", "expr" : "cast( (`ws_sold_date_sk` ) as INT )" }, { "ref" : "`ws_sold_time_sk`", "expr" : "cast( (`ws_sold_time_sk` ) as INT )" }, { "ref" : "`ws_ship_mode_sk`", "expr" : "cast( (`ws_ship_mode_sk` ) as INT )" }, { "ref" : "`ws_warehouse_sk`", "expr" : "cast( (`ws_warehouse_sk` ) as INT )" }, { "ref" : "`ws_quantity`", "expr" : "cast( (`ws_quantity` ) as INT )" }, { "ref" : "`ws_ext_sales_price`", "expr" : "cast( (`ws_ext_sales_price` ) as FLOAT8 )" }, { "ref" : "`ws_net_paid_inc_ship`", "expr" : "cast( (`ws_net_paid_inc_ship` ) as FLOAT8 )" } ], "child" : 131105, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "hash-join", "@id" : 131100, "left" : 131103, "right" : 131102, "conditions" : [ { "relationship" : "EQUALS", "left" : "`ws_sold_date_sk`", "right" : "`d_date_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "hash-join", "@id" : 131097, "left" : 131100, "right" : 131099, "conditions" : [ { "relationship" : "EQUALS", "left" : "`ws_warehouse_sk`", "right" : "`w_warehouse_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "project", "@id" : 131095, "exprs" : [ { "ref" : "`ws_sold_date_sk`", "expr" : "`ws_sold_date_sk`" }, { "ref" : "`ws_sold_time_sk`", "expr" : "`ws_sold_time_sk`" }, { "ref" : "`ws_ship_mode_sk`", "expr" : "`ws_ship_mode_sk`" }, { "ref" : "`ws_warehouse_sk`", "expr" : "`ws_warehouse_sk`" }, { "ref" : "`ws_quantity`", "expr" : "`ws_quantity`" }, { "ref" : "`ws_ext_sales_price`", "expr" : "`ws_ext_sales_price`" }, { "ref" : "`ws_net_paid_inc_ship`", "expr" : "`ws_net_paid_inc_ship`" }, { "ref" : "`w_warehouse_sk`", "expr" : "`w_warehouse_sk`" }, { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`d_date_sk`", "expr" : "`d_date_sk`" }, { "ref" : "`d_year`", "expr" : "`d_year`" }, { "ref" : "`d_moy`", "expr" : "`d_moy`" } ], "child" : 131097, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "project", "@id" : 131092, "exprs" : [ { "ref" : "`ws_sold_time_sk`", "expr" : "`ws_sold_time_sk`" }, { "ref" : "`ws_ship_mode_sk`", "expr" : "`ws_ship_mode_sk`" }, { "ref" : "`ws_quantity`", "expr" : "`ws_quantity`" }, { "ref" : "`ws_ext_sales_price`", "expr" : "`ws_ext_sales_price`" }, { "ref" : "`ws_net_paid_inc_ship`", "expr" : "`ws_net_paid_inc_ship`" }, { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`d_year`", "expr" : "`d_year`" }, { "ref" : "`d_moy`", "expr" : "`d_moy`" } ], "child" : 131095, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "hash-join", "@id" : 131089, "left" : 131092, "right" : 131091, "conditions" : [ { "relationship" : "EQUALS", "left" : "`ws_sold_time_sk`", "right" : "`t_time_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "project", "@id" : 131087, "exprs" : [ { "ref" : "`ws_ship_mode_sk`", "expr" : "`ws_ship_mode_sk`" }, { "ref" : "`ws_quantity`", "expr" : "`ws_quantity`" }, { "ref" : "`ws_ext_sales_price`", "expr" : "`ws_ext_sales_price`" }, { "ref" : "`ws_net_paid_inc_ship`", "expr" : "`ws_net_paid_inc_ship`" }, { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`d_year`", "expr" : "`d_year`" }, { "ref" : "`d_moy`", "expr" : "`d_moy`" } ], "child" : 131089, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "hash-join", "@id" : 131085, "left" : 131087, "right" : 131086, "conditions" : [ { "relationship" : "EQUALS", "left" : "`ws_ship_mode_sk`", "right" : "`sm_ship_mode_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "project", "@id" : 131084, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`d_year`" }, { "ref" : "`$f7`", "expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f8`", "expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f9`", "expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f10`", "expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f11`", "expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f12`", "expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f13`", "expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f14`", "expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f15`", "expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f16`", "expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f17`", "expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f18`", "expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f19`", "expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f20`", "expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f21`", "expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f22`", "expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f23`", "expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f24`", "expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f25`", "expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f26`", "expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f27`", "expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f28`", "expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f29`", "expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f30`", "expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) " } ], "child" : 131085, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 719384.0 }, { "pop" : "hash-aggregate", "@id" : 131082, "child" : 131084, "cardinality" : 1.0, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "aggPhase" : "PHASE_1of2", "groupByExprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`year1`" } ], "aggrExprs" : [ { "ref" : "`jan_sales`", "expr" : "sum(`$f7`) " }, { "ref" : "`feb_sales`", "expr" : "sum(`$f8`) " }, { "ref" : "`mar_sales`", "expr" : "sum(`$f9`) " }, { "ref" : "`apr_sales`", "expr" : "sum(`$f10`) " }, { "ref" : "`may_sales`", "expr" : "sum(`$f11`) " }, { "ref" : "`jun_sales`", "expr" : "sum(`$f12`) " }, { "ref" : "`jul_sales`", "expr" : "sum(`$f13`) " }, { "ref" : "`aug_sales`", "expr" : "sum(`$f14`) " }, { "ref" : "`sep_sales`", "expr" : "sum(`$f15`) " }, { "ref" : "`oct_sales`", "expr" : "sum(`$f16`) " }, { "ref" : "`nov_sales`", "expr" : "sum(`$f17`) " }, { "ref" : "`dec_sales`", "expr" : "sum(`$f18`) " }, { "ref" : "`jan_net`", "expr" : "sum(`$f19`) " }, { "ref" : "`feb_net`", "expr" : "sum(`$f20`) " }, { "ref" : "`mar_net`", "expr" : "sum(`$f21`) " }, { "ref" : "`apr_net`", "expr" : "sum(`$f22`) " }, { "ref" : "`may_net`", "expr" : "sum(`$f23`) " }, { "ref" : "`jun_net`", "expr" : "sum(`$f24`) " }, { "ref" : "`jul_net`", "expr" : "sum(`$f25`) " }, { "ref" : "`aug_net`", "expr" : "sum(`$f26`) " }, { "ref" : "`sep_net`", "expr" : "sum(`$f27`) " }, { "ref" : "`oct_net`", "expr" : "sum(`$f28`) " }, { "ref" : "`nov_net`", "expr" : "sum(`$f29`) " }, { "ref" : "`dec_net`", "expr" : "sum(`$f30`) " } ], "cost" : 713763.8125 }, { "pop" : "hash-aggregate", "@id" : 131080, "child" : 131082, "cardinality" : 1.0, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "aggPhase" : "PHASE_2of2", "groupByExprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`year1`" } ], "aggrExprs" : [ { "ref" : "`jan_sales`", "expr" : "sum(`jan_sales`) " }, { "ref" : "`feb_sales`", "expr" : "sum(`feb_sales`) " }, { "ref" : "`mar_sales`", "expr" : "sum(`mar_sales`) " }, { "ref" : "`apr_sales`", "expr" : "sum(`apr_sales`) " }, { "ref" : "`may_sales`", "expr" : "sum(`may_sales`) " }, { "ref" : "`jun_sales`", "expr" : "sum(`jun_sales`) " }, { "ref" : "`jul_sales`", "expr" : "sum(`jul_sales`) " }, { "ref" : "`aug_sales`", "expr" : "sum(`aug_sales`) " }, { "ref" : "`sep_sales`", "expr" : "sum(`sep_sales`) " }, { "ref" : "`oct_sales`", "expr" : "sum(`oct_sales`) " }, { "ref" : "`nov_sales`", "expr" : "sum(`nov_sales`) " }, { "ref" : "`dec_sales`", "expr" : "sum(`dec_sales`) " }, { "ref" : "`jan_net`", "expr" : "sum(`jan_net`) " }, { "ref" : "`feb_net`", "expr" : "sum(`feb_net`) " }, { "ref" : "`mar_net`", "expr" : "sum(`mar_net`) " }, { "ref" : "`apr_net`", "expr" : "sum(`apr_net`) " }, { "ref" : "`may_net`", "expr" : "sum(`may_net`) " }, { "ref" : "`jun_net`", "expr" : "sum(`jun_net`) " }, { "ref" : "`jul_net`", "expr" : "sum(`jul_net`) " }, { "ref" : "`aug_net`", "expr" : "sum(`aug_net`) " }, { "ref" : "`sep_net`", "expr" : "sum(`sep_net`) " }, { "ref" : "`oct_net`", "expr" : "sum(`oct_net`) " }, { "ref" : "`nov_net`", "expr" : "sum(`nov_net`) " }, { "ref" : "`dec_net`", "expr" : "sum(`dec_net`) " } ], "cost" : 71376.38124999999 }, { "pop" : "project", "@id" : 131078, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "concatOperator(concatOperator('ZOUROS', ',') , 'ZHOU') " }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" } ], "child" : 131080, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 7193.839999999999 }, { "pop" : "parquet-scan", "@id" : 393218, "entries" : [ { "path" : "file:/tmp/parquet/warehouse" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "file:///", "config" : null, "workspaces" : { "root" : { "location" : "/", "writable" : false, "defaultInputFormat" : null }, "tmp" : { "location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0", "writable" : true, "defaultInputFormat" : null } }, "formats" : { "psv" : { "type" : "text", "extensions" : [ "tbl" ], "delimiter" : "|" }, "csv" : { "type" : "text", "extensions" : [ "csv" ], "delimiter" : "," }, "tsv" : { "type" : "text", "extensions" : [ "tsv" ], "delimiter" : "\t" }, "httpd" : { "type" : "httpd", "logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\"" }, "parquet" : { "type" : "parquet" }, "json" : { "type" : "json", "extensions" : [ "json" ] }, "pcap" : { "type" : "pcap" }, "avro" : { "type" : "avro" }, "sequencefile" : { "type" : "sequencefile", "extensions" : [ "seq" ] }, "csvh" : { "type" : "text", "extensions" : [ "csvh" ], "extractHeader" : true, "delimiter" : "," }, "txt" : { "type" : "text", "extensions" : [ "txt" ], "delimiter" : "\u0000" }, "ssv" : { "type" : "text", "extensions" : [ "ssv" ], "delimiter" : " " }, "csvh-test" : { "type" : "text", "extensions" : [ "csvh-test" ], "skipFirstLine" : true, "extractHeader" : true, "delimiter" : "," } } }, "format" : { "type" : "parquet" }, "columns" : [ "`w_warehouse_sk`", "`w_warehouse_name`", "`w_warehouse_sq_ft`", "`w_city`", "`w_county`", "`w_state`", "`w_country`" ], "selectionRoot" : "file:/tmp/parquet/warehouse", "filter" : "true", "fileSet" : [ "/tmp/parquet/warehouse/0_0_0.parquet" ], "cost" : 5.0 }, { "pop" : "project", "@id" : 393217, "exprs" : [ { "ref" : "`w_warehouse_sk`", "expr" : "cast( (`w_warehouse_sk` ) as INT )" }, { "ref" : "`w_warehouse_name`", "expr" : "cast( (`w_warehouse_name` ) as VARCHAR(200) )" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "cast( (`w_warehouse_sq_ft` ) as INT )" }, { "ref" : "`w_city`", "expr" : "cast( (`w_city` ) as VARCHAR(200) )" }, { "ref" : "`w_county`", "expr" : "cast( (`w_county` ) as VARCHAR(200) )" }, { "ref" : "`w_state`", "expr" : "cast( (`w_state` ) as VARCHAR(200) )" }, { "ref" : "`w_country`", "expr" : "cast( (`w_country` ) as VARCHAR(200) )" } ], "child" : 393218, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "broadcast-exchange", "@id" : 262156, "child" : 393217, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 5.0 }, { "pop" : "hash-join", "@id" : 262155, "left" : 262157, "right" : 262156, "conditions" : [ { "relationship" : "EQUALS", "left" : "`cs_warehouse_sk`", "right" : "`w_warehouse_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "project", "@id" : 262154, "exprs" : [ { "ref" : "`cs_sold_date_sk`", "expr" : "`cs_sold_date_sk`" }, { "ref" : "`cs_sold_time_sk`", "expr" : "`cs_sold_time_sk`" }, { "ref" : "`cs_ship_mode_sk`", "expr" : "`cs_ship_mode_sk`" }, { "ref" : "`cs_warehouse_sk`", "expr" : "`cs_warehouse_sk`" }, { "ref" : "`cs_quantity`", "expr" : "`cs_quantity`" }, { "ref" : "`cs_ext_sales_price`", "expr" : "`cs_ext_sales_price`" }, { "ref" : "`cs_net_paid`", "expr" : "`cs_net_paid`" }, { "ref" : "`w_warehouse_sk`", "expr" : "`w_warehouse_sk`" }, { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`d_date_sk`", "expr" : "`d_date_sk`" }, { "ref" : "`d_year`", "expr" : "`d_year`" }, { "ref" : "`d_moy`", "expr" : "`d_moy`" } ], "child" : 262155, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "project", "@id" : 262153, "exprs" : [ { "ref" : "`cs_sold_time_sk`", "expr" : "`cs_sold_time_sk`" }, { "ref" : "`cs_ship_mode_sk`", "expr" : "`cs_ship_mode_sk`" }, { "ref" : "`cs_quantity`", "expr" : "`cs_quantity`" }, { "ref" : "`cs_ext_sales_price`", "expr" : "`cs_ext_sales_price`" }, { "ref" : "`cs_net_paid`", "expr" : "`cs_net_paid`" }, { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`d_year`", "expr" : "`d_year`" }, { "ref" : "`d_moy`", "expr" : "`d_moy`" } ], "child" : 262154, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "hash-join", "@id" : 262151, "left" : 262153, "right" : 262152, "conditions" : [ { "relationship" : "EQUALS", "left" : "`cs_sold_time_sk`", "right" : "`t_time_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "project", "@id" : 262150, "exprs" : [ { "ref" : "`cs_ship_mode_sk`", "expr" : "`cs_ship_mode_sk`" }, { "ref" : "`cs_quantity`", "expr" : "`cs_quantity`" }, { "ref" : "`cs_ext_sales_price`", "expr" : "`cs_ext_sales_price`" }, { "ref" : "`cs_net_paid`", "expr" : "`cs_net_paid`" }, { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`d_year`", "expr" : "`d_year`" }, { "ref" : "`d_moy`", "expr" : "`d_moy`" } ], "child" : 262151, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "hash-join", "@id" : 262148, "left" : 262150, "right" : 262149, "conditions" : [ { "relationship" : "EQUALS", "left" : "`cs_ship_mode_sk`", "right" : "`sm_ship_mode_sk`" } ], "joinType" : "INNER", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "project", "@id" : 262147, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`d_year`" }, { "ref" : "`$f7`", "expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f8`", "expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f9`", "expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f10`", "expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f11`", "expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f12`", "expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f13`", "expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f14`", "expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f15`", "expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f16`", "expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f17`", "expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f18`", "expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f19`", "expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f20`", "expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f21`", "expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f22`", "expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f23`", "expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f24`", "expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f25`", "expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f26`", "expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f27`", "expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f28`", "expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f29`", "expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " }, { "ref" : "`$f30`", "expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) " } ], "child" : 262148, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1441548.0 }, { "pop" : "hash-aggregate", "@id" : 262146, "child" : 262147, "cardinality" : 1.0, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "aggPhase" : "PHASE_1of2", "groupByExprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`year1`" } ], "aggrExprs" : [ { "ref" : "`jan_sales`", "expr" : "sum(`$f7`) " }, { "ref" : "`feb_sales`", "expr" : "sum(`$f8`) " }, { "ref" : "`mar_sales`", "expr" : "sum(`$f9`) " }, { "ref" : "`apr_sales`", "expr" : "sum(`$f10`) " }, { "ref" : "`may_sales`", "expr" : "sum(`$f11`) " }, { "ref" : "`jun_sales`", "expr" : "sum(`$f12`) " }, { "ref" : "`jul_sales`", "expr" : "sum(`$f13`) " }, { "ref" : "`aug_sales`", "expr" : "sum(`$f14`) " }, { "ref" : "`sep_sales`", "expr" : "sum(`$f15`) " }, { "ref" : "`oct_sales`", "expr" : "sum(`$f16`) " }, { "ref" : "`nov_sales`", "expr" : "sum(`$f17`) " }, { "ref" : "`dec_sales`", "expr" : "sum(`$f18`) " }, { "ref" : "`jan_net`", "expr" : "sum(`$f19`) " }, { "ref" : "`feb_net`", "expr" : "sum(`$f20`) " }, { "ref" : "`mar_net`", "expr" : "sum(`$f21`) " }, { "ref" : "`apr_net`", "expr" : "sum(`$f22`) " }, { "ref" : "`may_net`", "expr" : "sum(`$f23`) " }, { "ref" : "`jun_net`", "expr" : "sum(`$f24`) " }, { "ref" : "`jul_net`", "expr" : "sum(`$f25`) " }, { "ref" : "`aug_net`", "expr" : "sum(`$f26`) " }, { "ref" : "`sep_net`", "expr" : "sum(`$f27`) " }, { "ref" : "`oct_net`", "expr" : "sum(`$f28`) " }, { "ref" : "`nov_net`", "expr" : "sum(`$f29`) " }, { "ref" : "`dec_net`", "expr" : "sum(`$f30`) " } ], "cost" : 1430285.90625 }, { "pop" : "project", "@id" : 262145, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" }, { "ref" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`", "expr" : "hash32asdouble(`year1`, hash32asdouble(`w_country`, hash32asdouble(`w_state`, hash32asdouble(`w_county`, hash32asdouble(`w_city`, hash32asdouble(`w_warehouse_sq_ft`, hash32asdouble(`w_warehouse_name`, 1301011) ) ) ) ) ) ) " } ], "child" : 262146, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 144154.8 }, { "pop" : "unordered-mux-exchange", "@id" : 196609, "child" : 262145, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 144154.8 }, { "pop" : "hash-to-random-exchange", "@id" : 131083, "child" : 196609, "expr" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 144154.8 }, { "pop" : "project", "@id" : 131081, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" } ], "child" : 131083, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 144154.8 }, { "pop" : "hash-aggregate", "@id" : 131079, "child" : 131081, "cardinality" : 1.0, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "aggPhase" : "PHASE_2of2", "groupByExprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`year1`", "expr" : "`year1`" } ], "aggrExprs" : [ { "ref" : "`jan_sales`", "expr" : "sum(`jan_sales`) " }, { "ref" : "`feb_sales`", "expr" : "sum(`feb_sales`) " }, { "ref" : "`mar_sales`", "expr" : "sum(`mar_sales`) " }, { "ref" : "`apr_sales`", "expr" : "sum(`apr_sales`) " }, { "ref" : "`may_sales`", "expr" : "sum(`may_sales`) " }, { "ref" : "`jun_sales`", "expr" : "sum(`jun_sales`) " }, { "ref" : "`jul_sales`", "expr" : "sum(`jul_sales`) " }, { "ref" : "`aug_sales`", "expr" : "sum(`aug_sales`) " }, { "ref" : "`sep_sales`", "expr" : "sum(`sep_sales`) " }, { "ref" : "`oct_sales`", "expr" : "sum(`oct_sales`) " }, { "ref" : "`nov_sales`", "expr" : "sum(`nov_sales`) " }, { "ref" : "`dec_sales`", "expr" : "sum(`dec_sales`) " }, { "ref" : "`jan_net`", "expr" : "sum(`jan_net`) " }, { "ref" : "`feb_net`", "expr" : "sum(`feb_net`) " }, { "ref" : "`mar_net`", "expr" : "sum(`mar_net`) " }, { "ref" : "`apr_net`", "expr" : "sum(`apr_net`) " }, { "ref" : "`may_net`", "expr" : "sum(`may_net`) " }, { "ref" : "`jun_net`", "expr" : "sum(`jun_net`) " }, { "ref" : "`jul_net`", "expr" : "sum(`jul_net`) " }, { "ref" : "`aug_net`", "expr" : "sum(`aug_net`) " }, { "ref" : "`sep_net`", "expr" : "sum(`sep_net`) " }, { "ref" : "`oct_net`", "expr" : "sum(`oct_net`) " }, { "ref" : "`nov_net`", "expr" : "sum(`nov_net`) " }, { "ref" : "`dec_net`", "expr" : "sum(`dec_net`) " } ], "cost" : 143028.59062499998 }, { "pop" : "project", "@id" : 131077, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "concatOperator(concatOperator('ZOUROS', ',') , 'ZHOU') " }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" } ], "child" : 131079, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 14415.48 }, { "pop" : "union-all", "@id" : 131076, "children" : [ 131078, 131077 ], "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21609.32 }, { "pop" : "project", "@id" : 131075, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "`ship_carriers`" }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`$f20`", "expr" : "divide(`jan_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f21`", "expr" : "divide(`feb_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f22`", "expr" : "divide(`mar_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f23`", "expr" : "divide(`apr_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f24`", "expr" : "divide(`may_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f25`", "expr" : "divide(`jun_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f26`", "expr" : "divide(`jul_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f27`", "expr" : "divide(`aug_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f28`", "expr" : "divide(`sep_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f29`", "expr" : "divide(`oct_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f30`", "expr" : "divide(`nov_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`$f31`", "expr" : "divide(`dec_sales`, `w_warehouse_sq_ft`) " }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" } ], "child" : 131076, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 21609.32 }, { "pop" : "hash-aggregate", "@id" : 131074, "child" : 131075, "cardinality" : 1.0, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "aggPhase" : "PHASE_1of2", "groupByExprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "`ship_carriers`" }, { "ref" : "`year1`", "expr" : "`year1`" } ], "aggrExprs" : [ { "ref" : "`jan_sales`", "expr" : "sum(`jan_sales`) " }, { "ref" : "`feb_sales`", "expr" : "sum(`feb_sales`) " }, { "ref" : "`mar_sales`", "expr" : "sum(`mar_sales`) " }, { "ref" : "`apr_sales`", "expr" : "sum(`apr_sales`) " }, { "ref" : "`may_sales`", "expr" : "sum(`may_sales`) " }, { "ref" : "`jun_sales`", "expr" : "sum(`jun_sales`) " }, { "ref" : "`jul_sales`", "expr" : "sum(`jul_sales`) " }, { "ref" : "`aug_sales`", "expr" : "sum(`aug_sales`) " }, { "ref" : "`sep_sales`", "expr" : "sum(`sep_sales`) " }, { "ref" : "`oct_sales`", "expr" : "sum(`oct_sales`) " }, { "ref" : "`nov_sales`", "expr" : "sum(`nov_sales`) " }, { "ref" : "`dec_sales`", "expr" : "sum(`dec_sales`) " }, { "ref" : "`jan_sales_per_sq_foot`", "expr" : "sum(`$f20`) " }, { "ref" : "`feb_sales_per_sq_foot`", "expr" : "sum(`$f21`) " }, { "ref" : "`mar_sales_per_sq_foot`", "expr" : "sum(`$f22`) " }, { "ref" : "`apr_sales_per_sq_foot`", "expr" : "sum(`$f23`) " }, { "ref" : "`may_sales_per_sq_foot`", "expr" : "sum(`$f24`) " }, { "ref" : "`jun_sales_per_sq_foot`", "expr" : "sum(`$f25`) " }, { "ref" : "`jul_sales_per_sq_foot`", "expr" : "sum(`$f26`) " }, { "ref" : "`aug_sales_per_sq_foot`", "expr" : "sum(`$f27`) " }, { "ref" : "`sep_sales_per_sq_foot`", "expr" : "sum(`$f28`) " }, { "ref" : "`oct_sales_per_sq_foot`", "expr" : "sum(`$f29`) " }, { "ref" : "`nov_sales_per_sq_foot`", "expr" : "sum(`$f30`) " }, { "ref" : "`dec_sales_per_sq_foot`", "expr" : "sum(`$f31`) " }, { "ref" : "`jan_net`", "expr" : "sum(`jan_net`) " }, { "ref" : "`feb_net`", "expr" : "sum(`feb_net`) " }, { "ref" : "`mar_net`", "expr" : "sum(`mar_net`) " }, { "ref" : "`apr_net`", "expr" : "sum(`apr_net`) " }, { "ref" : "`may_net`", "expr" : "sum(`may_net`) " }, { "ref" : "`jun_net`", "expr" : "sum(`jun_net`) " }, { "ref" : "`jul_net`", "expr" : "sum(`jul_net`) " }, { "ref" : "`aug_net`", "expr" : "sum(`aug_net`) " }, { "ref" : "`sep_net`", "expr" : "sum(`sep_net`) " }, { "ref" : "`oct_net`", "expr" : "sum(`oct_net`) " }, { "ref" : "`nov_net`", "expr" : "sum(`nov_net`) " }, { "ref" : "`dec_net`", "expr" : "sum(`dec_net`) " } ], "cost" : 21524.90859375 }, { "pop" : "project", "@id" : 131073, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "`ship_carriers`" }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`jan_sales_per_sq_foot`", "expr" : "`jan_sales_per_sq_foot`" }, { "ref" : "`feb_sales_per_sq_foot`", "expr" : "`feb_sales_per_sq_foot`" }, { "ref" : "`mar_sales_per_sq_foot`", "expr" : "`mar_sales_per_sq_foot`" }, { "ref" : "`apr_sales_per_sq_foot`", "expr" : "`apr_sales_per_sq_foot`" }, { "ref" : "`may_sales_per_sq_foot`", "expr" : "`may_sales_per_sq_foot`" }, { "ref" : "`jun_sales_per_sq_foot`", "expr" : "`jun_sales_per_sq_foot`" }, { "ref" : "`jul_sales_per_sq_foot`", "expr" : "`jul_sales_per_sq_foot`" }, { "ref" : "`aug_sales_per_sq_foot`", "expr" : "`aug_sales_per_sq_foot`" }, { "ref" : "`sep_sales_per_sq_foot`", "expr" : "`sep_sales_per_sq_foot`" }, { "ref" : "`oct_sales_per_sq_foot`", "expr" : "`oct_sales_per_sq_foot`" }, { "ref" : "`nov_sales_per_sq_foot`", "expr" : "`nov_sales_per_sq_foot`" }, { "ref" : "`dec_sales_per_sq_foot`", "expr" : "`dec_sales_per_sq_foot`" }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" }, { "ref" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`", "expr" : "hash32asdouble(`year1`, hash32asdouble(`ship_carriers`, hash32asdouble(`w_country`, hash32asdouble(`w_state`, hash32asdouble(`w_county`, hash32asdouble(`w_city`, hash32asdouble(`w_warehouse_sq_ft`, hash32asdouble(`w_warehouse_name`, 1301011) ) ) ) ) ) ) ) " } ], "child" : 131074, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 2160.932 }, { "pop" : "unordered-mux-exchange", "@id" : 65537, "child" : 131073, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 2160.932 }, { "pop" : "hash-to-random-exchange", "@id" : 8, "child" : 65537, "expr" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 2160.932 }, { "pop" : "project", "@id" : 7, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "`ship_carriers`" }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`jan_sales_per_sq_foot`", "expr" : "`jan_sales_per_sq_foot`" }, { "ref" : "`feb_sales_per_sq_foot`", "expr" : "`feb_sales_per_sq_foot`" }, { "ref" : "`mar_sales_per_sq_foot`", "expr" : "`mar_sales_per_sq_foot`" }, { "ref" : "`apr_sales_per_sq_foot`", "expr" : "`apr_sales_per_sq_foot`" }, { "ref" : "`may_sales_per_sq_foot`", "expr" : "`may_sales_per_sq_foot`" }, { "ref" : "`jun_sales_per_sq_foot`", "expr" : "`jun_sales_per_sq_foot`" }, { "ref" : "`jul_sales_per_sq_foot`", "expr" : "`jul_sales_per_sq_foot`" }, { "ref" : "`aug_sales_per_sq_foot`", "expr" : "`aug_sales_per_sq_foot`" }, { "ref" : "`sep_sales_per_sq_foot`", "expr" : "`sep_sales_per_sq_foot`" }, { "ref" : "`oct_sales_per_sq_foot`", "expr" : "`oct_sales_per_sq_foot`" }, { "ref" : "`nov_sales_per_sq_foot`", "expr" : "`nov_sales_per_sq_foot`" }, { "ref" : "`dec_sales_per_sq_foot`", "expr" : "`dec_sales_per_sq_foot`" }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" } ], "child" : 8, "outputProj" : false, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 2160.932 }, { "pop" : "hash-aggregate", "@id" : 6, "child" : 7, "cardinality" : 1.0, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "aggPhase" : "PHASE_2of2", "groupByExprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "`ship_carriers`" }, { "ref" : "`year1`", "expr" : "`year1`" } ], "aggrExprs" : [ { "ref" : "`jan_sales`", "expr" : "sum(`jan_sales`) " }, { "ref" : "`feb_sales`", "expr" : "sum(`feb_sales`) " }, { "ref" : "`mar_sales`", "expr" : "sum(`mar_sales`) " }, { "ref" : "`apr_sales`", "expr" : "sum(`apr_sales`) " }, { "ref" : "`may_sales`", "expr" : "sum(`may_sales`) " }, { "ref" : "`jun_sales`", "expr" : "sum(`jun_sales`) " }, { "ref" : "`jul_sales`", "expr" : "sum(`jul_sales`) " }, { "ref" : "`aug_sales`", "expr" : "sum(`aug_sales`) " }, { "ref" : "`sep_sales`", "expr" : "sum(`sep_sales`) " }, { "ref" : "`oct_sales`", "expr" : "sum(`oct_sales`) " }, { "ref" : "`nov_sales`", "expr" : "sum(`nov_sales`) " }, { "ref" : "`dec_sales`", "expr" : "sum(`dec_sales`) " }, { "ref" : "`jan_sales_per_sq_foot`", "expr" : "sum(`jan_sales_per_sq_foot`) " }, { "ref" : "`feb_sales_per_sq_foot`", "expr" : "sum(`feb_sales_per_sq_foot`) " }, { "ref" : "`mar_sales_per_sq_foot`", "expr" : "sum(`mar_sales_per_sq_foot`) " }, { "ref" : "`apr_sales_per_sq_foot`", "expr" : "sum(`apr_sales_per_sq_foot`) " }, { "ref" : "`may_sales_per_sq_foot`", "expr" : "sum(`may_sales_per_sq_foot`) " }, { "ref" : "`jun_sales_per_sq_foot`", "expr" : "sum(`jun_sales_per_sq_foot`) " }, { "ref" : "`jul_sales_per_sq_foot`", "expr" : "sum(`jul_sales_per_sq_foot`) " }, { "ref" : "`aug_sales_per_sq_foot`", "expr" : "sum(`aug_sales_per_sq_foot`) " }, { "ref" : "`sep_sales_per_sq_foot`", "expr" : "sum(`sep_sales_per_sq_foot`) " }, { "ref" : "`oct_sales_per_sq_foot`", "expr" : "sum(`oct_sales_per_sq_foot`) " }, { "ref" : "`nov_sales_per_sq_foot`", "expr" : "sum(`nov_sales_per_sq_foot`) " }, { "ref" : "`dec_sales_per_sq_foot`", "expr" : "sum(`dec_sales_per_sq_foot`) " }, { "ref" : "`jan_net`", "expr" : "sum(`jan_net`) " }, { "ref" : "`feb_net`", "expr" : "sum(`feb_net`) " }, { "ref" : "`mar_net`", "expr" : "sum(`mar_net`) " }, { "ref" : "`apr_net`", "expr" : "sum(`apr_net`) " }, { "ref" : "`may_net`", "expr" : "sum(`may_net`) " }, { "ref" : "`jun_net`", "expr" : "sum(`jun_net`) " }, { "ref" : "`jul_net`", "expr" : "sum(`jul_net`) " }, { "ref" : "`aug_net`", "expr" : "sum(`aug_net`) " }, { "ref" : "`sep_net`", "expr" : "sum(`sep_net`) " }, { "ref" : "`oct_net`", "expr" : "sum(`oct_net`) " }, { "ref" : "`nov_net`", "expr" : "sum(`nov_net`) " }, { "ref" : "`dec_net`", "expr" : "sum(`dec_net`) " } ], "cost" : 2152.4908593749997 }, { "pop" : "top-n", "@id" : 5, "child" : 6, "orderings" : [ { "order" : "ASC", "expr" : "`w_warehouse_name`", "nullDirection" : "LAST" } ], "reverse" : false, "limit" : 100, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 216.09319999999997 }, { "pop" : "selection-vector-remover", "@id" : 4, "child" : 5, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 216.09319999999997 }, { "pop" : "limit", "@id" : 3, "child" : 4, "first" : 0, "last" : 100, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 100.0 }, { "pop" : "selection-vector-remover", "@id" : 2, "child" : 3, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 100.0 }, { "pop" : "project", "@id" : 1, "exprs" : [ { "ref" : "`w_warehouse_name`", "expr" : "`w_warehouse_name`" }, { "ref" : "`w_warehouse_sq_ft`", "expr" : "`w_warehouse_sq_ft`" }, { "ref" : "`w_city`", "expr" : "`w_city`" }, { "ref" : "`w_county`", "expr" : "`w_county`" }, { "ref" : "`w_state`", "expr" : "`w_state`" }, { "ref" : "`w_country`", "expr" : "`w_country`" }, { "ref" : "`ship_carriers`", "expr" : "`ship_carriers`" }, { "ref" : "`year1`", "expr" : "`year1`" }, { "ref" : "`jan_sales`", "expr" : "`jan_sales`" }, { "ref" : "`feb_sales`", "expr" : "`feb_sales`" }, { "ref" : "`mar_sales`", "expr" : "`mar_sales`" }, { "ref" : "`apr_sales`", "expr" : "`apr_sales`" }, { "ref" : "`may_sales`", "expr" : "`may_sales`" }, { "ref" : "`jun_sales`", "expr" : "`jun_sales`" }, { "ref" : "`jul_sales`", "expr" : "`jul_sales`" }, { "ref" : "`aug_sales`", "expr" : "`aug_sales`" }, { "ref" : "`sep_sales`", "expr" : "`sep_sales`" }, { "ref" : "`oct_sales`", "expr" : "`oct_sales`" }, { "ref" : "`nov_sales`", "expr" : "`nov_sales`" }, { "ref" : "`dec_sales`", "expr" : "`dec_sales`" }, { "ref" : "`jan_sales_per_sq_foot`", "expr" : "`jan_sales_per_sq_foot`" }, { "ref" : "`feb_sales_per_sq_foot`", "expr" : "`feb_sales_per_sq_foot`" }, { "ref" : "`mar_sales_per_sq_foot`", "expr" : "`mar_sales_per_sq_foot`" }, { "ref" : "`apr_sales_per_sq_foot`", "expr" : "`apr_sales_per_sq_foot`" }, { "ref" : "`may_sales_per_sq_foot`", "expr" : "`may_sales_per_sq_foot`" }, { "ref" : "`jun_sales_per_sq_foot`", "expr" : "`jun_sales_per_sq_foot`" }, { "ref" : "`jul_sales_per_sq_foot`", "expr" : "`jul_sales_per_sq_foot`" }, { "ref" : "`aug_sales_per_sq_foot`", "expr" : "`aug_sales_per_sq_foot`" }, { "ref" : "`sep_sales_per_sq_foot`", "expr" : "`sep_sales_per_sq_foot`" }, { "ref" : "`oct_sales_per_sq_foot`", "expr" : "`oct_sales_per_sq_foot`" }, { "ref" : "`nov_sales_per_sq_foot`", "expr" : "`nov_sales_per_sq_foot`" }, { "ref" : "`dec_sales_per_sq_foot`", "expr" : "`dec_sales_per_sq_foot`" }, { "ref" : "`jan_net`", "expr" : "`jan_net`" }, { "ref" : "`feb_net`", "expr" : "`feb_net`" }, { "ref" : "`mar_net`", "expr" : "`mar_net`" }, { "ref" : "`apr_net`", "expr" : "`apr_net`" }, { "ref" : "`may_net`", "expr" : "`may_net`" }, { "ref" : "`jun_net`", "expr" : "`jun_net`" }, { "ref" : "`jul_net`", "expr" : "`jul_net`" }, { "ref" : "`aug_net`", "expr" : "`aug_net`" }, { "ref" : "`sep_net`", "expr" : "`sep_net`" }, { "ref" : "`oct_net`", "expr" : "`oct_net`" }, { "ref" : "`nov_net`", "expr" : "`nov_net`" }, { "ref" : "`dec_net`", "expr" : "`dec_net`" } ], "child" : 2, "outputProj" : true, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 100.0 }, { "pop" : "screen", "@id" : 0, "child" : 1, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 100.0 } ] } {code} With option values: {code:sql} `planner.width.max_per_node` = 22 `planner.memory.max_query_memory_per_node` = 10737418240 `drill.exec.hashagg.fallback.enabled` = true {code} This plan uses TPC-DS parquet data. A decision to spill for both phases is taken considering free memory amount, allocated for the concrete operator. For the case when we have large {{planner.width.max_per_node}}, a small memory amount is allocated for HashAgg operator. In this case, spilling will be produced for every batch, and the query is executed very slowly. For the case, when {{planner.width.max_per_node}} is small, spilling does not happen and query executes quickly. Therefore, except {{planner.width.max_per_node}}, Drill should take into account available memory when calculating operators count. Also, this problem, perhaps, may be solved at the planning stage, when will be implemented cumulative memory cost usage. -- This message was sent by Atlassian JIRA (v6.4.14#64029)