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.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。