Query Performance Tools
On this page
Run ANALYZE
The ANALYZE command collects data statistics on a table to facilitate accurate query optimization.ANALYZE your tables after inserting, updating, or deleting large numbers of rows (30% of your table row count is a good rule of thumb).
EXPLAIN
Returns detailed information on how the query is compiled.
PROFILE
Provides detailed resources usage metrics about a query.
Visual Explain
Visual Explain is a feature of SingleStore that allows customers to see a query plan visualized via a graphical interface.
Query Performance Tool Results For The Same Query
Using a join between the people and age tables shown in the shard key and sort key topics, this is what we get when we submit the same query to EXPLAINand PROFILE.
EXPLAIN:
EXPLAIN SELECT people.user, people.first, people.last, age.ageFROM people JOIN age ON people.id = age.age_idORDER BY age.age;
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| GatherMerge [remote_0.age] partitions:all est_rows:8 alias:remote_0 |
| Project [people.user, people.first, people.last, age.age] est_rows:8 est_select_cost:16 |
| TopSort limit:[?] [age.age] |
| HashJoin |
| |---HashTableProbe [people.id = age.age_id] |
| | HashTableBuild alias:age |
| | Project [age_0.age, age_0.age_id] est_rows:8 |
| | ColumnStoreScan demo.age AS age_0, KEY __UNORDERED () USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore est_table_rows:8 est_filtered:8 |
| ColumnStoreScan demo.people, KEY __UNORDERED () USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore est_table_rows:8 est_filtered:8 |
+----------------------------------------------------------------------------------------------------------------------------------------------------+PROFILE:
PROFILE SELECT people.user, people.first, people.last, age.ageFROM people JOIN age ON people.id = age.age_idORDER BY age.age;
+-------------+-----------+------------+-----+
| user | first | last | age |
+-------------+-----------+------------+------
| cjohnson | carrie | johnson | 10 |
| dyarbrough | dennis | yarbrough | 13 |
| pquincy | penelope | quincy | 21 |
| cachebe | chioma | achebe | 29 |
| jmalik | jahid | malik | 35 |
| kharris | keisha | harris | 43 |
| tling | tai | ling | 67 |
| sstevens | samuel | stevens | 72 |
+-------------+-----------+------------+-----+
{
"plan_warnings": {
},
"execution_warnings": {
},
"mpl_path":"\/var\/lib\/memsql\/instance\/plancache\/a91\/Select_profile_age__et_al_a9111431d10dfe90ceed8b3f2b8e9c1d9706242c27af5f7ee4e97d7d3954a29a_6400bba1c33851b2",
"profile":[
{
"executor":"GatherMerge",
"keyId":4295163904,
"order":[
"remote_0.age"
],
"partitions":"all",
"est_rows":"8",
"est_rows_source":"JOIN",
"query":"SELECT STRAIGHT_JOIN `people`.`user` AS `user`, `people`.`first` AS `first`, `people`.`last` AS `last`, `age`.`age` AS `age` FROM (`demo_0`.`people` as `people` STRAIGHT_JOIN ( SELECT WITH(NO_MERGE_THIS_SELECT=1) `age_0`.`age` AS `age`, `age_0`.`age_id` AS `age_id` FROM `demo_0`.`age` as `age_0` ) AS `age`WITH (gen_min_max = TRUE)) WHERE (`people`.`id` = `age`.`age_id`) ORDER BY 4 \/*!90623 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST, CLIENT_FOUND_ROWS=1)*\/",
"alias":"remote_0",
"actual_total_time":{ "value":0 },
"start_time":{ "value":0 },
"end_time":{ "value":1 },
"actual_row_count":{ "value":8, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"inputs":[
{
"executor":"Project",
"keyId":196736,
"out":[
{
"alias":"",
"projection":"people.user"
},
{
"alias":"",
"projection":"people.first"
},
{
"alias":"",
"projection":"people.last"
},
{
"alias":"",
"projection":"age.age"
}
],
"est_rows":"8",
"est_rows_source":"JOIN",
"est_select_cost":"16",
"subselects":[],
"actual_row_count":{ "value":8, "avg":4.000000, "stddev":1.000000, "max":5, "maxPartition":0 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"network_traffic":{ "value":202, "avg":101.000000, "stddev":18.000000, "max":119, "maxPartition":0 },
"network_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"inputs":[
{
"executor":"Sort",
"keyId":196744,
"order":[
"age.age"
],
"actual_row_count":{ "value":8, "avg":4.000000, "stddev":1.000000, "max":5, "maxPartition":0 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"memory_usage":{ "value":785424, "avg":392712.000000, "stddev":0.000000, "max":392712, "maxPartition":0 },
"inputs":[
{
"executor":"HashJoin",
"keyId":327681,
"type":"inner",
"subselects":[],
"actual_row_count":{ "value":8, "avg":4.000000, "stddev":1.000000, "max":5, "maxPartition":0 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"inputs":[
{
"executor":"HashTableProbe",
"keyId":327825,
"condition":[
"people.id = age.age_id"
],
"inputs":[
{
"executor":"HashTableBuild",
"keyId":327824,
"alias":"age",
"actual_row_count":{ "value":8, "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":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"memory_usage":{ "value":262144, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 },
"inputs":[
{
"executor":"Project",
"keyId":327808,
"out":[
{
"alias":"",
"projection":"age_0.age"
},
{
"alias":"",
"projection":"age_0.age_id"
}
],
"est_rows":"8",
"est_rows_source":"JOIN",
"subselects":[],
"actual_row_count":{ "value":8, "avg":4.000000, "stddev":1.000000, "max":5, "maxPartition":0 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"inputs":[
{
"executor":"ColumnStoreScan",
"keyId":4295360512,
"db":"demo",
"table":"age",
"alias":"age_0",
"index":"KEY __UNORDERED () USING CLUSTERED COLUMNSTORE",
"storage":"columnar",
"table_type":"sharded_columnstore",
"columnstore_in_memory_scan_type":"TableScan",
"columnstore_in_memory_scan_index":"KEY __UNORDERED () USING CLUSTERED COLUMNSTORE",
"est_table_rows":"8",
"est_filtered":"8",
"est_filtered_source":"DEFAULT",
"actual_row_count":{ "value":8, "avg":4.000000, "stddev":1.000000, "max":5, "maxPartition":0 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"memory_usage":{ "value":262144, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 },
"segments_scanned":{ "value":2, "avg":1.000000, "stddev":0.000000, "max":1, "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 },
"segments_filter_encoded_data":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"blob_fetch_network_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"segments_in_blob_cache":{ "value":2, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 },
"inputs":[]
}
]
}
]
}
]
},
{
"executor":"ColumnStoreScan",
"keyId":4295229440,
"db":"demo",
"table":"people",
"alias":"people",
"index":"KEY __UNORDERED () USING CLUSTERED COLUMNSTORE",
"storage":"columnar",
"table_type":"sharded_columnstore",
"columnstore_in_memory_scan_type":"TableScan",
"columnstore_in_memory_scan_index":"KEY __UNORDERED () USING CLUSTERED COLUMNSTORE",
"est_table_rows":"8",
"est_filtered":"8",
"est_filtered_source":"DEFAULT",
"actual_row_count":{ "value":8, "avg":4.000000, "stddev":1.000000, "max":5, "maxPartition":0 },
"actual_total_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"start_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"memory_usage":{ "value":262144, "avg":131072.000000, "stddev":0.000000, "max":131072, "maxPartition":0 },
"segments_scanned":{ "value":2, "avg":1.000000, "stddev":0.000000, "max":1, "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 },
"segments_filter_encoded_data":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"blob_fetch_network_time":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 },
"segments_in_blob_cache":{ "value":2, "avg":1.000000, "stddev":0.000000, "max":1, "maxPartition":0 },
"inputs":[]
}
]
}
]
}
]
}
]
}
],
"version":"4",
"info":{
"memsql_version":"7.5.8",
"memsql_version_hash":"12c73130aa6881ec53d57b44a654e4bada1a07c5",
"num_online_leaves":"2",
"num_online_aggs":"1",
"context_database":"demo"
},
"query_info":{
"query_text":"PROFILE SELECT people.user, people.first, people.last, age.age\n FROM people\n JOIN age ON people.id = age.age_id\n ORDER BY age.age",
"total_runtime_ms":"2",
"text_profile":"GatherMerge [remote_0.age] partitions:all est_rows:8 alias:remote_0 exec_time: 0ms start_time: 00:00:00.000 end_time: 00:00:00.001 actual_rows: 8\nProject [people.user, people.first, people.last, age.age] est_rows:8 est_select_cost:16 actual_rows: 8 exec_time: 0ms start_time: 00:00:00.000 network_traffic: 0.202000 KB network_time: 0ms\nSort [age.age] actual_rows: 8 exec_time: 0ms start_time: 00:00:00.000 memory_usage: 785.424011 KB\nHashJoin actual_rows: 8 exec_time: 0ms start_time: 00:00:00.000\n|---HashTableProbe [people.id = age.age_id]\n| HashTableBuild alias:age actual_rows: 8 exec_time: 0ms start_time: 00:00:00.000 memory_usage: 262.144012 KB\n| Project [age_0.age, age_0.age_id] est_rows:8 actual_rows: 8 exec_time: 0ms start_time: 00:00:00.000\n| ColumnStoreScan demo.age AS age_0, KEY __UNORDERED () USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore est_table_rows:8 est_filtered:8 actual_rows: 8 exec_time: 0ms start_time: 00:00:00.000 memory_usage: 262.144012 KB segments_scanned: 2 segments_skipped: 0 segments_fully_contained: 0 blob_fetch_network_time: 0ms segments_in_blob_cache: 2\nColumnStoreScan demo.people, KEY __UNORDERED () USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore est_table_rows:8 est_filtered:8 actual_rows: 8 exec_time: 0ms start_time: 00:00:00.000 memory_usage: 262.144012 KB segments_scanned: 2 segments_skipped: 0 segments_fully_contained: 0 blob_fetch_network_time: 0ms segments_in_blob_cache: 2\nCompile Total Time: 0ms\n",
"compile_time_stats":{
"mbc_emission":"0",
"create_mbc_context":"0",
"optimizer_query_rewrites":"0",
"optimizer_stats_analyze":"0",
"optimizer_stats_other":"0",
"optimizer_setting_up_subselect":"0",
"optimizer_distributed_optimizations":"0",
"optimizer_enumerate_temporary_tables":"0",
"optimizer_singlebox_optimizations_agg":"0",
"optimizer_stats_autostats":"0",
"generating_query_mpl":"0",
"generating_user_function_mpl":"0",
"unknown":"0",
"total":"0"
}
}
}Last modified: February 23, 2024