ANALYZE FORMAT=JSON Examples

Example #1

Customers who have ordered more than 1M goods.

ANALYZE FORMAT=JSON SELECT COUNT(*) FROM customer WHERE  (SELECT SUM(o_totalprice) FROM orders WHERE o_custkey=c_custkey) > 1000*1000;

The query takes 40 seconds over cold cache.

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 shows that 39.208 seconds were spent in the subquery, which was executed 150K times (for every row of outer table).

_{This page is licensed: CC BY-SA / Gnu FDL}