DuckDB Performance Tuning: EXPLAIN ANALYZE Practical Guide

Have you ever encountered this scenario: a SQL query runs fast on test data but hangs in production? Or the data volume is small, yet the query takes more than 10 seconds?

The traditional approach is to guess the problem, add an index, modify the query, test again, and still find it slow. After hours of trial and error, you might not even find the real issue.

With DuckDB’s EXPLAIN ANALYZE, you can directly “see” the query execution plan and precisely identify performance bottlenecks.

1. EXPLAIN vs EXPLAIN ANALYZE: The Key Differences

Many developers only know how to use EXPLAIN, but miss the power of EXPLAIN ANALYZE.

Core difference: EXPLAIN tells you how it would run, while EXPLAIN ANALYZE tells you how it actually ran. For performance tuning, the latter is the real weapon.

2. Practical Scenario: Slow Query Diagnosis

Assume you have an orders table with 10 million rows. Now you need to execute this query:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
  AND order_date >= DATE '2026-06-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

The query results are correct, but it takes 8 seconds. What’s the problem?

Step 1: Use EXPLAIN to See the Execution Plan

EXPLAIN
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
  AND order_date >= DATE '2026-06-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

Sample Output:

Projection: customer_id, sum(orders.amount) AS total
  OrderBy: sum(orders.amount) DESC NULLS FIRST, LIMIT 100
    Aggregate: sum(orders.amount)
      Filter: (orders.status = 'completed') AND (orders.order_date >= 2026-06-01)
        TableScan on orders

You can see DuckDB chose a full table scan (TableScan), then filter, aggregate, and sort. No index is being used.

Step 2: Use EXPLAIN ANALYZE to See Actual Execution

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
  AND order_date >= DATE '2026-06-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

Key Output Information:

Execution Time: 8.2s
├─ TableScan: 2.3s (read all 10M rows)
├─ Filter: 0.1s (filtered down to 500K rows)
├─ Aggregate: 3.5s (grouped aggregation)
└─ OrderBy: 2.3s (sorting)

Key Findings:

• TableScan orders read all 10 million rows → Bottleneck is here!
• Only 500K rows remain after filtering, but 10M rows were already read

3. Diagnosis: Missing Index

The problem is clear: status and order_date columns have no indexes, causing a full table scan.

Solution: Create a Composite Index

CREATE INDEX idx_orders_status_date ON orders(status, order_date);

Index Column Order Tip: Put equality condition columns first, range condition columns second. This way status = 'completed' can quickly locate rows, and order_date >= '2026-06-01' does range scanning.

Re-run EXPLAIN ANALYZE:

Execution Time: 4.1s
└─ IndexScan: 0.3s (read only 500K rows, instead of 10M)

Improvement Results:

• Execution time reduced from 8.2s to 4.1s, 50% improvement
• Rows read reduced from 10M to 500K, 95% reduction

4. Advanced: Multi-Table JOIN Slow Query Optimization

Original Query (15 seconds)

SELECT
    o.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
  AND o.order_date >= DATE '2026-01-01'
  AND c.region = 'East China'
GROUP BY o.customer_id, c.name
ORDER BY total_amount DESC;

Step 1: Add Indexes

CREATE INDEX idx_orders_status_date ON orders(status, order_date);
CREATE INDEX idx_customers_region ON customers(region);

Step 2: Use Materialized View (If This Query Runs Frequently)

CREATE MATERIALIZED VIEW mv_customer_orders AS
SELECT
    o.customer_id,
    c.name,
    c.region,
    o.status,
    o.order_date,
    o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

CREATE INDEX idx_mv_status_date ON mv_customer_orders(status, order_date);
CREATE INDEX idx_mv_region ON mv_customer_orders(region);

Optimized Query

SELECT
    customer_id,
    name,
    COUNT(order_id) AS order_count,
    SUM(amount) AS total_amount
FROM mv_customer_orders
WHERE status = 'completed'
  AND order_date >= DATE '2026-01-01'
  AND region = 'East China'
GROUP BY customer_id, name
ORDER BY total_amount DESC;

Final Result: 15.2s → 0.5s, 30x improvement!

5. Common Performance Bottlenecks and Solutions

Bottleneck 1: Full Table Scan (TableScan)

-- Create index
CREATE INDEX idx_name ON table(column);

-- Composite index (pay attention to column order)
CREATE INDEX idx_status_date ON orders(status, order_date);

Bottleneck 2: Hash Aggregate Memory Overflow

-- Increase memory limit
SET memory_limit = '4GB';

-- Or use spill to disk (auto-spill)
SET temp_directory = '/tmp/duckdb_temp';

Bottleneck 3: Redundant Computation

-- Use CTE to extract common subqueries
WITH completed_customers AS (
    SELECT DISTINCT customer_id FROM orders WHERE status = 'completed'
)
SELECT o.*
FROM orders o
JOIN completed_customers c ON o.customer_id = c.customer_id;

Bottleneck 4: Large Table JOIN Small Table

-- Use BROADCAST hint to force broadcasting small table
SELECT /*+ BROADCAST(c) */ *
FROM large_table l
JOIN small_table c ON l.id = c.id;

6. Python Integration: Automated Performance Monitoring

import duckdb
import time

def analyze_query_performance(sql: str, db_path: str = "data.duckdb"):
    """Analyze SQL query performance"""
    con = duckdb.connect(db_path)
    
    # Execute query and get execution plan
    explain_result = con.execute(f"EXPLAIN ANALYZE {sql}").fetchall()
    
    # Parse execution time
    execution_time = None
    for row in explain_result:
        if "Execution Time" in str(row):
            execution_time = float(row[0].split(": ")[1].replace("s", ""))
            break
    
    con.close()
    return execution_time, explain_result

# Usage example
sql = """
SELECT customer_id, SUM(amount) 
FROM orders 
WHERE status = 'completed'
GROUP BY customer_id
"""

time_spent, plan = analyze_query_performance(sql)
print(f"Execution time: {time_spent}s")
for row in plan:
    print(row)

7. Performance Optimization Checklist

Before submitting SQL, ask yourself these questions:

1. Are there indexes? — Check WHERE and JOIN condition columns
2. Are you scanning too many rows? — Compare scanned rows vs returned rows
3. Is there redundant computation? — Consider using CTEs or materialized views
4. Is memory sufficient? — Check memory_limit setting
5. Are you using the right JOIN strategy? — Consider BROADCAST for large table JOIN small table

8. Monetization Suggestions

After mastering DuckDB performance optimization skills, you can:

1. Consulting: Help enterprises optimize database queries, charge per project ($2000-10000/project)
2. Training Courses: Create DuckDB performance optimization courses and sell on platforms like Udemy or GeekTime
3. SaaS Tool: Develop SQL performance analysis and optimization suggestion tools
4. Technical Blog: Continuously output in-depth DuckDB content, build personal brand, and attract paying users

Performance optimization is a must-have for data teams. Learning DuckDB’s EXPLAIN ANALYZE gives you the key to high-paying opportunities.

📖 For more in-depth DuckDB performance optimization content, including complete real-world optimization cases, visit duckdblab.org for detailed tutorial series.