1. The Three Layers of DuckDB Query Optimization
Many DuckDB users think optimization stops at partition pruning — store data in Hive-style directories, skip irrelevant files at query time. That’s the first layer.
But partition pruning fails completely when your data lives in a single large Parquet file: external datasets you can’t re-partition, real-time streaming data, or bulk exports from third-party APIs.
That’s when you need Layer 2 and Layer 3:
| Layer | Technique | Use Case | Potential Speedup |
|---|---|---|---|
| Layer 1 · File-level | Hive partitions + Glob paths | Partitionable historical data | 10-365x |
| Layer 2 · Row Group-level | Predicate pushdown + Row group tuning | Large single files / non-partitionable data | 2-15x |
| Layer 3 · Database-level | Filter indexes + materialized tables | High-frequency dashboard queries | 5-100x |
This article focuses on Layers 2 and 3 — the territory most tutorials don’t explore in depth.
2. Layer 2: Row Group-Level Predicate Pushdown
2.1 Inside a Parquet File
Every Parquet file is composed of Row Groups. By default, DuckDB creates row groups of approximately 122,880 rows each when exporting data.
Each row group carries column-level statistics (min, max, null count) stored in the file footer. Predicate pushdown exploits these statistics to skip entire row groups that cannot contain matching data:
┌─────────────────────────────────────────┐
│ Parquet File │
│ ┌───────────────────┐ │
│ │ Row Group 1 │ min=1, max=100 │ ← Skipped (no match)
│ ├───────────────────┤ │
│ │ Row Group 2 │ min=101, max=200│ ← Skipped
│ ├───────────────────┤ │
│ │ Row Group 3 │ min=201, max=300│ ← Read ✓ (contains target data)
│ ├───────────────────┤ │
│ │ Footer (metadata) │ │
│ └───────────────────┘ │
└─────────────────────────────────────────┘
2.2 Verifying Predicate Pushdown with EXPLAIN ANALYZE
DuckDB performs predicate pushdown automatically, but certain SQL patterns block this optimization. Always verify with EXPLAIN ANALYZE:
-- Create test data (10M rows)
CREATE TABLE sales AS
SELECT
range AS id,
'2026-01-01'::DATE + (range % 365) AS trans_date,
(random() * 10000)::INT AS amount
FROM range(10000000);
-- Export as a single large file (simulating non-partitionable data)
COPY sales TO 'sales_big.parquet' (FORMAT PARQUET);
-- Check if predicate pushdown is active
EXPLAIN ANALYZE
SELECT COUNT(*), SUM(amount)
FROM 'sales_big.parquet'
WHERE trans_date >= '2026-06-01' AND trans_date < '2026-07-01';
Look for these signals in the output:
┌─────────────────────────────────────────┐
│ Query Plan │
│ │
│ PROJECTION │
│ │ │
│ HASH_GROUP_BY │
│ │ │
│ PARQUET_SCAN(sales_big.parquet) │
│ │ Filters: trans_date>=... │ ← Pushdown confirmed
│ │ [table: ~80K rows] │ ← Only ~1/12 row groups read
│ └─────────────────────────────────────┘
If you see SEQ_SCAN instead of PARQUET_SCAN, or if the filter appears as a separate node after the scan layer, pushdown is not happening.
2.3 SQL Patterns That Block Predicate Pushdown
Anti-pattern 1: Wrapping columns with functions
-- ❌ Blocks pushdown
WHERE CAST(trans_date AS VARCHAR) LIKE '2026-06-%'
-- ✅ Correct
WHERE trans_date >= '2026-06-01' AND trans_date < '2026-07-01'
Anti-pattern 2: Arithmetic on columns
-- ❌ Blocks pushdown
WHERE trans_date + INTERVAL '1 day' > '2026-06-15'
-- ✅ Correct
WHERE trans_date > '2026-06-14'
Anti-pattern 3: Large IN lists
-- ❌ May block pushdown beyond ~50 elements
WHERE city IN ('Beijing', 'Shanghai', 'Guangzhou', ..., 200 values)
-- ✅ Use SEMI JOIN instead
WITH target_cities AS (
SELECT unnest(['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen']) AS city
)
SELECT * FROM sales
SEMI JOIN target_cities USING (city);
2.4 Row Group Size Tuning
The granularity of predicate pushdown depends on row group size. Smaller groups = more precise skipping, but more metadata overhead.
-- Export with custom row group size
COPY (
SELECT * FROM raw_data
ORDER BY trans_date
) TO 'optimized_sales.parquet'
(FORMAT PARQUET,
ROW_GROUP_SIZE 50000, -- default: 122880
PER_THREAD_OUTPUT TRUE);
Row Group Size Decision Guide:
| Row Group Size | Skip Precision | Metadata Overhead | Recommended For |
|---|---|---|---|
| 122880 (default) | Medium | Low | General purpose |
| 50000-80000 | High | Medium | Frequent date/range filtering |
| 20000-50000 | Very high | High | Point queries, precise filtering |
| < 10000 | Extremely high | Very high | Not recommended (metadata bloat) |
3. Layer 3: Database-Level Optimization
When file-level and row group-level optimizations are exhausted but queries still aren’t fast enough — for dashboards serving hundreds of concurrent requests — it’s time for Layer 3.
3.1 Filter Indexes
DuckDB’s indexes are filter indexes designed specifically for high-frequency filter columns:
-- Create index on high-frequency filter column
CREATE INDEX idx_sales_date ON sales(trans_date);
-- Composite index for multi-column filtering
CREATE INDEX idx_sales_region_date ON sales(region, trans_date);
Indexes store value-to-row-ID mappings, enabling direct row-level targeting:
-- Verify index usage with EXPLAIN
EXPLAIN
SELECT * FROM sales
WHERE trans_date = '2026-06-15'
AND region = 'North';
Look for Index Scan in the output — if you see Seq Scan, the index isn’t being used.
⚠️ Index Best Practices:
- Only index high-frequency filter columns (not every column)
- Best for high-selectivity queries (point lookups, narrow ranges)
- Not useful for full-table aggregates (SUM, AVG, COUNT)
- Drop indexes before bulk data loads, rebuild after
3.2 materialized tables
materialized tables are the ultimate optimization — trading storage for query speed:
-- Create materialized table: pre-aggregate to date + region granularity
CREATE TABLE IF NOT EXISTS daily_region_revenue AS
SELECT
trans_date,
region,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value
FROM sales
GROUP BY trans_date, region;
-- Dashboard queries hit the materialized table
-- Response time: seconds → milliseconds
SELECT * FROM daily_region_revenue
WHERE trans_date >= '2026-06-01'
ORDER BY trans_date;
Refreshing materialized tables (for daily batch updates):
DROP TABLE IF EXISTS daily_region_revenue; CREATE TABLE daily_region_revenue AS
SELECT
trans_date,
region,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue
FROM sales
GROUP BY trans_date, region;
When to use materialized tables:
| Characteristic | Good for materialized tables | Not suitable |
|---|---|---|
| Query frequency | Hundreds per hour | Once per day |
| Data update pattern | Daily batch | Real-time streaming |
| Aggregation dimensions | Fixed | Unpredictable |
| Storage budget | Can tolerate redundancy | Very tight |
4. Benchmark: 10M Rows End-to-End
Walk through a complete optimization pipeline:
-- 1. Prepare 10M rows of transaction data
CREATE TABLE raw_transactions AS
SELECT
range AS id,
'2026-01-01'::DATE + (range % 365) AS trans_date,
(random() * 100000)::INT AS amount,
CASE WHEN random() < 0.25 THEN 'North'
WHEN random() < 0.5 THEN 'East'
WHEN random() < 0.75 THEN 'South'
ELSE 'West' END AS region
FROM range(10000000);
-- 2. Baseline: full table scan (unoptimized)
.timer on
SELECT region, COUNT(*), SUM(amount)
FROM raw_transactions
WHERE trans_date >= '2026-06-01' AND trans_date < '2026-07-01'
GROUP BY region;
-- Time: ~350ms (full scan of 10M rows)
-- 3. Export to single Parquet + activate predicate pushdown
COPY raw_transactions TO 'transactions_big.parquet' (FORMAT PARQUET);
SELECT region, COUNT(*), SUM(amount)
FROM 'transactions_big.parquet'
WHERE trans_date >= '2026-06-01' AND trans_date < '2026-07-01'
GROUP BY region;
-- Time: ~120ms (pushdown active, ~800K rows read)
-- 4. Tune row group size + sort by date before export
COPY (
SELECT * FROM raw_transactions ORDER BY trans_date
) TO 'transactions_tuned.parquet'
(FORMAT PARQUET, ROW_GROUP_SIZE 50000, PER_THREAD_OUTPUT TRUE);
SELECT region, COUNT(*), SUM(amount)
FROM 'transactions_tuned.parquet'
WHERE trans_date >= '2026-06-01' AND trans_date < '2026-07-01'
GROUP BY region;
-- Time: ~60ms (finer row group skipping)
-- 5. Filter index (in-memory table)
CREATE INDEX idx_trans_date ON raw_transactions(trans_date);
SELECT region, COUNT(*), SUM(amount)
FROM raw_transactions
WHERE trans_date >= '2026-06-01' AND trans_date < '2026-07-01'
GROUP BY region;
-- Time: ~80ms (index-assisted filtering)
-- 6. materialized table (ultimate optimization)
CREATE TABLE IF NOT EXISTS daily_revenue_mv AS
SELECT trans_date, region, COUNT(*) AS cnt, SUM(amount) AS rev
FROM raw_transactions
GROUP BY trans_date, region;
SELECT region, SUM(cnt) AS order_count, SUM(rev) AS total_revenue
FROM daily_revenue_mv
WHERE trans_date >= '2026-06-01' AND trans_date < '2026-07-01'
GROUP BY region;
-- Time: ~3ms (116x speedup!)
Performance Summary:
| Stage | Time | Speedup vs Baseline |
|---|---|---|
| Full table scan (baseline) | 350ms | 1x |
| Parquet + predicate pushdown | 120ms | 2.9x |
| Row group tuning (50000) | 60ms | 5.8x |
| Filter index | 80ms | 4.4x |
| materialized table | 3ms | 116x |
本文信息
| 项目 | 内容 |
|---|---|
| DuckDB 版本 | v1.5.x（部分功能基于 v2.0 Preview） |
| 最后验证 | 2026-09-12 |
| 测试环境 | Linux / x86_64 / 16GB RAM |
| 官方文档 | DuckDB Documentation |
| GitHub | pengzz9527/duckdb-blog |
如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。