Plan and run queries

Status

Variants

Run within memory

Keep execution inside a memory limit by streaming batches and spilling to disk, and report memory and temporary-file use.

Parse and optimise the query plan

Turn a query into a plan and rewrite it, pushing filters, projections and limits toward the scan and reordering joins.

Reload from the source

Read a sheet again from its source, on demand, on a timer or when the file changes, clearing any cache first.

Maintain the database file

Checkpoint, vacuum and report on the size and storage of the database file and its connections.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Background

When to use it

DuckDB aims to perform well out of the box, so most workloads do not require any tuning.^[4] DuckDB has no single configuration that is optimal for every workload, since the right settings depend on the workload's data, hardware and environment.^[4] Reach for the engine's own controls when a query is slow, runs out of memory or needs explaining, and read its plan before changing any setting. (Meridian's view)

In DuckDB, EXPLAIN does not run the query, so the number of rows it shows for each operator is an estimate calculated from the statistics of the base tables.^[3] In DuckDB, EXPLAIN ANALYZE runs and profiles the query, showing the central processing unit (CPU) time that each step takes.^[4] Query optimisers cost each plan from estimates of its cardinality, the number of rows flowing through each step of the plan.^[1] Poor cardinality estimates and uncaught correlation between query conditions are one of the main reasons why query optimisers pick poor query plans.^[1] In DuckDB, the ANALYZE statement recomputes table statistics, which are used only for choosing the order in which tables are joined, and is recommended after large inserts or deletes.^[5]

DuckDB can process datasets larger than the available memory by spilling to disk, into a temporary directory that the temp_directory option can change.^[4] In DuckDB, blocking operators such as GROUP BY, JOIN, ORDER BY and window functions cannot output a single row until they have seen the last row of their input.^[4] DuckDB may still run out of memory when several blocking operators appear in the same query, or with aggregate functions such as string_agg() that do not support offloading to disk.^[4] In DuckDB, setting preserve_insertion_order to false lets results without an ORDER BY clause be reordered, which can reduce memory use when importing or exporting data larger than memory.^[4] Look at the plan before raising a memory setting: reading fewer rows or joining in a better order attacks the cause rather than the symptom. (Meridian's view)

The method

Most query optimisers represent a plan as a tree of plan nodes, each a single operation, with intermediate results flowing from the leaves, which scan the data, up to the top.^[1] Query optimisation is of two types: logical optimisation, which works out the sequence of operations that solves the query, and physical optimisation, which decides how each operation is carried out.^[1] Cost-based query optimisers assign an estimated cost to each possible plan, from factors such as the input and output operations and CPU path length it needs, and choose the plan with the smallest cost.^[1] Finding the exact optimal query plan is typically too costly, so query optimisation tries to approximate it with a good enough plan found in a reasonable time.^[1] The order in which tables are joined largely determines a plan's performance, and most optimisers choose it with a dynamic programming algorithm pioneered by IBM's System R database project.^[1]

In DuckDB, a table scan that does not apply, or push down, a filter condition used later in the plan performs unnecessary input and output.^[4] DuckDB parallelises work by row groups, groups of rows stored together, so a query on k threads needs to scan at least k times 122,880 rows, the default row group size.^[4] In DuckDB, a prepared statement completes parsing and planning once and caches their output, so repeated executions with different parameters skip those steps.^[4]

Sources

1. [1]Query optimization - Wikipediasecondary source
2. [2]Query plan - Wikipediasecondary source
3. [3]EXPLAIN: Inspect Query Plans – DuckDBdocumentation
4. [4]Tuning Workloads – DuckDBdocumentation
5. [5]ANALYZE Statement – DuckDBdocumentation