Query Performance Tools

Run ANALYZE

The ANALYZE command collects data statistics on a table to facilitate accurate query optimization. This is especially important for optimizing complex analytical queries. You should ANALYZE your tables after inserting, updating, or deleting large numbers of rows (30% of your table row count is a good rule of thumb). See the Statistics and Sampling topic for more information.

EXPLAIN

Returns detailed information on how the query is compiled. See EXPLAIN for more details.

PROFILE

Provides detailed resources usage metrics about a query. See PROFILE for more details.

Visual Explain

Visual Explain is a feature of SingleStore that allows customers to see a query plan visualized via a graphical interface. This is useful for tuning database queries to reduce run-time or resource usage. See SingleStore Visual Explain for more details.

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.age
FROM people JOIN age ON people.id = age.age_id
ORDER 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.age
FROM people JOIN age ON people.id = age.age_id
ORDER 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: January 10, 2024

Was this article helpful?