Visualizing SQL Query Performance with EXPLAIN Plans

Performance tuning is a critical aspect of working with relational databases. In PostgreSQL, understanding how a query gets executed is the first step to improving its performance. That’s where the EXPLAIN statement comes into play. It provides insight into how the PostgreSQL query planner intends to execute a query and allows you to identify inefficiencies in your SQL.

In this blog post, we’ll dive deep into using EXPLAIN and EXPLAIN ANALYZE with real examples, explore how to interpret different parts of the execution plan, and discover how indexing and query refactoring can lead to significant performance gains.

1. Getting Started with EXPLAIN

The EXPLAIN command shows the execution strategy PostgreSQL uses to run a query without actually running it. Let’s start with a basic setup and query example.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT,
  created_at TIMESTAMP
);

INSERT INTO users (name, email, created_at)
SELECT 'User' || i,
       'user' || i || '@example.com',
       NOW() - (i || ' days')::interval
FROM generate_series(1, 100000) AS s(i);

Now let’s run a simple query:

EXPLAIN SELECT * FROM users WHERE email = 'user50000@example.com';

This might return:

Seq Scan on users  (cost=0.00..1728.00 rows=1 width=72)
  Filter: (email = 'user50000@example.com')

Here, PostgreSQL is doing a sequential scan – inspecting every row to find a match. This is inefficient for large datasets and implies that no useful index exists on the email column.

2. EXPLAIN ANALYZE and Actual Performance

To understand actual performance characteristics such as time and row count, use EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user50000@example.com';

Sample output:

Seq Scan on users  (cost=0.00..1728.00 rows=1 width=72)
  Filter: (email = 'user50000@example.com')
  Rows Removed by Filter: 99999
  Execution Time: 42.123 ms

This reveals that PostgreSQL scanned all 100,000 rows, removing 99,999 irrelevant ones — not ideal. The execution time indicates real slowness under load.

3. Adding Indexes for Optimization

An index can vastly improve performance. Since we’re querying by email, let’s add an index:

CREATE INDEX idx_users_email ON users(email);

Now rerun the same EXPLAIN ANALYZE query:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user50000@example.com';

You should now see:

Index Scan using idx_users_email on users  (cost=0.43..8.45 rows=1 width=72)
  Index Cond: (email = 'user50000@example.com')
  Execution Time: 0.112 ms

This is a dramatic improvement. PostgreSQL uses an index scan, which gives a precise match without scanning all rows. Indexes reduce I/O and improve milliseconds into microseconds — significant at scale.

4. Complex Queries and Nested Plans

EXPLAIN becomes even more helpful on joins and aggregations. Suppose we create another table:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total NUMERIC,
  created_at TIMESTAMP
);

INSERT INTO orders (user_id, total, created_at)
SELECT (RANDOM() * 100000)::int, (RANDOM() * 100)::numeric,
       NOW() - (RANDOM() * 30 || ' days')::interval
FROM generate_series(1, 200000);

Let’s run an aggregate query:

EXPLAIN ANALYZE 
SELECT u.email, COUNT(o.id) total_orders
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.email
ORDER BY total_orders DESC
LIMIT 10;

Now the plan might include steps like Nested Loops, Hash Aggregates, and Sort operations. Interpreting such plans helps us detect bottlenecks like expensive join strategies or large data shuffles. For example, when the planner uses a Nested Loop join on large tables without indexes on join keys, performance suffers.

Pro Tip: Use indexes on foreign keys and where conditions in your JOINs to enable efficient Merge or Hash Join strategies.

5. Visualizing and Analyzing EXPLAIN Output

Complex execution plans can be hard to interpret. Thankfully, there are visualization tools:

Dalibo’s EXPLAIN Visualizer (pev2) – Copy-paste fully formatted EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output.
EXPLAIN FORMAT JSON: For programmatic analysis of plans.

EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM users WHERE email = 'user50000@example.com';

This outputs a machine-readable JSON plan tree, ideal for frontend tools and development dashboards.

Tip: Automate plan capture in your CI/CD or observability pipelines to catch regressions before deploying to production.

Conclusion

PostgreSQL’s EXPLAIN and EXPLAIN ANALYZE provide powerful insights into how queries behave beneath the surface. By learning to read these plans and reacting—whether through smarter indexes or query rewrites—you can eliminate costly operations and drastically improve data access speed.

In performance-critical applications, even microsecond savings per query matter. Start leveraging EXPLAIN as your go-to performance profiling tool in PostgreSQL workflows.

Useful links: