ANALYZE FORMAT=JSON 示例
示例 #1
已订购超过 100 万件商品的客户。
ANALYZE FORMAT=JSON SELECT COUNT(*) FROM customer WHERE (SELECT SUM(o_totalprice) FROM orders WHERE o_custkey=c_custkey) > 1000*1000;
查询在冷缓存上花费了 40 秒。
EXPLAIN: { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 39872, "table": { "table_name": "customer", "access_type": "index", "key": "i_c_nationkey", "key_length": "5", "used_key_parts": ["c_nationkey"], "r_loops": 1, "rows": 150303, "r_rows": 150000, "r_total_time_ms": 270.3, "filtered": 100, "r_filtered": 60.691, "attached_condition": "((subquery#2) > <cache>((1000 * 1000)))", "using_index": true }, "subqueries": [ { "query_block": { "select_id": 2, "r_loops": 150000, "r_total_time_ms": 39531, "table": { "table_name": "orders", "access_type": "ref", "possible_keys": ["i_o_custkey"], "key": "i_o_custkey", "key_length": "5", "used_key_parts": ["o_custkey"], "ref": ["dbt3sf1.customer.c_custkey"], "r_loops": 150000, "rows": 7, "r_rows": 10, "r_total_time_ms": 39208, "filtered": 100, "r_filtered": 100 } } } ] } }
ANALYZE
显示子查询花费了 39.2 秒,它被执行了 150K 次(对于外部表的每一行)。