How do you troubleshoot slow query performance in DuckDB?

Slow queries in DuckDB usually trace back to a handful of common causes, most diagnosable directly through DuckDB's own query plan inspection tools.

1. Check the query plan with EXPLAIN or EXPLAIN ANALYZE - reveals whether filters are being pushed down, which join algorithm was chosen, and where time is actually being spent in the plan.
2. Check whether file format and layout support pruning - querying many small, unsorted CSV files provides far less opportunity for row-group and zone-map pruning than well-organized, sorted Parquet files.
3. Check for unnecessary data movement - reading far more columns than a query actually needs, or materializing large intermediate results unnecessarily, adds avoidable I/O and memory pressure.
4. Check available memory and threads - DuckDB's performance scales with available RAM (for buffering and avoiding spilling to disk) and CPU threads (for its parallel, vectorized execution); a resource-constrained environment directly limits achievable performance.
5. Check for suboptimal join order or missing statistics - the optimizer relies on table statistics to choose good join orders and algorithms; stale or missing statistics (particularly relevant for external files rather than DuckDB's own managed tables) can lead to poor plan choices.

EXPLAIN ANALYZE is generally the most direct diagnostic starting point, since it shows actual (not just estimated) row counts and timing per operator in the executed plan, which usually makes it clear whether the bottleneck is I/O, a particular join, or an unexpectedly large intermediate result, rather than requiring guesswork about where time is going.