Visualizing EXPLAIN ANALYZE in PostgreSQL

When working with PostgreSQL, understanding how queries execute can greatly improve performance tuning and optimization. PostgreSQL provides the EXPLAIN ANALYZE command to help developers analyze query execution plans. However, reading raw execution plans can be challenging. In this post, we will explore how to interpret EXPLAIN ANALYZE output and visualize it using online tools.

Understanding `EXPLAIN ANALYZE`

The EXPLAIN command in PostgreSQL provides insight into how a query will be executed by the planner. Adding ANALYZE to the command executes the query and provides actual runtime statistics. Here’s an example:

EXPLAIN ANALYZE
SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id;

This query joins the orders and customers tables on customer_id. The output will contain details on join strategy, index usage, and execution time.

Example of `EXPLAIN ANALYZE` Output

Hash Join  (cost=35.00..75.00 rows=1000 width=64) (actual time=0.123..0.456 rows=1000 loops=1)
  Hash Cond: (orders.customer_id = customers.id)
  ->  Seq Scan on orders  (cost=0.00..30.00 rows=1000 width=32) (actual time=0.012..0.045 rows=1000 loops=1)
  ->  Hash  (cost=30.00..30.00 rows=1000 width=32) (actual time=0.098..0.100 rows=1000 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 64kB
        ->  Seq Scan on customers  (cost=0.00..30.00 rows=1000 width=32) (actual time=0.015..0.038 rows=1000 loops=1)
Planning Time: 0.345 ms
Execution Time: 0.678 ms

Breaking Down the Output

Join Type: The Hash Join indicates that PostgreSQL is using a hash-based strategy for joining tables.
Sequential Scans: Seq Scan on orders and Seq Scan on customers indicate full table scans, which may be inefficient.
Execution Time: The query took 0.678 ms to execute, which is useful for performance benchmarking.