Skip to content

Instantly share code, notes, and snippets.

@mpskovvang

mpskovvang/basic.json Secret

Created January 31, 2019 05:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mpskovvang/00909300c384ce533cb43f1e429f4841 to your computer and use it in GitHub Desktop.
Save mpskovvang/00909300c384ce533cb43f1e429f4841 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
basic.json
{ "profile":[ { "executor":"Top", "offset":"?", "limit":"?", "actual_row_count":{ "value":50 }, "actual_total_time":{ "value":0 }, "inputs":[ { "executor":"GatherMerge", "order":[ "remote_0.clicks DESC" ], "partitions":"all", "est_rows":"50", "query":"SELECT STRAIGHT_JOIN `r1_3`.`clicks` AS `clicks`, `r1_3`.`impressions` AS `impressions`, `r1_3`.`ctr` AS `ctr`, `r1_3`.`position` AS `position`, `r4`.`latest_snapshot_at` AS `latest_snapshot_at`, `r0`.`id` AS `id`, `r0`.`project_id` AS `project_id`, `r0`.`site_id` AS `site_id`, `r0`.`user_id` AS `user_id`, `r0`.`url_id` AS `url_id`, `r0`.`url` AS `url`, `r0`.`meta_title` AS `meta_title`, `r0`.`meta_description` AS `meta_description`, `r0`.`meta_keywords` AS `meta_keywords`, `r0`.`meta_canonical` AS `meta_canonical`, `r0`.`meta_index` AS `meta_index`, `r0`.`meta_follow` AS `meta_follow`, `r0`.`keyphrases` AS `keyphrases`, `r0`.`status` AS `status`, `r0`.`optimized_at` AS `optimized_at`, `r0`.`crawled_at` AS `crawled_at`, `r0`.`created_at` AS `created_at`, `r0`.`updated_at` AS `updated_at` FROM (( ( SELECT WITH(AGG_HINT_MERGE=true) `r1`.`page` AS `page`, SUM(`r1`.`clicks`) AS `clicks`, SUM(`r1`.`impressions`) AS `impressions`, (SUM(`r1`.`op_1`)\/SUM(`r1`.`impressions`)) AS `ctr`, (SUM(`r1`.`op_2`)\/SUM(`r1`.`impressions`)) AS `position` FROM @ `laravel`.REMOTE(:: `laravel`.`r1`(0) AS SELECT (1!:>varchar(2048) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `page`,(1!:>decimal(32,0) NULL) AS `clicks`,(1!:>decimal(32,0) NULL) AS `impressions`,(1!:>double NULL) AS `op_1`,(1!:>double NULL) AS `op_2`) as `r1` GROUP BY 1 ) AS `r1_3` STRAIGHT_JOIN @ `laravel`.REMOTE(:: `laravel`.`r0`(0) AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `id`,(1!:>bigint(20) unsigned NOT NULL) AS `project_id`,(1!:>bigint(20) unsigned NOT NULL) AS `site_id`,(1!:>bigint(20) unsigned NULL) AS `user_id`,(1!:>char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `url_id`,(1!:>varchar(4096) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL) AS `url`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_title`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_description`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_keywords`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_canonical`,(1!:>tinyint(1) NULL) AS `meta_index`,(1!:>tinyint(1) NULL) AS `meta_follow`,(1!:>JSON COLLATE utf8_bin NULL) AS `keyphrases`,(1!:>varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `status`,(1!:>datetime NULL) AS `optimized_at`,(1!:>datetime NULL) AS `crawled_at`,(1!:>datetime NULL) AS `created_at`,(1!:>datetime NULL) AS `updated_at`) as `r0` WITH (table_convert_subselect = FALSE)) LEFT JOIN :: `laravel`.`r4` as `r4` WITH (table_convert_subselect = FALSE) ON (`r4`.`page_id` = `r0`.`id`)) WHERE (`r0`.`url` = `r1_3`.`page`) ORDER BY 1 DESC LIMIT NULL OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "alias":"remote_0", "actual_row_count":{ "value":200, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":3 }, "end_time":{ "value":816 }, "inputs":[ { "executor":"Project", "out":[ { "alias":"", "projection":"r1_3.clicks" }, { "alias":"", "projection":"r1_3.impressions" }, { "alias":"", "projection":"r1_3.ctr" }, { "alias":"", "projection":"r1_3.position" }, { "alias":"", "projection":"r4.latest_snapshot_at" }, { "alias":"", "projection":"r0.id" }, { "alias":"", "projection":"r0.project_id" }, { "alias":"", "projection":"r0.site_id" }, { "alias":"", "projection":"r0.user_id" }, { "alias":"", "projection":"r0.url_id" }, { "alias":"", "projection":"r0.url" }, { "alias":"", "projection":"r0.meta_title" }, { "alias":"", "projection":"r0.meta_description" }, { "alias":"", "projection":"r0.meta_keywords" }, { "alias":"", "projection":"r0.meta_canonical" }, { "alias":"", "projection":"r0.meta_index" }, { "alias":"", "projection":"r0.meta_follow" }, { "alias":"", "projection":"r0.keyphrases" }, { "alias":"", "projection":"r0.status" }, { "alias":"", "projection":"r0.optimized_at" }, { "alias":"", "projection":"r0.crawled_at" }, { "alias":"", "projection":"r0.created_at" }, { "alias":"", "projection":"r0.updated_at" } ], "est_rows":"50", "est_select_cost":"383258", "subselects":[], "actual_row_count":{ "value":200, "avg":50.000000, "stddev":0.000000, "max":50, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":808, "avg":810.500000, "stddev":0.000000, "max":815, "maxPartition":3 }, "network_traffic":{ "value":164709, "avg":41177.250000, "stddev":6622.773300, "max":50518, "maxPartition":1 }, "network_time":{ "value":1, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 }, "inputs":[ { "executor":"TopSort", "limit":"?", "order":[ "r1_3.clicks DESC" ], "actual_row_count":{ "value":200, "avg":50.000000, "stddev":0.000000, "max":50, "maxPartition":0 }, "actual_total_time":{ "value":1, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 }, "start_time":{ "value":795, "avg":797.500000, "stddev":0.000000, "max":804, "maxPartition":3 }, "memory_usage":{ "value":524288, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 }, "inputs":[ { "executor":"HashJoin", "condition":[ "r4.page_id = r0.id" ], "type":"left", "subselects":[], "actual_row_count":{ "value":20833, "avg":5208.250000, "stddev":47.283057, "max":5283, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":795, "avg":797.500000, "stddev":0.000000, "max":804, "maxPartition":3 }, "inputs":[ { "executor":"HashTableBuild", "alias":"r4", "actual_row_count":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":13 }, "memory_usage":{ "value":0 }, "inputs":[ { "executor":"Broadcast", "out":[ { "alias":"", "projection":"r2.page_id" }, { "alias":"", "projection":"latest_snapshot_at" } ], "alias":"r4", "distribution":"tree", "est_rows":"1", "query":"CREATE RESULT TABLE r4 WITH (HASH=(`page_id`), BLOOM_FILTER=false, GEN_MIN_MAX=false, VECTOR_JOIN=false, LEFT_JOIN=false) AS SELECT `r3`.`latest_snapshot_at` AS `latest_snapshot_at`, `r3`.`page_id` AS `page_id` FROM @ `laravel`.REMOTE(:: NULL `laravel`.`r3` AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `page_id`,(1!:>datetime NULL) AS `latest_snapshot_at`) as `r3` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "query2":"CREATE RESULT TABLE r3 WITH (READERS=NULL, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT * FROM @ `laravel`.REMOTE(:: SELECT WITH(AGG_HINT_MERGE=true) `r2`.`page_id` AS `page_id`, MAX(`r2`.`latest_snapshot_at`) AS `latest_snapshot_at` FROM @ `laravel`.REMOTE(:: `laravel`.`r2`(0) AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `page_id`,(1!:>datetime NULL) AS `latest_snapshot_at`) as `r2` GROUP BY 1) AS remote_select OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":0 }, "actual_total_time":{ "value":0 }, "network_traffic":{ "value":0 }, "network_time":{ "value":0 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"latest_snapshot_at", "projection":"MAX(r2.latest_snapshot_at)" } ], "groups":[ "r2.page_id" ], "encoded_group_by_unsupported":"not a columnstore table: r2", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "memory_usage":{ "value":524288, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 }, "inputs":[ { "executor":"TableScan", "table":"r2", "alias":"r2", "storage":"list", "stream":"yes", "actual_row_count":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":13, "avg":13.000000, "stddev":0.000000, "max":13, "maxPartition":0 }, "end_time":{ "value":13, "avg":13.000000, "stddev":0.000000, "max":13, "maxPartition":0 }, "inputs":[ { "executor":"Repartition", "out":[ { "alias":"", "projection":"page_snapshots.page_id" }, { "alias":"", "projection":"latest_snapshot_at" } ], "alias":"r2", "shard_key":[ "page_id" ], "est_rows":"1", "est_select_cost":"4", "query":"CREATE RESULT TABLE r2 PARTITION BY (`page_id`) WITH (PARTITIONS=NULL, READERS=1, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT WITH(LOCAL_SHUFFLE_GROUP_BY=1) `page_snapshots`.`page_id` AS `page_id`, MAX(`page_snapshots`.`created_at`) AS `latest_snapshot_at` FROM `laravel_0`.`page_snapshots` as `page_snapshots` WHERE (`page_snapshots`.`view_id` = 50) GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "network_traffic":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "network_time":{ "value":0 }, "inputs":[ { "executor":"StreamingGroupBy", "out":[ { "alias":"latest_snapshot_at", "projection":"MAX(page_snapshots.created_at)" } ], "groups":[ "page_snapshots.page_id" ], "encoded_group_by_unsupported":"not a columnstore table: page_snapshots", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "memory_usage":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "inputs":[ { "executor":"Filter", "condition":[ "page_snapshots.view_id = ?" ], "subselects":[], "actual_row_count":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":2, "avg":2.000000, "stddev":0.000000, "max":2, "maxPartition":0 }, "inputs":[ { "executor":"TableScan", "db":"laravel", "table":"page_snapshots", "alias":"page_snapshots", "index":"KEY page_snapshots_page_id_view_id_created_at_index (page_id, view_id, created_at)", "storage":"lf_skiplist", "dir":"forward", "est_table_rows":"1223", "est_filtered":"1", "actual_row_count":{ "value":1223, "avg":305.750000, "stddev":14.306904, "max":317, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":2, "avg":2.000000, "stddev":0.000000, "max":2, "maxPartition":0 }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] }, { "executor":"HashJoin", "condition":[ "r1_3.page = r0.url" ], "type":"inner", "subselects":[], "actual_row_count":{ "value":20833, "avg":5208.250000, "stddev":47.283057, "max":5283, "maxPartition":0 }, "actual_total_time":{ "value":9, "avg":9.000000, "stddev":0.000000, "max":9, "maxPartition":0 }, "start_time":{ "value":795, "avg":797.500000, "stddev":0.000000, "max":804, "maxPartition":3 }, "inputs":[ { "executor":"HashTableBuild", "alias":"r0", "actual_row_count":{ "value":78762, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":5, "avg":5.000000, "stddev":0.000000, "max":5, "maxPartition":0 }, "start_time":{ "value":422, "avg":424.750000, "stddev":0.000000, "max":428, "maxPartition":3 }, "memory_usage":{ "value":87031808, "avg":21757952.000000, "stddev":131072.000000, "max":21889024, "maxPartition":0 }, "inputs":[ { "executor":"Repartition", "out":[ { "alias":"", "projection":"pages.id" }, { "alias":"", "projection":"pages.project_id" }, { "alias":"", "projection":"pages.site_id" }, { "alias":"", "projection":"pages.user_id" }, { "alias":"", "projection":"pages.url_id" }, { "alias":"", "projection":"pages.url" }, { "alias":"", "projection":"pages.meta_title" }, { "alias":"", "projection":"pages.meta_description" }, { "alias":"", "projection":"pages.meta_keywords" }, { "alias":"", "projection":"pages.meta_canonical" }, { "alias":"", "projection":"pages.meta_index" }, { "alias":"", "projection":"pages.meta_follow" }, { "alias":"", "projection":"pages.keyphrases" }, { "alias":"", "projection":"pages.status" }, { "alias":"", "projection":"pages.optimized_at" }, { "alias":"", "projection":"pages.crawled_at" }, { "alias":"", "projection":"pages.created_at" }, { "alias":"", "projection":"pages.updated_at" } ], "alias":"r0", "shard_key":[ "url" ], "est_rows":"78344", "query":"CREATE RESULT TABLE r0 PARTITION BY (`url`) WITH (PARTITIONS=NULL, READERS=1, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT `pages`.`id` AS `id`, `pages`.`project_id` AS `project_id`, `pages`.`site_id` AS `site_id`, `pages`.`user_id` AS `user_id`, `pages`.`url_id` AS `url_id`, `pages`.`url` AS `url`, `pages`.`meta_title` AS `meta_title`, `pages`.`meta_description` AS `meta_description`, `pages`.`meta_keywords` AS `meta_keywords`, `pages`.`meta_canonical` AS `meta_canonical`, `pages`.`meta_index` AS `meta_index`, `pages`.`meta_follow` AS `meta_follow`, `pages`.`keyphrases` AS `keyphrases`, `pages`.`status` AS `status`, `pages`.`optimized_at` AS `optimized_at`, `pages`.`crawled_at` AS `crawled_at`, `pages`.`created_at` AS `created_at`, `pages`.`updated_at` AS `updated_at` FROM `laravel_0`.`pages` as `pages` WHERE (`pages`.`site_id` = 40) OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":78762, "avg":19690.500000, "stddev":58.849384, "max":19741, "maxPartition":0 }, "actual_total_time":{ "value":39, "avg":39.000000, "stddev":0.000000, "max":39, "maxPartition":0 }, "start_time":{ "value":0, "avg":0.750000, "stddev":0.000000, "max":1, "maxPartition":1 }, "network_traffic":{ "value":67008047, "avg":16752011.750000, "stddev":105776.922746, "max":16914778, "maxPartition":3 }, "network_time":{ "value":59 }, "inputs":[ { "executor":"Filter", "condition":[ "pages.site_id = ?" ], "subselects":[], "actual_row_count":{ "value":78762, "avg":19690.500000, "stddev":58.849384, "max":19741, "maxPartition":0 }, "actual_total_time":{ "value":1, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 }, "start_time":{ "value":0, "avg":0.750000, "stddev":0.000000, "max":1, "maxPartition":1 }, "inputs":[ { "executor":"TableScan", "db":"laravel", "table":"pages", "alias":"pages", "index":"PRIMARY KEY (id)", "storage":"lf_skiplist", "dir":"forward", "est_table_rows":"442108", "est_filtered":"78344", "actual_row_count":{ "value":442108, "avg":110527.000000, "stddev":136.742459, "max":110736, "maxPartition":3 }, "actual_total_time":{ "value":129, "avg":129.000000, "stddev":0.000000, "max":129, "maxPartition":0 }, "start_time":{ "value":0, "avg":0.750000, "stddev":0.000000, "max":1, "maxPartition":1 }, "inputs":[] } ] } ] } ] }, { "executor":"TableScan", "table":"0tmp", "alias":"r1_3", "storage":"list", "stream":"yes", "est_table_rows":"113283", "actual_row_count":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "inputs":[ { "executor":"Project", "out":[ { "alias":"", "projection":"r1.page" }, { "alias":"", "projection":"clicks" }, { "alias":"", "projection":"impressions" }, { "alias":"ctr", "projection":"$0 \/ $2" }, { "alias":"position", "projection":"$1 \/ $3" } ], "est_rows":"113283", "subselects":[], "actual_row_count":{ "value":20833, "avg":5208.250000, "stddev":47.283057, "max":5283, "maxPartition":0 }, "actual_total_time":{ "value":7, "avg":7.000000, "stddev":0.000000, "max":7, "maxPartition":0 }, "start_time":{ "value":795, "avg":797.500000, "stddev":0.000000, "max":804, "maxPartition":3 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"clicks", "projection":"SUM(r1.clicks)" }, { "alias":"impressions", "projection":"SUM(r1.impressions)" }, { "alias":"$0", "projection":"SUM(r1.op_1)" }, { "alias":"$1", "projection":"SUM(r1.op_2)" } ], "groups":[ "r1.page" ], "encoded_group_by_unsupported":"not a columnstore table: r1", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":20833, "avg":5208.250000, "stddev":47.283057, "max":5283, "maxPartition":0 }, "actual_total_time":{ "value":11, "avg":11.000000, "stddev":0.000000, "max":11, "maxPartition":0 }, "start_time":{ "value":734, "avg":735.000000, "stddev":0.000000, "max":736, "maxPartition":2 }, "memory_usage":{ "value":13107200, "avg":3276800.000000, "stddev":0.000000, "max":3276800, "maxPartition":0 }, "inputs":[ { "executor":"TableScan", "table":"r1", "alias":"r1", "storage":"list", "stream":"yes", "est_table_rows":"113283", "actual_row_count":{ "value":53123, "avg":13280.750000, "stddev":140.659829, "max":13443, "maxPartition":1 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":795, "avg":797.500000, "stddev":0.000000, "max":804, "maxPartition":3 }, "end_time":{ "value":795, "avg":797.500000, "stddev":3.774917, "max":804, "maxPartition":3 }, "inputs":[ { "executor":"Repartition", "out":[ { "alias":"", "projection":"gsc_search_analytics.page" }, { "alias":"", "projection":"clicks" }, { "alias":"", "projection":"impressions" }, { "alias":"", "projection":"op_1" }, { "alias":"", "projection":"op_2" } ], "alias":"r1", "shard_key":[ "page" ], "est_rows":"113283", "est_select_cost":"556776", "query":"CREATE RESULT TABLE r1 PARTITION BY (`page`) WITH (PARTITIONS=NULL, READERS=1, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT WITH(LOCAL_SHUFFLE_GROUP_BY=1) `gsc_search_analytics`.`page` AS `page`, SUM(`gsc_search_analytics`.`clicks`) AS `clicks`, SUM(`gsc_search_analytics`.`impressions`) AS `impressions`, SUM((`gsc_search_analytics`.`ctr`*`gsc_search_analytics`.`impressions`)) AS `op_1`, SUM((`gsc_search_analytics`.`position`*`gsc_search_analytics`.`impressions`)) AS `op_2` FROM `laravel_0`.`gsc_search_analytics` as `gsc_search_analytics` WHERE ((`gsc_search_analytics`.`site_id` = 40) AND ISNULL(`gsc_search_analytics`.`country`) AND ISNULL(`gsc_search_analytics`.`device`) AND ISNULL(`gsc_search_analytics`.`query`) AND (`gsc_search_analytics`.`date` BETWEEN '2018-12-31 00:00:00' AND '2019-01-27 00:00:00') AND (NOT ISNULL(`gsc_search_analytics`.`page`)) AND (`gsc_search_analytics`.`search_type` = 'web')) GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":53123, "avg":13280.750000, "stddev":238.876930, "max":13479, "maxPartition":2 }, "actual_total_time":{ "value":29, "avg":29.000000, "stddev":0.000000, "max":29, "maxPartition":0 }, "start_time":{ "value":731, "avg":731.000000, "stddev":0.000000, "max":731, "maxPartition":0 }, "network_traffic":{ "value":4766185, "avg":1191546.250000, "stddev":21254.098339, "max":1210171, "maxPartition":2 }, "network_time":{ "value":18 }, "inputs":[ { "executor":"ShuffleGroupBy", "out":[ { "alias":"clicks", "projection":"SUM(gsc_search_analytics.clicks)" }, { "alias":"impressions", "projection":"SUM(gsc_search_analytics.impressions)" }, { "alias":"op_1", "projection":"SUM(gsc_search_analytics.ctr * gsc_search_analytics.impressions)" }, { "alias":"op_2", "projection":"SUM(gsc_search_analytics.position * gsc_search_analytics.impressions)" } ], "groups":[ "gsc_search_analytics.page" ], "subselects":[], "actual_row_count":{ "value":53123, "avg":13280.750000, "stddev":238.876930, "max":13479, "maxPartition":2 }, "actual_total_time":{ "value":160, "avg":160.000000, "stddev":0.000000, "max":160, "maxPartition":0 }, "start_time":{ "value":69, "avg":141.250000, "stddev":0.000000, "max":245, "maxPartition":1 }, "memory_usage":{ "value":38662944, "avg":9665736.000000, "stddev":3462106.292607, "max":15596744, "maxPartition":2 }, "main_hashtable_processed_rows":{ "value":60518, "avg":15129.500000, "stddev":18599.233069, "max":45558, "maxPartition":2 }, "local_hashtable_processed_rows":{ "value":66425, "avg":16606.250000, "stddev":614.520697, "max":17408, "maxPartition":1 }, "spill_outputted_rows":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "skew_aware_processed_rows":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "main_hashtable_memory_usage":{ "value":9306112, "avg":4653056.000000, "stddev":3473408.000000, "max":8126464, "maxPartition":2 }, "local_hashtable_memory_usage":{ "value":25165856, "avg":6291464.000000, "stddev":92681.900024, "max":6422536, "maxPartition":2 }, "shuffle_memory_usage":{ "value":4190976, "avg":1047744.000000, "stddev":0.000000, "max":1047744, "maxPartition":0 }, "inputs":[ { "executor":"Filter", "condition":[ "gsc_search_analytics.site_id = ? AND gsc_search_analytics.country IS NULL AND gsc_search_analytics.device IS NULL AND gsc_search_analytics.query IS NULL AND gsc_search_analytics.date >= ? AND gsc_search_analytics.date <= ? AND gsc_search_analytics.page IS NOT NULL AND gsc_search_analytics.search_type = ?" ], "subselects":[], "actual_row_count":{ "value":139861, "avg":34965.250000, "stddev":147.218163, "max":35165, "maxPartition":1 }, "actual_total_time":{ "value":12, "avg":12.000000, "stddev":0.000000, "max":12, "maxPartition":0 }, "start_time":{ "value":8, "avg":11.500000, "stddev":0.000000, "max":17, "maxPartition":2 }, "inputs":[ { "executor":"ColumnStoreScan", "db":"laravel", "table":"gsc_search_analytics", "alias":"gsc_search_analytics", "index":"KEY site_id (site_id, date, search_type, country, device, page, query) USING CLUSTERED COLUMNSTORE", "storage":"columnar", "est_table_rows":"373987289", "est_filtered":"139194", "actual_row_count":{ "value":24674347, "avg":6168586.750000, "stddev":870318.484590, "max":6865540, "maxPartition":1 }, "actual_total_time":{ "value":306, "avg":306.000000, "stddev":0.000000, "max":306, "maxPartition":0 }, "start_time":{ "value":2, "avg":4.000000, "stddev":0.000000, "max":8, "maxPartition":2 }, "memory_usage":{ "value":34603008, "avg":8650752.000000, "stddev":393216.000000, "max":9043968, "maxPartition":3 }, "segments_scanned":{ "value":29, "avg":7.250000, "stddev":0.829156, "max":8, "maxPartition":0 }, "segments_skipped":{ "value":351, "avg":87.750000, "stddev":0.829156, "max":89, "maxPartition":2 }, "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "segments_filter_encoded_data":{ "value":29, "avg":7.250000, "stddev":0.829156, "max":8, "maxPartition":0 }, "segments_encoded_group_by":{ "value":22, "avg":5.500000, "stddev":0.866025, "max":7, "maxPartition":1 }, "encoded_group_by_bailout":{ "Number of segments that reached dynamic dictionary size limit":"10" }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ], "version":"2", "info":{ "memsql_version":"6.7.9", "memsql_version_hash":"7f441d7ccdaecb489b02a6eed97521af44ba528d", "num_online_leaves":"1", "num_online_aggs":"1", "context_database":"laravel" } }
Raw
complex.json
{ "profile":[ { "executor":"Top", "offset":"?", "limit":"?", "actual_row_count":{ "value":50 }, "actual_total_time":{ "value":0 }, "inputs":[ { "executor":"GatherMerge", "order":[ "remote_0.clicks DESC" ], "partitions":"all", "est_rows":"50", "query":"SELECT STRAIGHT_JOIN `gsc_search_analytics_1`.`clicks` AS `clicks`, `gsc_search_analytics_1`.`impressions` AS `impressions`, `gsc_search_analytics_1`.`ctr` AS `ctr`, `gsc_search_analytics_1`.`position` AS `position`, `r5`.`latest_snapshot_at` AS `latest_snapshot_at`, `r2`.`id` AS `id`, `r2`.`project_id` AS `project_id`, `r2`.`site_id` AS `site_id`, `r2`.`user_id` AS `user_id`, `r2`.`url_id` AS `url_id`, `r2`.`url` AS `url`, `r2`.`meta_title` AS `meta_title`, `r2`.`meta_description` AS `meta_description`, `r2`.`meta_keywords` AS `meta_keywords`, `r2`.`meta_canonical` AS `meta_canonical`, `r2`.`meta_index` AS `meta_index`, `r2`.`meta_follow` AS `meta_follow`, `r2`.`keyphrases` AS `keyphrases`, `r2`.`status` AS `status`, `r2`.`optimized_at` AS `optimized_at`, `r2`.`crawled_at` AS `crawled_at`, `r2`.`created_at` AS `created_at`, `r2`.`updated_at` AS `updated_at` FROM ((:: `laravel`.`r2` as `r2` STRAIGHT_JOIN ( SELECT WITH(AGG_HINT_MERGE=true) `r0`.`page` AS `page`, SUM(`r0`.`clicks`) AS `clicks`, SUM(`r0`.`impressions`) AS `impressions`, (SUM(`r0`.`op_1`)\/SUM(`r0`.`impressions`)) AS `ctr`, (SUM(`r0`.`op_2`)\/SUM(`r0`.`impressions`)) AS `position` FROM @ `laravel`.REMOTE(:: `laravel`.`r0`(0) AS SELECT (1!:>varchar(2048) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `page`,(1!:>decimal(32,0) NULL) AS `clicks`,(1!:>decimal(32,0) NULL) AS `impressions`,(1!:>double NULL) AS `op_1`,(1!:>double NULL) AS `op_2`) as `r0` GROUP BY 1 ) AS `gsc_search_analytics_1`) LEFT JOIN :: `laravel`.`r5` as `r5` WITH (table_convert_subselect = FALSE) ON (`r5`.`page_id` = `r2`.`id`)) WHERE (`r2`.`url` = `gsc_search_analytics_1`.`page`) ORDER BY 1 DESC LIMIT NULL OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "alias":"remote_0", "actual_row_count":{ "value":200, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":21 }, "end_time":{ "value":162717 }, "inputs":[ { "executor":"Project", "out":[ { "alias":"", "projection":"gsc_search_analytics_1.clicks" }, { "alias":"", "projection":"gsc_search_analytics_1.impressions" }, { "alias":"", "projection":"gsc_search_analytics_1.ctr" }, { "alias":"", "projection":"gsc_search_analytics_1.position" }, { "alias":"", "projection":"r5.latest_snapshot_at" }, { "alias":"", "projection":"r2.id" }, { "alias":"", "projection":"r2.project_id" }, { "alias":"", "projection":"r2.site_id" }, { "alias":"", "projection":"r2.user_id" }, { "alias":"", "projection":"r2.url_id" }, { "alias":"", "projection":"r2.url" }, { "alias":"", "projection":"r2.meta_title" }, { "alias":"", "projection":"r2.meta_description" }, { "alias":"", "projection":"r2.meta_keywords" }, { "alias":"", "projection":"r2.meta_canonical" }, { "alias":"", "projection":"r2.meta_index" }, { "alias":"", "projection":"r2.meta_follow" }, { "alias":"", "projection":"r2.keyphrases" }, { "alias":"", "projection":"r2.status" }, { "alias":"", "projection":"r2.optimized_at" }, { "alias":"", "projection":"r2.crawled_at" }, { "alias":"", "projection":"r2.created_at" }, { "alias":"", "projection":"r2.updated_at" } ], "est_rows":"50", "est_select_cost":"626756", "subselects":[], "actual_row_count":{ "value":200, "avg":50.000000, "stddev":0.000000, "max":50, "maxPartition":0 }, "actual_total_time":{ "value":1, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 }, "start_time":{ "value":162715, "avg":162715.250000, "stddev":0.000000, "max":162716, "maxPartition":1 }, "network_traffic":{ "value":179835, "avg":44958.750000, "stddev":4586.499884, "max":51023, "maxPartition":1 }, "network_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "inputs":[ { "executor":"TopSort", "limit":"?", "order":[ "gsc_search_analytics_1.clicks DESC" ], "actual_row_count":{ "value":200, "avg":50.000000, "stddev":0.000000, "max":50, "maxPartition":0 }, "actual_total_time":{ "value":1, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "memory_usage":{ "value":524288, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 }, "inputs":[ { "executor":"HashJoin", "condition":[ "r5.page_id = r2.id" ], "type":"left", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "inputs":[ { "executor":"HashTableBuild", "alias":"r5", "actual_row_count":{ "value":1, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":22 }, "memory_usage":{ "value":131072 }, "inputs":[ { "executor":"Broadcast", "out":[ { "alias":"", "projection":"r3.page_id" }, { "alias":"", "projection":"latest_snapshot_at" } ], "alias":"r5", "distribution":"tree", "est_rows":"1", "query":"CREATE RESULT TABLE r5 WITH (HASH=(`page_id`), BLOOM_FILTER=false, GEN_MIN_MAX=false, VECTOR_JOIN=false, LEFT_JOIN=false) AS SELECT `r4`.`latest_snapshot_at` AS `latest_snapshot_at`, `r4`.`page_id` AS `page_id` FROM @ `laravel`.REMOTE(:: NULL `laravel`.`r4` AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `page_id`,(1!:>datetime NULL) AS `latest_snapshot_at`) as `r4` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "query2":"CREATE RESULT TABLE r4 WITH (READERS=NULL, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT * FROM @ `laravel`.REMOTE(:: SELECT WITH(AGG_HINT_MERGE=true) `r3`.`page_id` AS `page_id`, MAX(`r3`.`latest_snapshot_at`) AS `latest_snapshot_at` FROM @ `laravel`.REMOTE(:: `laravel`.`r3`(0) AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `page_id`,(1!:>datetime NULL) AS `latest_snapshot_at`) as `r3` GROUP BY 1) AS remote_select OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":1 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":17 }, "network_traffic":{ "value":18 }, "network_time":{ "value":0 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"latest_snapshot_at", "projection":"MAX(r3.latest_snapshot_at)" } ], "groups":[ "r3.page_id" ], "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":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":1 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":17, "avg":17.000000, "stddev":0.000000, "max":17, "maxPartition":1 }, "memory_usage":{ "value":524288, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 }, "inputs":[ { "executor":"TableScan", "table":"r3", "alias":"r3", "storage":"list", "stream":"yes", "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":1 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":17, "avg":17.000000, "stddev":0.000000, "max":17, "maxPartition":1 }, "end_time":{ "value":17, "avg":17.750000, "stddev":0.433013, "max":18, "maxPartition":0 }, "inputs":[ { "executor":"Repartition", "out":[ { "alias":"", "projection":"page_snapshots.page_id" }, { "alias":"", "projection":"latest_snapshot_at" } ], "alias":"r3", "shard_key":[ "page_id" ], "est_rows":"1", "est_select_cost":"4", "query":"CREATE RESULT TABLE r3 PARTITION BY (`page_id`) WITH (PARTITIONS=NULL, READERS=1, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT WITH(LOCAL_SHUFFLE_GROUP_BY=1) `page_snapshots`.`page_id` AS `page_id`, MAX(`page_snapshots`.`created_at`) AS `latest_snapshot_at` FROM `laravel_0`.`page_snapshots` as `page_snapshots` WHERE (`page_snapshots`.`view_id` = 57) GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":2 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.000000, "stddev":0.000000, "max":9, "maxPartition":2 }, "network_traffic":{ "value":18, "avg":4.500000, "stddev":7.794229, "max":18, "maxPartition":2 }, "network_time":{ "value":3 }, "inputs":[ { "executor":"StreamingGroupBy", "out":[ { "alias":"latest_snapshot_at", "projection":"MAX(page_snapshots.created_at)" } ], "groups":[ "page_snapshots.page_id" ], "encoded_group_by_unsupported":"not a columnstore table: page_snapshots", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":2 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.000000, "stddev":0.000000, "max":9, "maxPartition":2 }, "memory_usage":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "inputs":[ { "executor":"Filter", "condition":[ "page_snapshots.view_id = ?" ], "subselects":[], "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":2 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.000000, "stddev":0.000000, "max":9, "maxPartition":2 }, "inputs":[ { "executor":"TableScan", "db":"laravel", "table":"page_snapshots", "alias":"page_snapshots", "index":"KEY page_snapshots_page_id_view_id_created_at_index (page_id, view_id, created_at)", "storage":"lf_skiplist", "dir":"forward", "est_table_rows":"1223", "est_filtered":"1", "actual_row_count":{ "value":1223, "avg":305.750000, "stddev":14.306904, "max":317, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.250000, "stddev":0.000000, "max":10, "maxPartition":3 }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] }, { "executor":"HashJoin", "condition":[ "r2.url = gsc_search_analytics_1.page" ], "type":"inner", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":58, "avg":58.000000, "stddev":0.000000, "max":58, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "inputs":[ { "executor":"HashTableBuild", "alias":"gsc_search_analytics_1", "actual_row_count":{ "value":70581, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":162415, "avg":162417.250000, "stddev":0.000000, "max":162419, "maxPartition":3 }, "memory_usage":{ "value":14155776, "avg":3538944.000000, "stddev":0.000000, "max":3538944, "maxPartition":0 }, "inputs":[ { "executor":"Project", "out":[ { "alias":"", "projection":"r0.page" }, { "alias":"", "projection":"clicks" }, { "alias":"", "projection":"impressions" }, { "alias":"ctr", "projection":"$0 \/ $2" }, { "alias":"position", "projection":"$1 \/ $3" } ], "est_rows":"311029", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":10, "avg":10.000000, "stddev":0.000000, "max":10, "maxPartition":0 }, "start_time":{ "value":162380, "avg":162381.000000, "stddev":0.000000, "max":162382, "maxPartition":3 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"clicks", "projection":"SUM(r0.clicks)" }, { "alias":"impressions", "projection":"SUM(r0.impressions)" }, { "alias":"$0", "projection":"SUM(r0.op_1)" }, { "alias":"$1", "projection":"SUM(r0.op_2)" } ], "groups":[ "r0.page" ], "encoded_group_by_unsupported":"not a columnstore table: r0", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":26, "avg":26.000000, "stddev":0.000000, "max":26, "maxPartition":0 }, "start_time":{ "value":162224, "avg":162224.500000, "stddev":0.000000, "max":162226, "maxPartition":1 }, "memory_usage":{ "value":45350944, "avg":11337736.000000, "stddev":65536.000000, "max":11403272, "maxPartition":0 }, "inputs":[ { "executor":"TableScan", "table":"r0", "alias":"r0", "storage":"list", "stream":"yes", "actual_row_count":{ "value":111897, "avg":27974.250000, "stddev":175.461356, "max":28225, "maxPartition":1 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":162380, "avg":162381.000000, "stddev":0.000000, "max":162382, "maxPartition":3 }, "end_time":{ "value":162380, "avg":162381.000000, "stddev":0.707107, "max":162382, "maxPartition":3 }, "inputs":[ { "executor":"Repartition", "out":[ { "alias":"", "projection":"gsc_search_analytics.page" }, { "alias":"", "projection":"clicks" }, { "alias":"", "projection":"impressions" }, { "alias":"", "projection":"op_1" }, { "alias":"", "projection":"op_2" } ], "alias":"r0", "shard_key":[ "page" ], "est_rows":"311029", "est_select_cost":"4038320", "query":"CREATE RESULT TABLE r0 PARTITION BY (`page`) WITH (PARTITIONS=NULL, READERS=1, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT WITH(LOCAL_SHUFFLE_GROUP_BY=1) `gsc_search_analytics`.`page` AS `page`, SUM(`gsc_search_analytics`.`clicks`) AS `clicks`, SUM(`gsc_search_analytics`.`impressions`) AS `impressions`, SUM((`gsc_search_analytics`.`ctr`*`gsc_search_analytics`.`impressions`)) AS `op_1`, SUM((`gsc_search_analytics`.`position`*`gsc_search_analytics`.`impressions`)) AS `op_2` FROM `laravel_0`.`gsc_search_analytics` as `gsc_search_analytics` WHERE ((`gsc_search_analytics`.`site_id` = 40) AND (`gsc_search_analytics`.`query` NOT REGEXP 'karkkainen|k\u00c3\u00a4rkk\u00c3\u00a4inen.com|k\u00c3\u00a4rkk\u00c3\u00a4isen verkkokauppa|karkainen|verkkokauppa k\u00c3\u00a4rkk\u00c3\u00a4inen|karkkainen|k\u00c3\u00a4rkk\u00c3\u00a4inen. fi|k\u00c3\u00a4rkk\u00c3\u00b6inen|k\u00c3\u00a4rkk\u00c3\u00a4inrn|k\u00c3\u00a4rkk\u00c3\u00a4inen.com|k\u00c3\u00a4rkk\u00c3\u00a4ine|j k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inem|karkkainen.fi|j.k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inn|www.k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4onen|tavaratalo k\u00c3\u00a4rkk\u00c3\u00a4inen|krkk\u00c3\u00a4inen|karkainen|k\u00c3\u00a4rkk\u00c3\u00a4ine|k\u00c3\u00a4rkk\u00c3\u00a4inem|k\u00c3\u00a4rkk\u00c3\u00a4nen|www.karkkainen.fi|k\u00c3\u00a4rkk\u00c3\u00a4isen nettikauppa|nettikauppa k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00b6inen|k\u00c3\u00a4rkk\u00c3\u00a4inrn|k\u00c3\u00a4rkl\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inwn|k\u00c3\u00a4rkk\u00c3\u00a4imen|k\u00c3\u00a4rkk\u00c3\u00a4inen.|k\u00c3\u00a4kk\u00c3\u00a4inen|k\u00c3\u00a4ekk\u00c3\u00a4inen|k\u00c3\u00a4ekk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inem|k\u00c3\u00a4rkk\u00c3\u00a4nen') AND ISNULL(`gsc_search_analytics`.`device`) AND (`gsc_search_analytics`.`date` BETWEEN '2018-12-31 00:00:00' AND '2019-01-27 00:00:00') AND (NOT ISNULL(`gsc_search_analytics`.`page`)) AND (`gsc_search_analytics`.`country` = 'fin') AND (`gsc_search_analytics`.`search_type` = 'web')) GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":111897, "avg":27974.250000, "stddev":1907.092994, "max":30931, "maxPartition":1 }, "actual_total_time":{ "value":27, "avg":27.000000, "stddev":0.000000, "max":27, "maxPartition":0 }, "start_time":{ "value":162220, "avg":162220.500000, "stddev":0.000000, "max":162222, "maxPartition":0 }, "network_traffic":{ "value":10124230, "avg":2531057.500000, "stddev":166397.656176, "max":2788157, "maxPartition":1 }, "network_time":{ "value":4 }, "inputs":[ { "executor":"ShuffleGroupBy", "out":[ { "alias":"clicks", "projection":"SUM(gsc_search_analytics.clicks)" }, { "alias":"impressions", "projection":"SUM(gsc_search_analytics.impressions)" }, { "alias":"op_1", "projection":"SUM(gsc_search_analytics.ctr * gsc_search_analytics.impressions)" }, { "alias":"op_2", "projection":"SUM(gsc_search_analytics.position * gsc_search_analytics.impressions)" } ], "groups":[ "gsc_search_analytics.page" ], "subselects":[], "actual_row_count":{ "value":111897, "avg":27974.250000, "stddev":1907.092994, "max":30931, "maxPartition":1 }, "actual_total_time":{ "value":2624, "avg":2624.000000, "stddev":0.000000, "max":2624, "maxPartition":0 }, "start_time":{ "value":27187, "avg":28495.500000, "stddev":0.000000, "max":29144, "maxPartition":3 }, "memory_usage":{ "value":85186496, "avg":21296624.000000, "stddev":14348337.933405, "max":46135544, "maxPartition":1 }, "main_hashtable_processed_rows":{ "value":923026, "avg":230756.500000, "stddev":155326.300596, "max":486785, "maxPartition":1 }, "local_hashtable_processed_rows":{ "value":66985, "avg":16746.250000, "stddev":3026.930242, "max":21062, "maxPartition":1 }, "spill_outputted_rows":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "skew_aware_processed_rows":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "main_hashtable_memory_usage":{ "value":43778112, "avg":10944528.000000, "stddev":13876492.144732, "max":34865216, "maxPartition":1 }, "local_hashtable_memory_usage":{ "value":28311584, "avg":7077896.000000, "stddev":1161300.878732, "max":8912904, "maxPartition":1 }, "shuffle_memory_usage":{ "value":18728424, "avg":4682106.000000, "stddev":927818.391115, "max":6024528, "maxPartition":0 }, "inputs":[ { "executor":"Filter", "condition":[ "gsc_search_analytics.site_id = ? AND gsc_search_analytics.device IS NULL AND gsc_search_analytics.date >= ? AND gsc_search_analytics.date <= ? AND gsc_search_analytics.page IS NOT NULL AND gsc_search_analytics.country = ? AND gsc_search_analytics.search_type = ? AND (gsc_search_analytics.query NOT REGEXP ?)" ], "subselects":[], "actual_row_count":{ "value":1011535, "avg":252883.750000, "stddev":408.273422, "max":253409, "maxPartition":3 }, "actual_total_time":{ "value":8, "avg":8.000000, "stddev":0.000000, "max":8, "maxPartition":0 }, "start_time":{ "value":27181, "avg":28452.750000, "stddev":0.000000, "max":29105, "maxPartition":0 }, "inputs":[ { "executor":"ColumnStoreScan", "db":"laravel", "table":"gsc_search_analytics", "alias":"gsc_search_analytics", "index":"KEY site_id (site_id, date, search_type, country, device, page, query) USING CLUSTERED COLUMNSTORE", "storage":"columnar", "est_table_rows":"373987289", "est_filtered":"1009581", "actual_row_count":{ "value":24674347, "avg":6168586.750000, "stddev":870318.484590, "max":6865540, "maxPartition":1 }, "actual_total_time":{ "value":38180, "avg":38180.000000, "stddev":0.000000, "max":38180, "maxPartition":0 }, "start_time":{ "value":2, "avg":4.000000, "stddev":0.000000, "max":10, "maxPartition":2 }, "memory_usage":{ "value":33292288, "avg":8323072.000000, "stddev":468020.653493, "max":8912896, "maxPartition":3 }, "segments_scanned":{ "value":29, "avg":7.250000, "stddev":0.829156, "max":8, "maxPartition":0 }, "segments_skipped":{ "value":351, "avg":87.750000, "stddev":0.829156, "max":89, "maxPartition":2 }, "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "segments_filter_encoded_data":{ "value":29, "avg":7.250000, "stddev":0.829156, "max":8, "maxPartition":0 }, "segments_encoded_group_by":{ "value":22, "avg":5.500000, "stddev":0.866025, "max":7, "maxPartition":1 }, "encoded_group_by_bailout":{ "Number of segments that reached dynamic dictionary size limit":"22" }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] }, { "executor":"TableScan", "table":"r2", "alias":"r2", "storage":"list", "stream":"yes", "est_table_rows":"78344", "actual_row_count":{ "value":315048, "avg":78762.000000, "stddev":0.000000, "max":78762, "maxPartition":0 }, "actual_total_time":{ "value":68, "avg":68.000000, "stddev":0.000000, "max":68, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "inputs":[ { "executor":"Broadcast", "out":[ { "alias":"", "projection":"pages.id" }, { "alias":"", "projection":"pages.project_id" }, { "alias":"", "projection":"pages.site_id" }, { "alias":"", "projection":"pages.user_id" }, { "alias":"", "projection":"pages.url_id" }, { "alias":"", "projection":"pages.url" }, { "alias":"", "projection":"pages.meta_title" }, { "alias":"", "projection":"pages.meta_description" }, { "alias":"", "projection":"pages.meta_keywords" }, { "alias":"", "projection":"pages.meta_canonical" }, { "alias":"", "projection":"pages.meta_index" }, { "alias":"", "projection":"pages.meta_follow" }, { "alias":"", "projection":"pages.keyphrases" }, { "alias":"", "projection":"pages.status" }, { "alias":"", "projection":"pages.optimized_at" }, { "alias":"", "projection":"pages.crawled_at" }, { "alias":"", "projection":"pages.created_at" }, { "alias":"", "projection":"pages.updated_at" } ], "alias":"r2", "distribution":"tree", "est_rows":"78344", "query":"CREATE RESULT TABLE r2 WITH (READERS=NULL, BUFFERED_ROWS=NULL) AS SELECT `r1`.`id` AS `id`, `r1`.`project_id` AS `project_id`, `r1`.`site_id` AS `site_id`, `r1`.`user_id` AS `user_id`, `r1`.`url_id` AS `url_id`, `r1`.`url` AS `url`, `r1`.`meta_title` AS `meta_title`, `r1`.`meta_description` AS `meta_description`, `r1`.`meta_keywords` AS `meta_keywords`, `r1`.`meta_canonical` AS `meta_canonical`, `r1`.`meta_index` AS `meta_index`, `r1`.`meta_follow` AS `meta_follow`, `r1`.`keyphrases` AS `keyphrases`, `r1`.`status` AS `status`, `r1`.`optimized_at` AS `optimized_at`, `r1`.`crawled_at` AS `crawled_at`, `r1`.`created_at` AS `created_at`, `r1`.`updated_at` AS `updated_at` FROM @ `laravel`.REMOTE(:: NULL `laravel`.`r1` AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `id`,(1!:>bigint(20) unsigned NOT NULL) AS `project_id`,(1!:>bigint(20) unsigned NOT NULL) AS `site_id`,(1!:>bigint(20) unsigned NULL) AS `user_id`,(1!:>char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `url_id`,(1!:>varchar(4096) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL) AS `url`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_title`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_description`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_keywords`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_canonical`,(1!:>tinyint(1) NULL) AS `meta_index`,(1!:>tinyint(1) NULL) AS `meta_follow`,(1!:>JSON COLLATE utf8_bin NULL) AS `keyphrases`,(1!:>varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `status`,(1!:>datetime NULL) AS `optimized_at`,(1!:>datetime NULL) AS `crawled_at`,(1!:>datetime NULL) AS `created_at`,(1!:>datetime NULL) AS `updated_at`) as `r1` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "query2":"CREATE RESULT TABLE r1 WITH (READERS=NULL, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT * FROM @ `laravel`.REMOTE(:: SELECT `pages`.`id` AS `id`, `pages`.`project_id` AS `project_id`, `pages`.`site_id` AS `site_id`, `pages`.`user_id` AS `user_id`, `pages`.`url_id` AS `url_id`, `pages`.`url` AS `url`, `pages`.`meta_title` AS `meta_title`, `pages`.`meta_description` AS `meta_description`, `pages`.`meta_keywords` AS `meta_keywords`, `pages`.`meta_canonical` AS `meta_canonical`, `pages`.`meta_index` AS `meta_index`, `pages`.`meta_follow` AS `meta_follow`, `pages`.`keyphrases` AS `keyphrases`, `pages`.`status` AS `status`, `pages`.`optimized_at` AS `optimized_at`, `pages`.`crawled_at` AS `crawled_at`, `pages`.`created_at` AS `created_at`, `pages`.`updated_at` AS `updated_at` FROM `laravel_0`.`pages` as `pages` WHERE (`pages`.`site_id` = 40)) AS remote_select OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":78762 }, "actual_total_time":{ "value":7 }, "start_time":{ "value":10 }, "network_traffic":{ "value":67005236 }, "network_time":{ "value":66643 }, "inputs":[ { "executor":"Filter", "condition":[ "pages.site_id = ?" ], "subselects":[], "actual_row_count":{ "value":78762, "avg":19690.500000, "stddev":58.849384, "max":19741, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":2, "avg":12.500000, "stddev":0.000000, "max":19, "maxPartition":3 }, "inputs":[ { "executor":"TableScan", "db":"laravel", "table":"pages", "alias":"pages", "index":"PRIMARY KEY (id)", "storage":"lf_skiplist", "dir":"forward", "est_table_rows":"442108", "est_filtered":"78344", "actual_row_count":{ "value":442108, "avg":110527.000000, "stddev":136.742459, "max":110736, "maxPartition":3 }, "actual_total_time":{ "value":53380, "avg":53380.000000, "stddev":0.000000, "max":53380, "maxPartition":0 }, "start_time":{ "value":2, "avg":12.500000, "stddev":0.000000, "max":19, "maxPartition":3 }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ], "version":"2", "info":{ "memsql_version":"6.7.9", "memsql_version_hash":"7f441d7ccdaecb489b02a6eed97521af44ba528d", "num_online_leaves":"1", "num_online_aggs":"1", "context_database":"laravel" } }
Raw
rlike.json
{ "profile":[ { "executor":"Top", "offset":"?", "limit":"?", "actual_row_count":{ "value":50 }, "actual_total_time":{ "value":0 }, "inputs":[ { "executor":"GatherMerge", "order":[ "remote_0.clicks DESC" ], "partitions":"all", "est_rows":"50", "query":"SELECT STRAIGHT_JOIN `gsc_search_analytics_1`.`clicks` AS `clicks`, `gsc_search_analytics_1`.`impressions` AS `impressions`, `gsc_search_analytics_1`.`ctr` AS `ctr`, `gsc_search_analytics_1`.`position` AS `position`, `r5`.`latest_snapshot_at` AS `latest_snapshot_at`, `r2`.`id` AS `id`, `r2`.`project_id` AS `project_id`, `r2`.`site_id` AS `site_id`, `r2`.`user_id` AS `user_id`, `r2`.`url_id` AS `url_id`, `r2`.`url` AS `url`, `r2`.`meta_title` AS `meta_title`, `r2`.`meta_description` AS `meta_description`, `r2`.`meta_keywords` AS `meta_keywords`, `r2`.`meta_canonical` AS `meta_canonical`, `r2`.`meta_index` AS `meta_index`, `r2`.`meta_follow` AS `meta_follow`, `r2`.`keyphrases` AS `keyphrases`, `r2`.`status` AS `status`, `r2`.`optimized_at` AS `optimized_at`, `r2`.`crawled_at` AS `crawled_at`, `r2`.`created_at` AS `created_at`, `r2`.`updated_at` AS `updated_at` FROM ((:: `laravel`.`r2` as `r2` STRAIGHT_JOIN ( SELECT WITH(AGG_HINT_MERGE=true) `r0`.`page` AS `page`, SUM(`r0`.`clicks`) AS `clicks`, SUM(`r0`.`impressions`) AS `impressions`, (SUM(`r0`.`op_1`)\/SUM(`r0`.`impressions`)) AS `ctr`, (SUM(`r0`.`op_2`)\/SUM(`r0`.`impressions`)) AS `position` FROM @ `laravel`.REMOTE(:: `laravel`.`r0`(0) AS SELECT (1!:>varchar(2048) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `page`,(1!:>decimal(32,0) NULL) AS `clicks`,(1!:>decimal(32,0) NULL) AS `impressions`,(1!:>double NULL) AS `op_1`,(1!:>double NULL) AS `op_2`) as `r0` GROUP BY 1 ) AS `gsc_search_analytics_1`) LEFT JOIN :: `laravel`.`r5` as `r5` WITH (table_convert_subselect = FALSE) ON (`r5`.`page_id` = `r2`.`id`)) WHERE (`r2`.`url` = `gsc_search_analytics_1`.`page`) ORDER BY 1 DESC LIMIT NULL OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "alias":"remote_0", "actual_row_count":{ "value":200, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":21 }, "end_time":{ "value":162717 }, "inputs":[ { "executor":"Project", "out":[ { "alias":"", "projection":"gsc_search_analytics_1.clicks" }, { "alias":"", "projection":"gsc_search_analytics_1.impressions" }, { "alias":"", "projection":"gsc_search_analytics_1.ctr" }, { "alias":"", "projection":"gsc_search_analytics_1.position" }, { "alias":"", "projection":"r5.latest_snapshot_at" }, { "alias":"", "projection":"r2.id" }, { "alias":"", "projection":"r2.project_id" }, { "alias":"", "projection":"r2.site_id" }, { "alias":"", "projection":"r2.user_id" }, { "alias":"", "projection":"r2.url_id" }, { "alias":"", "projection":"r2.url" }, { "alias":"", "projection":"r2.meta_title" }, { "alias":"", "projection":"r2.meta_description" }, { "alias":"", "projection":"r2.meta_keywords" }, { "alias":"", "projection":"r2.meta_canonical" }, { "alias":"", "projection":"r2.meta_index" }, { "alias":"", "projection":"r2.meta_follow" }, { "alias":"", "projection":"r2.keyphrases" }, { "alias":"", "projection":"r2.status" }, { "alias":"", "projection":"r2.optimized_at" }, { "alias":"", "projection":"r2.crawled_at" }, { "alias":"", "projection":"r2.created_at" }, { "alias":"", "projection":"r2.updated_at" } ], "est_rows":"50", "est_select_cost":"626756", "subselects":[], "actual_row_count":{ "value":200, "avg":50.000000, "stddev":0.000000, "max":50, "maxPartition":0 }, "actual_total_time":{ "value":1, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 }, "start_time":{ "value":162715, "avg":162715.250000, "stddev":0.000000, "max":162716, "maxPartition":1 }, "network_traffic":{ "value":179835, "avg":44958.750000, "stddev":4586.499884, "max":51023, "maxPartition":1 }, "network_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "inputs":[ { "executor":"TopSort", "limit":"?", "order":[ "gsc_search_analytics_1.clicks DESC" ], "actual_row_count":{ "value":200, "avg":50.000000, "stddev":0.000000, "max":50, "maxPartition":0 }, "actual_total_time":{ "value":1, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "memory_usage":{ "value":524288, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 }, "inputs":[ { "executor":"HashJoin", "condition":[ "r5.page_id = r2.id" ], "type":"left", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "inputs":[ { "executor":"HashTableBuild", "alias":"r5", "actual_row_count":{ "value":1, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":22 }, "memory_usage":{ "value":131072 }, "inputs":[ { "executor":"Broadcast", "out":[ { "alias":"", "projection":"r3.page_id" }, { "alias":"", "projection":"latest_snapshot_at" } ], "alias":"r5", "distribution":"tree", "est_rows":"1", "query":"CREATE RESULT TABLE r5 WITH (HASH=(`page_id`), BLOOM_FILTER=false, GEN_MIN_MAX=false, VECTOR_JOIN=false, LEFT_JOIN=false) AS SELECT `r4`.`latest_snapshot_at` AS `latest_snapshot_at`, `r4`.`page_id` AS `page_id` FROM @ `laravel`.REMOTE(:: NULL `laravel`.`r4` AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `page_id`,(1!:>datetime NULL) AS `latest_snapshot_at`) as `r4` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "query2":"CREATE RESULT TABLE r4 WITH (READERS=NULL, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT * FROM @ `laravel`.REMOTE(:: SELECT WITH(AGG_HINT_MERGE=true) `r3`.`page_id` AS `page_id`, MAX(`r3`.`latest_snapshot_at`) AS `latest_snapshot_at` FROM @ `laravel`.REMOTE(:: `laravel`.`r3`(0) AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `page_id`,(1!:>datetime NULL) AS `latest_snapshot_at`) as `r3` GROUP BY 1) AS remote_select OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":1 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":17 }, "network_traffic":{ "value":18 }, "network_time":{ "value":0 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"latest_snapshot_at", "projection":"MAX(r3.latest_snapshot_at)" } ], "groups":[ "r3.page_id" ], "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":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":1 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":17, "avg":17.000000, "stddev":0.000000, "max":17, "maxPartition":1 }, "memory_usage":{ "value":524288, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 }, "inputs":[ { "executor":"TableScan", "table":"r3", "alias":"r3", "storage":"list", "stream":"yes", "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":1 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":17, "avg":17.000000, "stddev":0.000000, "max":17, "maxPartition":1 }, "end_time":{ "value":17, "avg":17.750000, "stddev":0.433013, "max":18, "maxPartition":0 }, "inputs":[ { "executor":"Repartition", "out":[ { "alias":"", "projection":"page_snapshots.page_id" }, { "alias":"", "projection":"latest_snapshot_at" } ], "alias":"r3", "shard_key":[ "page_id" ], "est_rows":"1", "est_select_cost":"4", "query":"CREATE RESULT TABLE r3 PARTITION BY (`page_id`) WITH (PARTITIONS=NULL, READERS=1, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT WITH(LOCAL_SHUFFLE_GROUP_BY=1) `page_snapshots`.`page_id` AS `page_id`, MAX(`page_snapshots`.`created_at`) AS `latest_snapshot_at` FROM `laravel_0`.`page_snapshots` as `page_snapshots` WHERE (`page_snapshots`.`view_id` = 57) GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":2 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.000000, "stddev":0.000000, "max":9, "maxPartition":2 }, "network_traffic":{ "value":18, "avg":4.500000, "stddev":7.794229, "max":18, "maxPartition":2 }, "network_time":{ "value":3 }, "inputs":[ { "executor":"StreamingGroupBy", "out":[ { "alias":"latest_snapshot_at", "projection":"MAX(page_snapshots.created_at)" } ], "groups":[ "page_snapshots.page_id" ], "encoded_group_by_unsupported":"not a columnstore table: page_snapshots", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":2 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.000000, "stddev":0.000000, "max":9, "maxPartition":2 }, "memory_usage":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "inputs":[ { "executor":"Filter", "condition":[ "page_snapshots.view_id = ?" ], "subselects":[], "actual_row_count":{ "value":1, "avg":0.250000, "stddev":0.433013, "max":1, "maxPartition":2 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.000000, "stddev":0.000000, "max":9, "maxPartition":2 }, "inputs":[ { "executor":"TableScan", "db":"laravel", "table":"page_snapshots", "alias":"page_snapshots", "index":"KEY page_snapshots_page_id_view_id_created_at_index (page_id, view_id, created_at)", "storage":"lf_skiplist", "dir":"forward", "est_table_rows":"1223", "est_filtered":"1", "actual_row_count":{ "value":1223, "avg":305.750000, "stddev":14.306904, "max":317, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":9, "avg":9.250000, "stddev":0.000000, "max":10, "maxPartition":3 }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] }, { "executor":"HashJoin", "condition":[ "r2.url = gsc_search_analytics_1.page" ], "type":"inner", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":58, "avg":58.000000, "stddev":0.000000, "max":58, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "inputs":[ { "executor":"HashTableBuild", "alias":"gsc_search_analytics_1", "actual_row_count":{ "value":70581, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":162415, "avg":162417.250000, "stddev":0.000000, "max":162419, "maxPartition":3 }, "memory_usage":{ "value":14155776, "avg":3538944.000000, "stddev":0.000000, "max":3538944, "maxPartition":0 }, "inputs":[ { "executor":"Project", "out":[ { "alias":"", "projection":"r0.page" }, { "alias":"", "projection":"clicks" }, { "alias":"", "projection":"impressions" }, { "alias":"ctr", "projection":"$0 \/ $2" }, { "alias":"position", "projection":"$1 \/ $3" } ], "est_rows":"311029", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":10, "avg":10.000000, "stddev":0.000000, "max":10, "maxPartition":0 }, "start_time":{ "value":162380, "avg":162381.000000, "stddev":0.000000, "max":162382, "maxPartition":3 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"clicks", "projection":"SUM(r0.clicks)" }, { "alias":"impressions", "projection":"SUM(r0.impressions)" }, { "alias":"$0", "projection":"SUM(r0.op_1)" }, { "alias":"$1", "projection":"SUM(r0.op_2)" } ], "groups":[ "r0.page" ], "encoded_group_by_unsupported":"not a columnstore table: r0", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":70581, "avg":17645.250000, "stddev":95.387041, "max":17740, "maxPartition":0 }, "actual_total_time":{ "value":26, "avg":26.000000, "stddev":0.000000, "max":26, "maxPartition":0 }, "start_time":{ "value":162224, "avg":162224.500000, "stddev":0.000000, "max":162226, "maxPartition":1 }, "memory_usage":{ "value":45350944, "avg":11337736.000000, "stddev":65536.000000, "max":11403272, "maxPartition":0 }, "inputs":[ { "executor":"TableScan", "table":"r0", "alias":"r0", "storage":"list", "stream":"yes", "actual_row_count":{ "value":111897, "avg":27974.250000, "stddev":175.461356, "max":28225, "maxPartition":1 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":162380, "avg":162381.000000, "stddev":0.000000, "max":162382, "maxPartition":3 }, "end_time":{ "value":162380, "avg":162381.000000, "stddev":0.707107, "max":162382, "maxPartition":3 }, "inputs":[ { "executor":"Repartition", "out":[ { "alias":"", "projection":"gsc_search_analytics.page" }, { "alias":"", "projection":"clicks" }, { "alias":"", "projection":"impressions" }, { "alias":"", "projection":"op_1" }, { "alias":"", "projection":"op_2" } ], "alias":"r0", "shard_key":[ "page" ], "est_rows":"311029", "est_select_cost":"4038320", "query":"CREATE RESULT TABLE r0 PARTITION BY (`page`) WITH (PARTITIONS=NULL, READERS=1, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT WITH(LOCAL_SHUFFLE_GROUP_BY=1) `gsc_search_analytics`.`page` AS `page`, SUM(`gsc_search_analytics`.`clicks`) AS `clicks`, SUM(`gsc_search_analytics`.`impressions`) AS `impressions`, SUM((`gsc_search_analytics`.`ctr`*`gsc_search_analytics`.`impressions`)) AS `op_1`, SUM((`gsc_search_analytics`.`position`*`gsc_search_analytics`.`impressions`)) AS `op_2` FROM `laravel_0`.`gsc_search_analytics` as `gsc_search_analytics` WHERE ((`gsc_search_analytics`.`site_id` = 40) AND (`gsc_search_analytics`.`query` NOT REGEXP 'karkkainen|k\u00c3\u00a4rkk\u00c3\u00a4inen.com|k\u00c3\u00a4rkk\u00c3\u00a4isen verkkokauppa|karkainen|verkkokauppa k\u00c3\u00a4rkk\u00c3\u00a4inen|karkkainen|k\u00c3\u00a4rkk\u00c3\u00a4inen. fi|k\u00c3\u00a4rkk\u00c3\u00b6inen|k\u00c3\u00a4rkk\u00c3\u00a4inrn|k\u00c3\u00a4rkk\u00c3\u00a4inen.com|k\u00c3\u00a4rkk\u00c3\u00a4ine|j k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inem|karkkainen.fi|j.k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inn|www.k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4onen|tavaratalo k\u00c3\u00a4rkk\u00c3\u00a4inen|krkk\u00c3\u00a4inen|karkainen|k\u00c3\u00a4rkk\u00c3\u00a4ine|k\u00c3\u00a4rkk\u00c3\u00a4inem|k\u00c3\u00a4rkk\u00c3\u00a4nen|www.karkkainen.fi|k\u00c3\u00a4rkk\u00c3\u00a4isen nettikauppa|nettikauppa k\u00c3\u00a4rkk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00b6inen|k\u00c3\u00a4rkk\u00c3\u00a4inrn|k\u00c3\u00a4rkl\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inwn|k\u00c3\u00a4rkk\u00c3\u00a4imen|k\u00c3\u00a4rkk\u00c3\u00a4inen.|k\u00c3\u00a4kk\u00c3\u00a4inen|k\u00c3\u00a4ekk\u00c3\u00a4inen|k\u00c3\u00a4ekk\u00c3\u00a4inen|k\u00c3\u00a4rkk\u00c3\u00a4inem|k\u00c3\u00a4rkk\u00c3\u00a4nen') AND ISNULL(`gsc_search_analytics`.`device`) AND (`gsc_search_analytics`.`date` BETWEEN '2018-12-31 00:00:00' AND '2019-01-27 00:00:00') AND (NOT ISNULL(`gsc_search_analytics`.`page`)) AND (`gsc_search_analytics`.`country` = 'fin') AND (`gsc_search_analytics`.`search_type` = 'web')) GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":111897, "avg":27974.250000, "stddev":1907.092994, "max":30931, "maxPartition":1 }, "actual_total_time":{ "value":27, "avg":27.000000, "stddev":0.000000, "max":27, "maxPartition":0 }, "start_time":{ "value":162220, "avg":162220.500000, "stddev":0.000000, "max":162222, "maxPartition":0 }, "network_traffic":{ "value":10124230, "avg":2531057.500000, "stddev":166397.656176, "max":2788157, "maxPartition":1 }, "network_time":{ "value":4 }, "inputs":[ { "executor":"ShuffleGroupBy", "out":[ { "alias":"clicks", "projection":"SUM(gsc_search_analytics.clicks)" }, { "alias":"impressions", "projection":"SUM(gsc_search_analytics.impressions)" }, { "alias":"op_1", "projection":"SUM(gsc_search_analytics.ctr * gsc_search_analytics.impressions)" }, { "alias":"op_2", "projection":"SUM(gsc_search_analytics.position * gsc_search_analytics.impressions)" } ], "groups":[ "gsc_search_analytics.page" ], "subselects":[], "actual_row_count":{ "value":111897, "avg":27974.250000, "stddev":1907.092994, "max":30931, "maxPartition":1 }, "actual_total_time":{ "value":2624, "avg":2624.000000, "stddev":0.000000, "max":2624, "maxPartition":0 }, "start_time":{ "value":27187, "avg":28495.500000, "stddev":0.000000, "max":29144, "maxPartition":3 }, "memory_usage":{ "value":85186496, "avg":21296624.000000, "stddev":14348337.933405, "max":46135544, "maxPartition":1 }, "main_hashtable_processed_rows":{ "value":923026, "avg":230756.500000, "stddev":155326.300596, "max":486785, "maxPartition":1 }, "local_hashtable_processed_rows":{ "value":66985, "avg":16746.250000, "stddev":3026.930242, "max":21062, "maxPartition":1 }, "spill_outputted_rows":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "skew_aware_processed_rows":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "main_hashtable_memory_usage":{ "value":43778112, "avg":10944528.000000, "stddev":13876492.144732, "max":34865216, "maxPartition":1 }, "local_hashtable_memory_usage":{ "value":28311584, "avg":7077896.000000, "stddev":1161300.878732, "max":8912904, "maxPartition":1 }, "shuffle_memory_usage":{ "value":18728424, "avg":4682106.000000, "stddev":927818.391115, "max":6024528, "maxPartition":0 }, "inputs":[ { "executor":"Filter", "condition":[ "gsc_search_analytics.site_id = ? AND gsc_search_analytics.device IS NULL AND gsc_search_analytics.date >= ? AND gsc_search_analytics.date <= ? AND gsc_search_analytics.page IS NOT NULL AND gsc_search_analytics.country = ? AND gsc_search_analytics.search_type = ? AND (gsc_search_analytics.query NOT REGEXP ?)" ], "subselects":[], "actual_row_count":{ "value":1011535, "avg":252883.750000, "stddev":408.273422, "max":253409, "maxPartition":3 }, "actual_total_time":{ "value":8, "avg":8.000000, "stddev":0.000000, "max":8, "maxPartition":0 }, "start_time":{ "value":27181, "avg":28452.750000, "stddev":0.000000, "max":29105, "maxPartition":0 }, "inputs":[ { "executor":"ColumnStoreScan", "db":"laravel", "table":"gsc_search_analytics", "alias":"gsc_search_analytics", "index":"KEY site_id (site_id, date, search_type, country, device, page, query) USING CLUSTERED COLUMNSTORE", "storage":"columnar", "est_table_rows":"373987289", "est_filtered":"1009581", "actual_row_count":{ "value":24674347, "avg":6168586.750000, "stddev":870318.484590, "max":6865540, "maxPartition":1 }, "actual_total_time":{ "value":38180, "avg":38180.000000, "stddev":0.000000, "max":38180, "maxPartition":0 }, "start_time":{ "value":2, "avg":4.000000, "stddev":0.000000, "max":10, "maxPartition":2 }, "memory_usage":{ "value":33292288, "avg":8323072.000000, "stddev":468020.653493, "max":8912896, "maxPartition":3 }, "segments_scanned":{ "value":29, "avg":7.250000, "stddev":0.829156, "max":8, "maxPartition":0 }, "segments_skipped":{ "value":351, "avg":87.750000, "stddev":0.829156, "max":89, "maxPartition":2 }, "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "segments_filter_encoded_data":{ "value":29, "avg":7.250000, "stddev":0.829156, "max":8, "maxPartition":0 }, "segments_encoded_group_by":{ "value":22, "avg":5.500000, "stddev":0.866025, "max":7, "maxPartition":1 }, "encoded_group_by_bailout":{ "Number of segments that reached dynamic dictionary size limit":"22" }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] }, { "executor":"TableScan", "table":"r2", "alias":"r2", "storage":"list", "stream":"yes", "est_table_rows":"78344", "actual_row_count":{ "value":315048, "avg":78762.000000, "stddev":0.000000, "max":78762, "maxPartition":0 }, "actual_total_time":{ "value":68, "avg":68.000000, "stddev":0.000000, "max":68, "maxPartition":0 }, "start_time":{ "value":162418, "avg":162419.750000, "stddev":0.000000, "max":162422, "maxPartition":3 }, "inputs":[ { "executor":"Broadcast", "out":[ { "alias":"", "projection":"pages.id" }, { "alias":"", "projection":"pages.project_id" }, { "alias":"", "projection":"pages.site_id" }, { "alias":"", "projection":"pages.user_id" }, { "alias":"", "projection":"pages.url_id" }, { "alias":"", "projection":"pages.url" }, { "alias":"", "projection":"pages.meta_title" }, { "alias":"", "projection":"pages.meta_description" }, { "alias":"", "projection":"pages.meta_keywords" }, { "alias":"", "projection":"pages.meta_canonical" }, { "alias":"", "projection":"pages.meta_index" }, { "alias":"", "projection":"pages.meta_follow" }, { "alias":"", "projection":"pages.keyphrases" }, { "alias":"", "projection":"pages.status" }, { "alias":"", "projection":"pages.optimized_at" }, { "alias":"", "projection":"pages.crawled_at" }, { "alias":"", "projection":"pages.created_at" }, { "alias":"", "projection":"pages.updated_at" } ], "alias":"r2", "distribution":"tree", "est_rows":"78344", "query":"CREATE RESULT TABLE r2 WITH (READERS=NULL, BUFFERED_ROWS=NULL) AS SELECT `r1`.`id` AS `id`, `r1`.`project_id` AS `project_id`, `r1`.`site_id` AS `site_id`, `r1`.`user_id` AS `user_id`, `r1`.`url_id` AS `url_id`, `r1`.`url` AS `url`, `r1`.`meta_title` AS `meta_title`, `r1`.`meta_description` AS `meta_description`, `r1`.`meta_keywords` AS `meta_keywords`, `r1`.`meta_canonical` AS `meta_canonical`, `r1`.`meta_index` AS `meta_index`, `r1`.`meta_follow` AS `meta_follow`, `r1`.`keyphrases` AS `keyphrases`, `r1`.`status` AS `status`, `r1`.`optimized_at` AS `optimized_at`, `r1`.`crawled_at` AS `crawled_at`, `r1`.`created_at` AS `created_at`, `r1`.`updated_at` AS `updated_at` FROM @ `laravel`.REMOTE(:: NULL `laravel`.`r1` AS SELECT (1!:>bigint(20) unsigned NOT NULL) AS `id`,(1!:>bigint(20) unsigned NOT NULL) AS `project_id`,(1!:>bigint(20) unsigned NOT NULL) AS `site_id`,(1!:>bigint(20) unsigned NULL) AS `user_id`,(1!:>char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `url_id`,(1!:>varchar(4096) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL) AS `url`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_title`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_description`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_keywords`,(1!:>text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL) AS `meta_canonical`,(1!:>tinyint(1) NULL) AS `meta_index`,(1!:>tinyint(1) NULL) AS `meta_follow`,(1!:>JSON COLLATE utf8_bin NULL) AS `keyphrases`,(1!:>varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL) AS `status`,(1!:>datetime NULL) AS `optimized_at`,(1!:>datetime NULL) AS `crawled_at`,(1!:>datetime NULL) AS `created_at`,(1!:>datetime NULL) AS `updated_at`) as `r1` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "query2":"CREATE RESULT TABLE r1 WITH (READERS=NULL, TAKEOVER_CONN=TRUE, BUFFERED_ROWS=NULL) AS SELECT * FROM @ `laravel`.REMOTE(:: SELECT `pages`.`id` AS `id`, `pages`.`project_id` AS `project_id`, `pages`.`site_id` AS `site_id`, `pages`.`user_id` AS `user_id`, `pages`.`url_id` AS `url_id`, `pages`.`url` AS `url`, `pages`.`meta_title` AS `meta_title`, `pages`.`meta_description` AS `meta_description`, `pages`.`meta_keywords` AS `meta_keywords`, `pages`.`meta_canonical` AS `meta_canonical`, `pages`.`meta_index` AS `meta_index`, `pages`.`meta_follow` AS `meta_follow`, `pages`.`keyphrases` AS `keyphrases`, `pages`.`status` AS `status`, `pages`.`optimized_at` AS `optimized_at`, `pages`.`crawled_at` AS `crawled_at`, `pages`.`created_at` AS `created_at`, `pages`.`updated_at` AS `updated_at` FROM `laravel_0`.`pages` as `pages` WHERE (`pages`.`site_id` = 40)) AS remote_select OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "actual_row_count":{ "value":78762 }, "actual_total_time":{ "value":7 }, "start_time":{ "value":10 }, "network_traffic":{ "value":67005236 }, "network_time":{ "value":66643 }, "inputs":[ { "executor":"Filter", "condition":[ "pages.site_id = ?" ], "subselects":[], "actual_row_count":{ "value":78762, "avg":19690.500000, "stddev":58.849384, "max":19741, "maxPartition":0 }, "actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "start_time":{ "value":2, "avg":12.500000, "stddev":0.000000, "max":19, "maxPartition":3 }, "inputs":[ { "executor":"TableScan", "db":"laravel", "table":"pages", "alias":"pages", "index":"PRIMARY KEY (id)", "storage":"lf_skiplist", "dir":"forward", "est_table_rows":"442108", "est_filtered":"78344", "actual_row_count":{ "value":442108, "avg":110527.000000, "stddev":136.742459, "max":110736, "maxPartition":3 }, "actual_total_time":{ "value":53380, "avg":53380.000000, "stddev":0.000000, "max":53380, "maxPartition":0 }, "start_time":{ "value":2, "avg":12.500000, "stddev":0.000000, "max":19, "maxPartition":3 }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ], "version":"2", "info":{ "memsql_version":"6.7.9", "memsql_version_hash":"7f441d7ccdaecb489b02a6eed97521af44ba528d", "num_online_leaves":"1", "num_online_aggs":"1", "context_database":"laravel" } }
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment