Review practical examples of ANALYZE FORMAT=JSON output. Learn to identify performance bottlenecks by comparing estimated costs against actual execution metrics.
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.
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
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
}
}
}
]
}
}