-
-
Save IdanFridman/8378ac3a7543888ea61beea4a0c9b440 to your computer and use it in GitHub Desktop.
profile with outter join
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "profile":[ | |
| { | |
| "executor":"Top", | |
| "limit":"@@SESSION.`sql_select_limit`", | |
| "actual_row_count":{ "value":300 }, | |
| "actual_total_time":{ "value":0 }, | |
| "inputs":[ | |
| { | |
| "executor":"Gather", | |
| "partitions":"all", | |
| "est_rows":"1", | |
| "query":"SELECT `SUBQ_VWW_0`.`player_id` AS `player_id`, `SUBQ_VWW_0`.`game_id` AS `game_id`, `SUBQ_VWW_0`.`bet_amount_converted` AS `bet_amount_converted`, `SUBQ_VWW_0`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `SUBQ_VWW_0`.`base_currency` AS `base_currency` FROM ( SELECT `r2`.`player_id` AS `player_id`, `r2`.`game_id` AS `game_id`, `r2`.`bet_amount_converted` AS `bet_amount_converted`, `r2`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `r2`.`base_currency` AS `base_currency` FROM :: `winner`.`r2` as `r2` UNION ALL SELECT `r3`.`player_id` AS `player_id`, `r3`.`game_id` AS `game_id`, `r3`.`bet_amount_converted` AS `bet_amount_converted`, `r3`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `r3`.`base_currency` AS `base_currency` FROM @ `winner`.REMOTE(:: `winner`.`r3`(0) AS SELECT (1!:>bigint(20) NOT NULL) AS `i0`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `player_id`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `game_id`,(1!:>decimal(15,4) NULL) AS `bet_amount_converted`,(1!:>decimal(15,4) NULL) AS `cont_amount_jackpot`,(1!:>varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `base_currency`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `event_id`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `event_id_8`,(1!:>varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `status`,(1!:>varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `action`,(1!:>datetime NULL) AS `event_arrival_time`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `status_12`) as `r3` GROUP BY `r3`.`i0` HAVING ((`r3`.`status_12` = 'ACCEPTED') AND (`r3`.`event_arrival_time` BETWEEN '2019-12-22 00:00:00' AND '2019-12-23 15:00:00') AND (`r3`.`action` IN ('PLACE_BET','CANCEL_BET')) AND (`r3`.`game_id` IN ('2k9d71558609680765')) AND (COUNT(*) = 16)) ) AS `SUBQ_VWW_0` LIMIT NULL OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", | |
| "alias":"remote_0", | |
| "actual_row_count":{ "value":300, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "actual_total_time":{ "value":0 }, | |
| "start_time":{ "value":31 }, | |
| "end_time":{ "value":17110 }, | |
| "inputs":[ | |
| { | |
| "executor":"Project", | |
| "out":[ | |
| { | |
| "alias":"", | |
| "projection":"SUBQ_VWW_0.player_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"SUBQ_VWW_0.game_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"SUBQ_VWW_0.bet_amount_converted" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"SUBQ_VWW_0.cont_amount_jackpot" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"SUBQ_VWW_0.base_currency" | |
| } | |
| ], | |
| "est_rows":"1", | |
| "est_select_cost":"64", | |
| "subselects":[], | |
| "actual_row_count":{ "value":300, "avg":18.750000, "stddev":72.618438, "max":300, "maxPartition":7 }, | |
| "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "start_time":{ "value":17109, "avg":17109.000000, "stddev":0.000000, "max":17109, "maxPartition":7 }, | |
| "network_traffic":{ "value":14387, "avg":14387.000000, "stddev":0.000000, "max":14387, "maxPartition":7 }, | |
| "network_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "inputs":[ | |
| { | |
| "executor":"Top", | |
| "limit":"?", | |
| "actual_row_count":{ "value":300, "avg":18.750000, "stddev":72.618438, "max":300, "maxPartition":7 }, | |
| "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "inputs":[ | |
| { | |
| "executor":"TableScan", | |
| "table":"0tmp", | |
| "alias":"SUBQ_VWW_0", | |
| "storage":"list", | |
| "stream":"no", | |
| "actual_row_count":{ "value":300, "avg":18.750000, "stddev":72.618438, "max":300, "maxPartition":7 }, | |
| "actual_total_time":{ "value":30, "avg":15.000000, "stddev":15.000000, "max":30, "maxPartition":0 }, | |
| "start_time":{ "value":17109, "avg":17109.000000, "stddev":0.000000, "max":17109, "maxPartition":7 }, | |
| "inputs":[ | |
| { | |
| "executor":"UnionAll", | |
| "actual_row_count":{ "value":691009, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "inputs":[ | |
| { | |
| "executor":"Project", | |
| "out":[ | |
| { | |
| "alias":"", | |
| "projection":"r2.player_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r2.game_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r2.bet_amount_converted" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r2.cont_amount_jackpot" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r2.base_currency" | |
| } | |
| ], | |
| "subselects":[], | |
| "actual_row_count":{ "value":4645, "avg":290.312500, "stddev":19.334938, "max":326, "maxPartition":5 }, | |
| "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "start_time":{ "value":12818, "avg":14897.062500, "stddev":0.000000, "max":16977, "maxPartition":11 }, | |
| "inputs":[ | |
| { | |
| "executor":"TableScan", | |
| "table":"r2", | |
| "alias":"r2", | |
| "storage":"list", | |
| "stream":"yes", | |
| "actual_row_count":{ "value":4645, "avg":290.312500, "stddev":19.334938, "max":326, "maxPartition":5 }, | |
| "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "start_time":{ "value":31, "avg":31.750000, "stddev":0.000000, "max":34, "maxPartition":12 }, | |
| "inputs":[ | |
| { | |
| "executor":"Broadcast", | |
| "out":[ | |
| { | |
| "alias":"", | |
| "projection":"r1.i0" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.player_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.game_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.bet_amount_converted" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.cont_amount_jackpot" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.base_currency" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.event_id" | |
| }, | |
| { | |
| "alias":"event_id_8", | |
| "projection":"win.event_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"win.status" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.action" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.event_arrival_time" | |
| }, | |
| { | |
| "alias":"status_12", | |
| "projection":"r1.status" | |
| } | |
| ], | |
| "alias":"r2", | |
| "distribution":"direct", | |
| "query":"CREATE RESULT TABLE r2 WITH (READERS=1,PER_PARTITION=TRUE , BUFFERED_ROWS=NULL) WHERE (NOT ISNULL(`win`.`create_time`)), r3 PARTITION BY (`i0`) WITH (PARTITIONS=NULL, TAKEOVER_CONN=FALSE) WHERE ISNULL(`win`.`create_time`) AS SELECT STRAIGHT_JOIN `r1`.`i0` AS `i0`, `r1`.`player_id` AS `player_id`, `r1`.`game_id` AS `game_id`, `r1`.`bet_amount_converted` AS `bet_amount_converted`, `r1`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `r1`.`base_currency` AS `base_currency`, `r1`.`event_id` AS `event_id`, `win`.`event_id` AS `event_id_8`, `win`.`status` AS `status`, `r1`.`action` AS `action`, `r1`.`event_arrival_time` AS `event_arrival_time`, `r1`.`status` AS `status_12` FROM (`winner_0`.`winner` as `win` RIGHT_STRAIGHT_JOIN :: `winner`.`r1` as `r1` WITH (table_convert_subselect = FALSE) ON ((`r1`.`event_id` = `win`.`event_id`) AND (`win`.`status` IN ('ACCEPTED','CANCEL')))) OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", | |
| "actual_row_count":{ "value":31153902, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "inputs":[ | |
| { | |
| "executor":"NestedLoopJoin", | |
| "type":"right", | |
| "actual_row_count":{ "value":31153902, "avg":1947118.875000, "stddev":52.566476, "max":1947218, "maxPartition":8 }, | |
| "actual_total_time":{ "value":124, "avg":95.500000, "stddev":28.500000, "max":124, "maxPartition":8 }, | |
| "start_time":{ "value":2441, "avg":2504.875000, "stddev":0.000000, "max":2578, "maxPartition":15 }, | |
| "inputs":[ | |
| { | |
| "executor":"Filter", | |
| "condition":[ | |
| "win.status IN (...)" | |
| ], | |
| "subselects":[], | |
| "actual_row_count":{ "value":31153899, "avg":1947118.687500, "stddev":52.610264, "max":1947218, "maxPartition":8 }, | |
| "actual_total_time":{ "value":126, "avg":97.500000, "stddev":28.500000, "max":126, "maxPartition":8 }, | |
| "start_time":{ "value":2560, "avg":2693.875000, "stddev":0.000000, "max":2859, "maxPartition":13 }, | |
| "inputs":[ | |
| { | |
| "executor":"HashTableBuild", | |
| "table":"r1", | |
| "alias":"r1", | |
| "index":"PRIMARY KEY (event_id) USING HASH BUCKET_COUNT=1073741824", | |
| "storage":"lf_hashtable", | |
| "stream":"no", | |
| "scan":[ | |
| "event_id = win.event_id" | |
| ], | |
| "subselects":[], | |
| "est_table_rows":"1", | |
| "actual_row_count":{ "value":1949149, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "actual_total_time":{ "value":322 }, | |
| "start_time":{ "value":2119 }, | |
| "memory_usage":{ "value":471203852 }, | |
| "inputs":[ | |
| { | |
| "executor":"Broadcast", | |
| "out":[ | |
| { | |
| "alias":"", | |
| "projection":"cce.player_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.game_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.bet_amount_converted" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.cont_amount_jackpot" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.base_currency" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.event_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.action" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.event_arrival_time" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.status" | |
| } | |
| ], | |
| "alias":"r1", | |
| "hash_key":[ | |
| "event_id" | |
| ], | |
| "distribution":"tree", | |
| "est_rows":"1", | |
| "query":"CREATE RESULT TABLE r1 WITH (HASH=(`event_id`), BLOOM_FILTER=false, GEN_MIN_MAX=false, VECTOR_JOIN=false, LEFT_JOIN=false) AS SELECT `r0`.`player_id` AS `player_id`, `r0`.`game_id` AS `game_id`, `r0`.`bet_amount_converted` AS `bet_amount_converted`, `r0`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `r0`.`base_currency` AS `base_currency`, `r0`.`event_id` AS `event_id`, `r0`.`action` AS `action`, `r0`.`event_arrival_time` AS `event_arrival_time`, `r0`.`status` AS `status`, ROW_NUMBER() OVER () AS `i0`, 0 AS `$$fullJoin$$Matched$$` FROM @ `contribution`.REMOTE(:: NULL `contribution`.`r0` AS SELECT (1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `player_id`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `game_id`,(1!:>decimal(15,4) NULL) AS `bet_amount_converted`,(1!:>decimal(15,4) NULL) AS `cont_amount_jackpot`,(1!:>varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `base_currency`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `event_id`,(1!:>varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `action`,(1!:>datetime NULL) AS `event_arrival_time`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `status`) as `r0` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", | |
| "query2":"CREATE RESULT TABLE r0 WITH (READERS=NULL, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT * FROM @ `contribution`.REMOTE(:: SELECT `cce`.`player_id` AS `player_id`, `cce`.`game_id` AS `game_id`, `cce`.`bet_amount_converted` AS `bet_amount_converted`, `cce`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `cce`.`base_currency` AS `base_currency`, `cce`.`event_id` AS `event_id`, `cce`.`action` AS `action`, `cce`.`event_arrival_time` AS `event_arrival_time`, `cce`.`status` AS `status` FROM `contribution_0`.`cont_event` as `cce` WHERE ((`cce`.`game_id` IN ('2k9d71558609680765')) AND (`cce`.`action` IN ('PLACE_BET','CANCEL_BET')) AND (`cce`.`event_arrival_time` BETWEEN '2019-12-22 00:00:00' AND '2019-12-23 15:00:00') AND (`cce`.`status` = 'ACCEPTED'))) AS remote_select OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", | |
| "actual_row_count":{ "value":1949149 }, | |
| "actual_total_time":{ "value":62 }, | |
| "start_time":{ "value":88 }, | |
| "network_traffic":{ "value":1146099612 }, | |
| "network_time":{ "value":891, "avg":933.187500, "stddev":55.500317, "max":891, "maxPartition":0 }, | |
| "inputs":[ | |
| { | |
| "executor":"Filter", | |
| "condition":[ | |
| "cce.game_id IN (...) AND cce.action IN (...) AND cce.event_arrival_time >= ? AND cce.event_arrival_time <= ? AND cce.status = ?" | |
| ], | |
| "subselects":[], | |
| "actual_row_count":{ "value":1949149, "avg":121821.812500, "stddev":312.350208, "max":122311, "maxPartition":1 }, | |
| "actual_total_time":{ "value":65, "avg":54.500000, "stddev":10.500000, "max":65, "maxPartition":8 }, | |
| "start_time":{ "value":20, "avg":22.500000, "stddev":0.000000, "max":31, "maxPartition":15 }, | |
| "inputs":[ | |
| { | |
| "executor":"ColumnStoreScan", | |
| "db":"contribution", | |
| "table":"cont_event", | |
| "alias":"cce", | |
| "index":"KEY operator_id (event_arrival_time, action, operator_id, game_id, correlation_id) USING CLUSTERED COLUMNSTORE", | |
| "storage":"columnar", | |
| "est_table_rows":"277424661", | |
| "est_filtered":"1", | |
| "actual_row_count":{ "value":16511936, "avg":1031996.000000, "stddev":26731.119206, "max":1088406, "maxPartition":4 }, | |
| "actual_total_time":{ "value":991, "avg":972.000000, "stddev":19.000000, "max":991, "maxPartition":0 }, | |
| "start_time":{ "value":20, "avg":22.250000, "stddev":0.000000, "max":31, "maxPartition":15 }, | |
| "memory_usage":{ "value":15728640, "avg":983040.000000, "stddev":340535.045175, "max":1572864, "maxPartition":4 }, | |
| "segments_scanned":{ "value":20, "avg":1.250000, "stddev":0.433013, "max":2, "maxPartition":4 }, | |
| "segments_skipped":{ "value":268, "avg":16.750000, "stddev":0.433013, "max":17, "maxPartition":0 }, | |
| "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "segments_filter_encoded_data":{ "value":20, "avg":1.250000, "stddev":0.433013, "max":2, "maxPartition":4 }, | |
| "inputs":[] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| { | |
| "executor":"ColumnStoreScan", | |
| "db":"winner", | |
| "table":"winner", | |
| "alias":"win", | |
| "index":"KEY operator_id (operator_id, game_id, operator_game_code, player_id, create_time) USING CLUSTERED COLUMNSTORE", | |
| "storage":"columnar", | |
| "est_table_rows":"773444", | |
| "est_filtered":"233307", | |
| "actual_row_count":{ "value":773446, "avg":48340.375000, "stddev":247.940233, "max":48687, "maxPartition":5 }, | |
| "actual_total_time":{ "value":134, "avg":105.000000, "stddev":29.000000, "max":134, "maxPartition":8 }, | |
| "start_time":{ "value":2441, "avg":2504.250000, "stddev":0.000000, "max":2578, "maxPartition":15 }, | |
| "memory_usage":{ "value":8388608, "avg":524288.000000, "stddev":0.000000, "max":524288, "maxPartition":0 }, | |
| "segments_scanned":{ "value":61, "avg":3.812500, "stddev":0.390312, "max":4, "maxPartition":0 }, | |
| "segments_skipped":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "inputs":[] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| { | |
| "executor":"Project", | |
| "out":[ | |
| { | |
| "alias":"", | |
| "projection":"r3.player_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r3.game_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r3.bet_amount_converted" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r3.cont_amount_jackpot" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r3.base_currency" | |
| } | |
| ], | |
| "subselects":[], | |
| "actual_row_count":{ "value":686364, "avg":42897.750000, "stddev":46779.395007, "max":121420, "maxPartition":7 }, | |
| "actual_total_time":{ "value":26, "avg":13.000000, "stddev":13.000000, "max":26, "maxPartition":0 }, | |
| "start_time":{ "value":16976, "avg":16988.125000, "stddev":0.000000, "max":17012, "maxPartition":5 }, | |
| "inputs":[ | |
| { | |
| "executor":"Filter", | |
| "condition":[ | |
| "r3.status_12 = ? AND (r3.event_arrival_time BETWEEN ? AND ?) AND r3.action IN (...) AND r3.game_id IN (...) AND $0 = 16" | |
| ], | |
| "subselects":[], | |
| "actual_row_count":{ "value":686364, "avg":42897.750000, "stddev":46779.395007, "max":121420, "maxPartition":7 }, | |
| "actual_total_time":{ "value":92, "avg":46.000000, "stddev":46.000000, "max":92, "maxPartition":0 }, | |
| "start_time":{ "value":16976, "avg":16988.125000, "stddev":0.000000, "max":17012, "maxPartition":5 }, | |
| "inputs":[ | |
| { | |
| "executor":"HashGroupBy", | |
| "out":[ | |
| { | |
| "alias":"$0", | |
| "projection":"COUNT(*)" | |
| } | |
| ], | |
| "groups":[ | |
| "r3.i0" | |
| ], | |
| "encoded_group_by_unsupported":"not a columnstore table: r3", | |
| "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", | |
| "subselects":[], | |
| "actual_row_count":{ "value":999779, "avg":62486.187500, "stddev":59371.815458, "max":122387, "maxPartition":3 }, | |
| "actual_total_time":{ "value":1208, "avg":632.000000, "stddev":576.000000, "max":1208, "maxPartition":0 }, | |
| "start_time":{ "value":12819, "avg":14907.500000, "stddev":0.000000, "max":16998, "maxPartition":14 }, | |
| "memory_usage":{ "value":287965312, "avg":17997832.000000, "stddev":17113609.707703, "max":35258384, "maxPartition":1 }, | |
| "inputs":[ | |
| { | |
| "executor":"TableScan", | |
| "table":"r3", | |
| "alias":"r3", | |
| "storage":"list", | |
| "stream":"yes", | |
| "actual_row_count":{ "value":15855812, "avg":990988.250000, "stddev":956420.663595, "max":1955873, "maxPartition":3 }, | |
| "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "start_time":{ "value":16976, "avg":16988.125000, "stddev":0.000000, "max":17012, "maxPartition":5 }, | |
| "end_time":{ "value":16976, "avg":16988.125000, "stddev":14.163664, "max":17012, "maxPartition":5 }, | |
| "inputs":[ | |
| { | |
| "executor":"Broadcast", | |
| "out":[ | |
| { | |
| "alias":"", | |
| "projection":"r1.i0" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.player_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.game_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.bet_amount_converted" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.cont_amount_jackpot" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.base_currency" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.event_id" | |
| }, | |
| { | |
| "alias":"event_id_8", | |
| "projection":"win.event_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"win.status" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.action" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"r1.event_arrival_time" | |
| }, | |
| { | |
| "alias":"status_12", | |
| "projection":"r1.status" | |
| } | |
| ], | |
| "alias":"r2", | |
| "distribution":"direct", | |
| "query":"CREATE RESULT TABLE r2 WITH (READERS=1,PER_PARTITION=TRUE , BUFFERED_ROWS=NULL) WHERE (NOT ISNULL(`win`.`create_time`)), r3 PARTITION BY (`i0`) WITH (PARTITIONS=NULL, TAKEOVER_CONN=FALSE) WHERE ISNULL(`win`.`create_time`) AS SELECT STRAIGHT_JOIN `r1`.`i0` AS `i0`, `r1`.`player_id` AS `player_id`, `r1`.`game_id` AS `game_id`, `r1`.`bet_amount_converted` AS `bet_amount_converted`, `r1`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `r1`.`base_currency` AS `base_currency`, `r1`.`event_id` AS `event_id`, `win`.`event_id` AS `event_id_8`, `win`.`status` AS `status`, `r1`.`action` AS `action`, `r1`.`event_arrival_time` AS `event_arrival_time`, `r1`.`status` AS `status_12` FROM (`winner_0`.`winner` as `win` RIGHT_STRAIGHT_JOIN :: `winner`.`r1` as `r1` WITH (table_convert_subselect = FALSE) ON ((`r1`.`event_id` = `win`.`event_id`) AND (`win`.`status` IN ('ACCEPTED','CANCEL')))) OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", | |
| "actual_row_count":{ "value":31153902, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "network_time":{ "value":214 }, | |
| "inputs":[ | |
| { | |
| "executor":"NestedLoopJoin", | |
| "type":"right", | |
| "actual_row_count":{ "value":31153902, "avg":1947118.875000, "stddev":52.566476, "max":1947218, "maxPartition":8 }, | |
| "actual_total_time":{ "value":124, "avg":95.500000, "stddev":28.500000, "max":124, "maxPartition":8 }, | |
| "start_time":{ "value":2441, "avg":2504.875000, "stddev":0.000000, "max":2578, "maxPartition":15 }, | |
| "inputs":[ | |
| { | |
| "executor":"Filter", | |
| "condition":[ | |
| "win.status IN (...)" | |
| ], | |
| "subselects":[], | |
| "actual_row_count":{ "value":31153899, "avg":1947118.687500, "stddev":52.610264, "max":1947218, "maxPartition":8 }, | |
| "actual_total_time":{ "value":126, "avg":97.500000, "stddev":28.500000, "max":126, "maxPartition":8 }, | |
| "start_time":{ "value":2560, "avg":2693.875000, "stddev":0.000000, "max":2859, "maxPartition":13 }, | |
| "inputs":[ | |
| { | |
| "executor":"HashTableBuild", | |
| "table":"r1", | |
| "alias":"r1", | |
| "index":"PRIMARY KEY (event_id) USING HASH BUCKET_COUNT=1073741824", | |
| "storage":"lf_hashtable", | |
| "stream":"no", | |
| "scan":[ | |
| "event_id = win.event_id" | |
| ], | |
| "subselects":[], | |
| "est_table_rows":"1", | |
| "actual_row_count":{ "value":1949149, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "actual_total_time":{ "value":322 }, | |
| "start_time":{ "value":2119 }, | |
| "memory_usage":{ "value":471203852 }, | |
| "inputs":[ | |
| { | |
| "executor":"Broadcast", | |
| "out":[ | |
| { | |
| "alias":"", | |
| "projection":"cce.player_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.game_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.bet_amount_converted" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.cont_amount_jackpot" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.base_currency" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.event_id" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.action" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.event_arrival_time" | |
| }, | |
| { | |
| "alias":"", | |
| "projection":"cce.status" | |
| } | |
| ], | |
| "alias":"r1", | |
| "hash_key":[ | |
| "event_id" | |
| ], | |
| "distribution":"tree", | |
| "est_rows":"1", | |
| "query":"CREATE RESULT TABLE r1 WITH (HASH=(`event_id`), BLOOM_FILTER=false, GEN_MIN_MAX=false, VECTOR_JOIN=false, LEFT_JOIN=false) AS SELECT `r0`.`player_id` AS `player_id`, `r0`.`game_id` AS `game_id`, `r0`.`bet_amount_converted` AS `bet_amount_converted`, `r0`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `r0`.`base_currency` AS `base_currency`, `r0`.`event_id` AS `event_id`, `r0`.`action` AS `action`, `r0`.`event_arrival_time` AS `event_arrival_time`, `r0`.`status` AS `status`, ROW_NUMBER() OVER () AS `i0`, 0 AS `$$fullJoin$$Matched$$` FROM @ `contribution`.REMOTE(:: NULL `contribution`.`r0` AS SELECT (1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `player_id`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `game_id`,(1!:>decimal(15,4) NULL) AS `bet_amount_converted`,(1!:>decimal(15,4) NULL) AS `cont_amount_jackpot`,(1!:>varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `base_currency`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `event_id`,(1!:>varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `action`,(1!:>datetime NULL) AS `event_arrival_time`,(1!:>varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `status`) as `r0` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", | |
| "query2":"CREATE RESULT TABLE r0 WITH (READERS=NULL, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT * FROM @ `contribution`.REMOTE(:: SELECT `cce`.`player_id` AS `player_id`, `cce`.`game_id` AS `game_id`, `cce`.`bet_amount_converted` AS `bet_amount_converted`, `cce`.`cont_amount_jackpot` AS `cont_amount_jackpot`, `cce`.`base_currency` AS `base_currency`, `cce`.`event_id` AS `event_id`, `cce`.`action` AS `action`, `cce`.`event_arrival_time` AS `event_arrival_time`, `cce`.`status` AS `status` FROM `contribution_0`.`cont_event` as `cce` WHERE ((`cce`.`game_id` IN ('2k9d71558609680765')) AND (`cce`.`action` IN ('PLACE_BET','CANCEL_BET')) AND (`cce`.`event_arrival_time` BETWEEN '2019-12-22 00:00:00' AND '2019-12-23 15:00:00') AND (`cce`.`status` = 'ACCEPTED'))) AS remote_select OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", | |
| "actual_row_count":{ "value":1949149 }, | |
| "actual_total_time":{ "value":62 }, | |
| "start_time":{ "value":88 }, | |
| "network_traffic":{ "value":1146099612 }, | |
| "network_time":{ "value":891, "avg":933.187500, "stddev":55.500317, "max":891, "maxPartition":0 }, | |
| "inputs":[ | |
| { | |
| "executor":"Filter", | |
| "condition":[ | |
| "cce.game_id IN (...) AND cce.action IN (...) AND cce.event_arrival_time >= ? AND cce.event_arrival_time <= ? AND cce.status = ?" | |
| ], | |
| "subselects":[], | |
| "actual_row_count":{ "value":1949149, "avg":121821.812500, "stddev":312.350208, "max":122311, "maxPartition":1 }, | |
| "actual_total_time":{ "value":65, "avg":54.500000, "stddev":10.500000, "max":65, "maxPartition":8 }, | |
| "start_time":{ "value":20, "avg":22.500000, "stddev":0.000000, "max":31, "maxPartition":15 }, | |
| "inputs":[ | |
| { | |
| "executor":"ColumnStoreScan", | |
| "db":"contribution", | |
| "table":"cont_event", | |
| "alias":"cce", | |
| "index":"KEY operator_id (event_arrival_time, action, operator_id, game_id, correlation_id) USING CLUSTERED COLUMNSTORE", | |
| "storage":"columnar", | |
| "est_table_rows":"277424661", | |
| "est_filtered":"1", | |
| "actual_row_count":{ "value":16511936, "avg":1031996.000000, "stddev":26731.119206, "max":1088406, "maxPartition":4 }, | |
| "actual_total_time":{ "value":991, "avg":972.000000, "stddev":19.000000, "max":991, "maxPartition":0 }, | |
| "start_time":{ "value":20, "avg":22.250000, "stddev":0.000000, "max":31, "maxPartition":15 }, | |
| "memory_usage":{ "value":15728640, "avg":983040.000000, "stddev":340535.045175, "max":1572864, "maxPartition":4 }, | |
| "segments_scanned":{ "value":20, "avg":1.250000, "stddev":0.433013, "max":2, "maxPartition":4 }, | |
| "segments_skipped":{ "value":268, "avg":16.750000, "stddev":0.433013, "max":17, "maxPartition":0 }, | |
| "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "segments_filter_encoded_data":{ "value":20, "avg":1.250000, "stddev":0.433013, "max":2, "maxPartition":4 }, | |
| "inputs":[] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| { | |
| "executor":"ColumnStoreScan", | |
| "db":"winner", | |
| "table":"winner", | |
| "alias":"win", | |
| "index":"KEY operator_id (operator_id, game_id, operator_game_code, player_id, create_time) USING CLUSTERED COLUMNSTORE", | |
| "storage":"columnar", | |
| "est_table_rows":"773444", | |
| "est_filtered":"233307", | |
| "actual_row_count":{ "value":773446, "avg":48340.375000, "stddev":247.940233, "max":48687, "maxPartition":5 }, | |
| "actual_total_time":{ "value":134, "avg":105.000000, "stddev":29.000000, "max":134, "maxPartition":8 }, | |
| "start_time":{ "value":2441, "avg":2504.250000, "stddev":0.000000, "max":2578, "maxPartition":15 }, | |
| "memory_usage":{ "value":8388608, "avg":524288.000000, "stddev":0.000000, "max":524288, "maxPartition":0 }, | |
| "segments_scanned":{ "value":61, "avg":3.812500, "stddev":0.390312, "max":4, "maxPartition":0 }, | |
| "segments_skipped":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, | |
| "inputs":[] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| ], | |
| "version":"2", | |
| "info":{ | |
| "memsql_version":"6.8.11", | |
| "memsql_version_hash":"e973c625ae6d372c2d41d39b19612202c244fd7a", | |
| "num_online_leaves":"2", | |
| "num_online_aggs":"1", | |
| "context_database":"contribution" | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment