Obtaining Information with EXPLAIN ANALYZE

EXPLAIN ANALYZE is a profiling tool for your queries that will show you where SQL spends time on your query and why. It will plan the query, instrument it and execute it while counting rows and measuring time spent at various points in the execution plan. When execution finishes, EXPLAIN ANALYZE will print the plan and the measurements instead of the query result.

EXPLAIN ANALYZE, which runs a statement and produces EXPLAIN output along with timing and additional, iterator-based, information about how the optimizer’s expectations matched the actual execution. For each iterator, the following information is provided:

  • Estimated execution cost

Some iterators are not accounted for by the cost model, and so are not included in the estimate.

  • Estimated number of returned rows

  • Time to return first row

  • Time spent executing this iterator (including child iterators, but not parent iterators), in milliseconds.

  • Number of rows returned by the iterator

  • Number of loops

The query execution information is displayed using the TREE output format, in which nodes represent iterators. EXPLAIN ANALYZE always uses the TREE output format, also can optionally be specified explicitly using FORMAT=TREE; formats other than TREE remain unsupported.

EXPLAIN ANALYZE can be used with SELECT statements, as well as with multi-table UPDATE and DELETE statements.

You can terminate this statement using KILL QUERY or CTRL-C.

EXPLAIN ANALYZE cannot be used with FOR CONNECTION.

Example

Create table

  1. CREATE TABLE t1 (
  2.     c1 INTEGER DEFAULT NULL,
  3.     c2 INTEGER DEFAULT NULL
  4. );
  6. CREATE TABLE t2 (
  7.     c1 INTEGER DEFAULT NULL,
  8.     c2 INTEGER DEFAULT NULL
  9. );
  11. CREATE TABLE t3 (
  12.     pk INTEGER NOT NULL PRIMARY KEY,
  13.     i INTEGER DEFAULT NULL
  14. );

Example output:

  1. > EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
  2. *************************** 1. row ***************************
  3. QUERY PLAN: Project
  4. *************************** 2. row ***************************
  5. QUERY PLAN:   Analyze: timeConsumed=0us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  6. *************************** 3. row ***************************
  7. QUERY PLAN:   ->  Join
  8. *************************** 4. row ***************************
  9. QUERY PLAN:         Analyze: timeConsumed=5053us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  10. *************************** 5. row ***************************
  11. QUERY PLAN:         Join Type: INNER
  12. *************************** 6. row ***************************
  13. QUERY PLAN:         Join Cond: (t1.c1 = t2.c2)
  14. *************************** 7. row ***************************
  15. QUERY PLAN:         ->  Table Scan on aaa.t1
  16. *************************** 8. row ***************************
  17. QUERY PLAN:               Analyze: timeConsumed=2176us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  18. *************************** 9. row ***************************
  19. QUERY PLAN:         ->  Table Scan on aaa.t2
  20. *************************** 10. row ***************************
  21. QUERY PLAN:               Analyze: timeConsumed=0us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  22. 10 rows in set (0.00 sec)
  24. > EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
  25. *************************** 1. row ***************************
  26. QUERY PLAN: Project
  27. *************************** 2. row ***************************
  28. QUERY PLAN:   Analyze: timeConsumed=0us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  29. *************************** 3. row ***************************
  30. QUERY PLAN:   ->  Table Scan on aaa.t3
  31. *************************** 4. row ***************************
  32. QUERY PLAN:         Analyze: timeConsumed=154us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  33. *************************** 5. row ***************************
  34. QUERY PLAN:         Filter Cond: (CAST(t3.i AS BIGINT) > 8)
  35. 5 rows in set (0.00 sec)
  37. > EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
  38. *************************** 1. row ***************************
  39. QUERY PLAN: Project
  40. *************************** 2. row ***************************
  41. QUERY PLAN:   Analyze: timeConsumed=0us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  42. *************************** 3. row ***************************
  43. QUERY PLAN:   ->  Table Scan on aaa.t3
  44. *************************** 4. row ***************************
  45. QUERY PLAN:         Analyze: timeConsumed=309us inputRows=0 outputRows=0 inputSize=0bytes outputSize=0bytes memorySize=0bytes
  46. *************************** 5. row ***************************
  47. QUERY PLAN:         Filter Cond: (CAST(t3.pk AS BIGINT) > 17)
  48. 5 rows in set (0.00 sec)

Values shown for actual time in the output of this statement are expressed in milliseconds.