DuckDB EXPLAIN ANALYZE Complete Guide: From Slow Queries to Millisecond Responses

Have you ever experienced this scenario:

• A query returns instantly with 100K rows, but hangs with 10M rows
• Adding a WHERE clause somehow makes it slower
• You can’t tell if the bottleneck is JOIN, aggregation, or scanning

Many data analysts’ first reaction to slow queries is “add an index” or “switch databases.” But in DuckDB’s world, there’s a built-in superpower that helps you pinpoint the exact root cause: EXPLAIN ANALYZE.

In this article, I’ll walk through 5 real-world scenarios, teaching you step-by-step how to use EXPLAIN ANALYZE to find the real reasons behind slow queries—and provide executable optimization strategies.

1. EXPLAIN vs EXPLAIN ANALYZE: Know the Difference

Before diving in, let’s clarify the fundamental difference:

-- View the plan only (query not executed)
EXPLAIN SELECT * FROM sales WHERE amount > 1000;

-- Execute query + output actual time and row counts (recommended!)
EXPLAIN ANALYZE SELECT * FROM sales WHERE amount > 1000;

Key takeaway: For performance issues, always use EXPLAIN ANALYZE. EXPLAIN tells you “what it plans to do,” while EXPLAIN ANALYZE tells you “how long it actually took and how many rows it read.”

2. Scenario One: Finding JOIN Performance Black Holes

Imagine two tables: sales (1M sales records) and products (10K product items).

SELECT p.category, SUM(s.amount) AS total
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.sale_date >= '2026-01-01'
GROUP BY p.category;

Without analysis, you might think this JOIN is fine. But with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT p.category, SUM(s.amount) AS total
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.sale_date >= '2026-01-01'
GROUP BY p.category;

Typical output (simplified):

Project [category, sum(amount)]
  Output: category, sum(amount)
  Estimated Rows: 10
    Hash Join (inner)
      Output: p.category, s.amount, s.product_id
      Hash Inner: s.product_id
      Probe Side:
        Output: p.id, p.category
        Estimated Rows: 10000
          TableScan products
            Total Runtime: 1.2ms
            Total rows: 10,000
      Build Side:
        Output: s.product_id, s.amount, s.sale_date
        Estimated Rows: 500000
          SelectionScan sales
            Filter: sale_date >= '2026-01-01'
            Total Runtime: 345.8ms
            Total rows: 1,200,000
            Rows filtered: 700,000
            Rows kept: 500,000
      Total Runtime: 412.5ms

Reading the output:

1. TableScan products — 1.2ms, 10K rows, very fast ✅
2. SelectionScan sales — 345.8ms, scanned 1.2M rows, filtered 700K ❗
3. Total query time: 412.5ms, with 84% spent on scanning and filtering the sales table

Problem identified: Most time is spent on the sales table scan/filter. If sale_date is clustered, this step would be much faster.

Optimization:

-- Method 1: Physically sort data with CLUSTER BY
CREATE TABLE sales_clustered AS
SELECT * FROM sales CLUSTER BY sale_date;

-- Method 2: Use projection indexes (DuckDB 1.1+)
CREATE PROJECTION sales_date_idx ON sales (sale_date);

-- Re-run EXPLAIN ANALYZE — you'll see SelectionScan becomes IndexScan, dropping from 345ms to a few ms

3. Scenario Two: The Nested Query Performance Trap

Sometimes SQL that “looks reasonable” performs poorly. EXPLAIN ANALYZE reveals the size of intermediate result sets:

-- Approach A: Filter first, then JOIN (recommended)
EXPLAIN ANALYZE
SELECT p.category, SUM(s.amount) AS total
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE s.sale_date >= '2026-01-01'
GROUP BY p.category;

-- Approach B: JOIN first, then filter (may be slow)
EXPLAIN ANALYZE
SELECT category, total
FROM (
    SELECT p.category, SUM(s.amount) AS total
    FROM sales s
    JOIN products p ON s.product_id = p.id
    GROUP BY p.category
) sub
WHERE sub.category = 'Electronics';

Compare the Estimated Rows and actual row counts:

• Approach A: WHERE filters sales first, dramatically reducing JOIN data volume
• Approach B: Joins all data first, then filters — the intermediate result set could be enormous, consuming more memory

Key insight: The larger the gap between Estimated Rows and actual rows, the less accurate the optimizer’s statistics — pay attention to this.

4. Scenario Three: Identifying Unnecessary Computations

EXPLAIN ANALYZE
SELECT 
    product_id,
    amount * 1.13 AS amount_with_tax,
    sale_date,
    UPPER(customer_name) AS customer
FROM sales;

Output shows:

Projection [product_id, amount * 1.13, sale_date, UPPER(customer_name)]
  Total Runtime: 125.3ms
  Tables scanned: 1
  Total rows: 5,000,000

Problem found: UPPER(customer_name) performed case conversion on 5 million rows — but you might not even need that field!

Optimization: Remove unnecessary computations or use LIMIT:

EXPLAIN ANALYZE
SELECT 
    product_id,
    amount * 1.13 AS amount_with_tax,
    sale_date
FROM sales
LIMIT 1000;

With LIMIT, runtime can drop from 125ms to 0.5ms.

5. Scenario Four: Detecting Redundant Scans

This is the most easily overlooked performance killer:

EXPLAIN ANALYZE
SELECT 
    (SELECT AVG(amount) FROM sales WHERE sale_date >= '2026-06-01') AS june_avg,
    (SELECT AVG(amount) FROM sales WHERE sale_date >= '2026-05-01') AS may_avg,
    (SELECT AVG(amount) FROM sales WHERE sale_date >= '2026-04-01') AS april_avg;

In the EXPLAIN ANALYZE output, you’ll see sales scanned 3 times! Each time reading all 5M rows from scratch.

Optimization: Use a single scan with conditional aggregation:

EXPLAIN ANALYZE
SELECT 
    AVG(CASE WHEN sale_date >= '2026-06-01' THEN amount END) AS june_avg,
    AVG(CASE WHEN sale_date >= '2026-05-01' THEN amount END) AS may_avg,
    AVG(CASE WHEN sale_date >= '2026-04-01' THEN amount END) AS april_avg
FROM sales;

Now sales is scanned only once — approximately 3x performance improvement.

6. Scenario Five: Using EXPLAIN ANALYZE in Python

Two ways to use EXPLAIN ANALYZE in Python:

import duckdb
import time

con = duckdb.connect(":memory:")

# Create test data
con.execute("""
    CREATE TABLE sales AS 
    SELECT 
        (random() * 10000)::INT AS product_id,
        (random() * 1000)::DECIMAL(10,2) AS amount,
        date '2026-01-01' + (random() * 365)::INT AS sale_date,
        'Customer_' || (random() * 1000)::INT AS customer_name
    FROM range(5000000)
""")

# Method 1: View directly in SQL
result = con.execute("""
    EXPLAIN ANALYZE
    SELECT p.category, SUM(s.amount)
    FROM sales s
    JOIN products p ON s.product_id = p.id
    WHERE s.sale_date >= '2026-01-01'
    GROUP BY p.category
""").fetchall()

for row in result:
    print(row[0])

# Method 2: Compare different approaches in Python
queries = {
    "Subquery Approach": """
        SELECT 
            (SELECT AVG(amount) FROM sales WHERE sale_date >= '2026-06-01'),
            (SELECT AVG(amount) FROM sales WHERE sale_date >= '2026-05-01')
    """,
    "Conditional Aggregation": """
        SELECT 
            AVG(CASE WHEN sale_date >= '2026-06-01' THEN amount END),
            AVG(CASE WHEN sale_date >= '2026-05-01' THEN amount END)
        FROM sales
    """,
}

for name, sql in queries.items():
    start = time.time()
    con.execute(sql).fetchall()
    elapsed = time.time() - start
    print(f"{name}: {elapsed:.4f}s")

7. Key Metrics in EXPLAIN ANALYZE Output

Learn these indicators and you’ll diagnose problems like a pro:

8. Comparison with Traditional Tools

DuckDB’s advantage: zero configuration, one command, done.

9. Monetization Advice

Mastering EXPLAIN ANALYZE opens several revenue streams:

1. Confident freelance pricing — When you know why your queries are fast, clients will pay for your expertise
2. Performance consulting — Diagnose slow queries for enterprises at ¥500-2000/hour
3. Build a monitoring SaaS — Periodically run EXPLAIN ANALYZE, generate performance reports for small businesses

Remember: The biggest leverage for data analysts isn’t writing faster SQL — it’s helping others save money. If your query is 10x faster, the client’s cloud costs drop by 90%. That’s your justification for charging premium rates.

📖 The complete runnable notebook and more DuckDB performance optimization cases are published at duckdblab.org, including interactive demos and comparison test data.

💡 Want to systematically learn DuckDB performance tuning? duckdblab.org has a complete EXPLAIN ANALYZE tutorial series, from beginner to advanced.