Query (id=e34d8a6c4ced0b6c:286730a6c1b8babb): Summary: Session ID: 604fd63b0bcdd21e:1e98c99d386f2a9 Session Type: BEESWAX Start Time: 2014-07-15 16:53:51.860205000 End Time: 2014-07-15 16:53:53.376377000 Query Type: QUERY Query State: FINISHED Query Status: OK Impala Version: impalad version 1.3.1-cdh5 RELEASE (build ) User: stephen Network Address: ::ffff:10.80.5.144:41257 Default Db: default Sql Statement: select COUNT(DISTINCT b.doc_id) FROM ( SELECT DISTINCT doc_id FROM `stats`.pmp_interactions ) a FULL OUTER JOIN ( SELECT DISTINCT doc_id FROM warehouse.pmp_interactions_read_times WHERE ds = '2014-07-09' ) b ON a.doc_id = b.doc_id WHERE a.doc_id IS NULL AND b.doc_id IS NOT NULL Plan: ---------------- Estimated Per-Host Requirements: Memory=0B VCores=0 WARNING: The following tables are missing relevant table and/or column statistics. stats.pmp_interactions, warehouse.pmp_interactions_read_times F00:PLAN FRAGMENT [PARTITION=UNPARTITIONED] 06:AGGREGATE [MERGE FINALIZE] | output: count(b.doc_id) | hosts=10 per-host-mem=unavailable | tuple-ids=7 row-size=8B cardinality=1 | 05:AGGREGATE | group by: doc_id | hosts=10 per-host-mem=unavailable | tuple-ids=6 row-size=8B cardinality=4239204 | 04:HASH JOIN [FULL OUTER JOIN] | hash predicates: doc_id = doc_id | other predicates: doc_id IS NULL, doc_id IS NOT NULL | hosts=10 per-host-mem=unavailable | tuple-ids=1N,4N row-size=12B cardinality=4239204 | |--03:AGGREGATE [FINALIZE] | | group by: doc_id | | hosts=10 per-host-mem=unavailable | | tuple-ids=4 row-size=8B cardinality=2089237 | | | 02:SCAN HDFS [warehouse.pmp_interactions_read_times] | partitions=1/408 size=23.08MB | predicates: warehouse.pmp_interactions_read_times.doc_id IS NOT NULL | table stats: 283399104 rows total | columns missing stats: doc_id | hosts=10 per-host-mem=unavailable | tuple-ids=3 row-size=23B cardinality=2089237 | 01:AGGREGATE [FINALIZE] | group by: doc_id | hosts=10 per-host-mem=unavailable | tuple-ids=1 row-size=4B cardinality=2149967 | 00:SCAN HDFS [stats.pmp_interactions] partitions=1/1 size=133.61MB table stats: 2149967 rows total column stats: unavailable hosts=10 per-host-mem=unavailable tuple-ids=0 row-size=4B cardinality=2149967 ---------------- Estimated Per-Host Mem: 0 Estimated Per-Host VCores: 0 Tables Missing Stats: stats.pmp_interactions,warehouse.pmp_interactions_read_times Admission result: Admitted immediately Request Pool: root.stephen Query Timeline: 1s517ms - Start execution: 1.644ms (1.644ms) - Planning finished: 34.515ms (32.871ms) - Submit for admission: 35.790ms (1.274ms) - Completed admission: 35.801ms (11.516us) - Rows available: 1s440ms (1s404ms) - First row fetched: 1s506ms (65.721ms) - Unregister query: 1s516ms (9.853ms) ImpalaServer: - ClientFetchWaitTimer: 75.514ms - RowMaterializationTimer: 8.381us Execution Profile e34d8a6c4ced0b6c:286730a6c1b8babb:(Total: 1s403ms, non-child: 0ns, % non-child: 0.00%) - FinalizationTimer: 0ns Coordinator Fragment:(Total: 1s206ms, non-child: 0ns, % non-child: 0.00%) Hdfs split stats (:<# splits>/): 0:2/34.28 MB 2:3/33.62 MB 3:1/11.20 MB 4:4/44.32 MB 5:3/33.27 MB MemoryUsage(500.0ms): 188.00 KB, 62.16 MB, 47.84 MB ThreadUsage(500.0ms): 1, 15, 6 - AverageThreadTokens: 7.33 - PeakMemoryUsage: 86.62 MB - PrepareTime: 7.599ms - RowsProduced: 1 - TotalCpuTime: 11s753ms - TotalNetworkReceiveTime: 0ns - TotalNetworkSendTime: 0ns - TotalStorageWaitTime: 5s928ms CodeGen:(Total: 188.371ms, non-child: 188.371ms, % non-child: 100.00%) - CodegenTime: 6.561ms - CompileTime: 180.462ms - LoadTime: 7.72ms - ModuleFileSize: 105.51 KB AGGREGATION_NODE (id=6):(Total: 1s213ms, non-child: 356.161us, % non-child: 0.03%) ExecOption: Codegen Enabled - BuildBuckets: 1.02K (1024) - BuildTime: 3.178us - GetResultsTime: 2.671us - LoadFactor: 0.00 - PeakMemoryUsage: 44.00 KB - RowsReturned: 1 - RowsReturnedRate: 0 AGGREGATION_NODE (id=5):(Total: 1s213ms, non-child: 2.766ms, % non-child: 0.23%) ExecOption: Codegen Enabled - BuildBuckets: 1.02K (1024) - BuildTime: 98.508us - GetResultsTime: 24.542us - LoadFactor: 0.58 - PeakMemoryUsage: 76.00 KB - RowsReturned: 883 - RowsReturnedRate: 727.00 /sec HASH_JOIN_NODE (id=4):(Total: 1s210ms, non-child: 79.684ms, % non-child: 6.58%) ExecOption: Build Side Codegen Enabled, Join Build-Side Prepared Asynchronously - BuildBuckets: 131.07K (131072) - BuildRows: 154.21K (154205) - BuildTime: 17.982ms - LeftChildRows: 155.69K (155689) - LeftChildTime: 61.148ms - LoadFactor: 0.70 - PeakMemoryUsage: 5.58 MB - RowsReturned: 883 - RowsReturnedRate: 729.00 /sec AGGREGATION_NODE (id=3):(Total: 1s071ms, non-child: 193.649ms, % non-child: 18.07%) ExecOption: Codegen Enabled - BuildBuckets: 131.07K (131072) - BuildTime: 124.482ms - GetResultsTime: 8.102ms - LoadFactor: 0.70 - PeakMemoryUsage: 10.28 MB - RowsReturned: 154.21K (154205) - RowsReturnedRate: 143.88 K/sec HDFS_SCAN_NODE (id=2):(Total: 878.82ms, non-child: 878.82ms, % non-child: 100.00%) Hdfs split stats (:<# splits>/): 0:1/23.08 MB Hdfs Read Thread Concurrency Bucket: 0:0% 1:100% 2:0% 3:0% 4:0% 5:0% 6:0% 7:0% 8:0% File Formats: RC_FILE/DEFAULT:1 ExecOption: Codegen enabled: 0 out of 2 BytesRead(500.0ms): 0, 8.00 MB, 16.00 MB - AverageHdfsReadThreadConcurrency: 1.00 - AverageScannerThreadConcurrency: 1.00 - BytesRead: 23.08 MB - BytesReadDataNodeCache: 0 - BytesReadLocal: 0 - BytesReadShortCircuit: 0 - BytesSkipped: 0 - DecompressionTime: 120.740ms - NumDisksAccessed: 1 - NumScannerThreadsStarted: 1 - PeakMemoryUsage: 25.06 MB - PerReadThreadRawHdfsThroughput: 25.90 MB/sec - RowsRead: 2.09M (2089237) - RowsReturned: 2.09M (2089237) - RowsReturnedRate: 2.38 M/sec - ScanRangesComplete: 1 - ScannerThreadsInvoluntaryContextSwitches: 0 - ScannerThreadsTotalWallClockTime: 1s003ms - MaterializeTupleTime(*): 185.44ms - ScannerThreadsSysTime: 13.997ms - ScannerThreadsUserTime: 291.955ms - ScannerThreadsVoluntaryContextSwitches: 7 - TotalRawHdfsReadTime(*): 891.94ms - TotalReadThroughput: 10.67 MB/sec AGGREGATION_NODE (id=1):(Total: 1s147ms, non-child: 129.850ms, % non-child: 11.32%) ExecOption: Codegen Enabled - BuildBuckets: 131.07K (131072) - BuildTime: 119.199ms - GetResultsTime: 5.672ms - LoadFactor: 0.70 - PeakMemoryUsage: 9.45 MB - RowsReturned: 155.69K (155689) - RowsReturnedRate: 135.74 K/sec HDFS_SCAN_NODE (id=0):(Total: 1s017ms, non-child: 1s017ms, % non-child: 100.00%) Hdfs split stats (:<# splits>/): 0:1/11.20 MB 2:3/33.62 MB 3:1/11.20 MB 4:4/44.32 MB 5:3/33.27 MB Hdfs Read Thread Concurrency Bucket: 0:0% 1:50% 2:0% 3:50% 4:0% 5:0% 6:0% 7:0% 8:0% File Formats: RC_FILE/DEFAULT:12 ExecOption: Codegen enabled: 0 out of 24 BytesRead(500.0ms): 0, 38.05 MB, 108.29 MB - AverageHdfsReadThreadConcurrency: 2.00 - AverageScannerThreadConcurrency: 7.50 - BytesRead: 133.62 MB - BytesReadDataNodeCache: 0 - BytesReadLocal: 33.97 MB - BytesReadShortCircuit: 33.97 MB - BytesSkipped: 0 - DecompressionTime: 337.887ms - NumDisksAccessed: 8 - NumScannerThreadsStarted: 12 - PeakMemoryUsage: 60.19 MB - PerReadThreadRawHdfsThroughput: 54.13 MB/sec - RowsRead: 2.15M (2149967) - RowsReturned: 2.15M (2149967) - RowsReturnedRate: 2.11 M/sec - ScanRangesComplete: 12 - ScannerThreadsInvoluntaryContextSwitches: 4 - ScannerThreadsTotalWallClockTime: 9s662ms - MaterializeTupleTime(*): 318.698ms - ScannerThreadsSysTime: 7.995ms - ScannerThreadsUserTime: 647.896ms - ScannerThreadsVoluntaryContextSwitches: 116 - TotalRawHdfsReadTime(*): 2s468ms - TotalReadThroughput: 72.19 MB/sec