The DataFusion Performance Cheat Sheet: The Config Knobs and Optimizer Rules That Actually Move the Needle

TL;DR: datafusion/common/src/config.rs is a thousand lines of options and most of them you will never touch. The ones that matter fall into four groups: parallelism (target_partitions), pushdown (dynamic filters, filter pushdown, repartition_*), spilling (sort_spill_reservation_bytes, max_spill_file_size_bytes), and the algorithm choices (prefer_hash_join, enable_piecewise_merge_join, hash_join_inlist_pushdown_max_size). This is a tour of the ones with a real performance consequence, and what each one costs you.

DataFusion’s configuration surface is enormous, and that is both a feature and a trap. Every interesting optimisation has a switch, and every switch has a “correctness” mode and a “fast” mode, and the defaults are tuned for a workload that is not yours.

Here is the map.

Parallelism

/// Number of partitions for query execution. Increasing partitions can increase
/// ... 
pub target_partitions: usize, transform = ExecutionOptions::normalized_parallelism, default = get_available_parallelism()

target_partitions is the number of parallel tasks DataFusion will use. It defaults to the machine’s available parallelism, which is right for a dedicated box and wrong for a container with 2 CPUs’ worth of quota among 64 vCPUs.

The transform is normalized_parallelism, which means the value is clamped into a sane range rather than taken literally. Setting target_partitions = 1000 on a 4-core box will not give you 1000-way parallelism; it will give you as much as the normalisation allows.

The related knob is planning_concurrency, also normalised parallelism. It controls how many threads run the physical planner, not the query. On a service that plans many small queries, raising it can lower planning latency.

Two more that matter for joins:

/// The number of rows under which the hash join will use a single partition
pub hash_join_single_partition_threshold: usize, default = 4 * 1024 * 1024
pub hash_join_single_partition_threshold_rows: usize, default = 1024 * 128

Both the bytes and the rows thresholds must be exceeded before the join is partitioned. A join with a 5 MB build side stays in one partition — one hash table, no shuffle, no repartition of the probe side. For a small dimension table joined against a huge fact table, that is the difference between a local hash join and a distributed one.

Pushdown

This is where most of DataFusion’s recent performance work has gone.

/// When set to true attempts to push down dynamic filters generated by operators
/// (TopK, Join & Aggregate) into the file scan phase.
pub enable_dynamic_filter_pushdown: bool, default = true

The master switch. It suppresses the three more specific ones, which is worth knowing because the interaction is surprising:

The config will suppress enable_join_dynamic_filter_pushdown, enable_topk_dynamic_filter_pushdown & enable_aggregate_dynamic_filter_pushdown. So if you disable enable_topk_dynamic_filter_pushdown, then enable enable_dynamic_filter_pushdown, the enable_topk_dynamic_filter_pushdown will be overridden.

The example in the doc is the one worth remembering:

For example, for a query such as SELECT * FROM t ORDER BY timestamp DESC LIMIT 10, the optimizer will attempt to push down the current top 10 timestamps that the TopK operator references into the file scans. This means that if we already have 10 timestamps in the year 2025 any files that only have timestamps in the year 2024 can be skipped / pruned at various stages in the scan.

That is the single highest-leverage optimisation in the list for time-partitioned data, and it is on by default.

Then the repartition switches, which control where shuffles are inserted:

pub repartition_aggregations: bool, default = true
pub repartition_joins: bool, default = true
pub repartition_file_scans: bool, default = true
pub repartition_windows: bool, default = true
pub repartition_sorts: bool, default = true

Each of these inserts a RepartitionExec when the operator declares it needs a particular distribution. Turning them off makes plans simpler and can help on a single machine — but they are the mechanism by which DataFusion gets parallelism at all.

And the round-robin one, which is a different kind of repartition:

/// When set to true, the physical plan optimizer will try to add round robin
/// repartitioning to increase parallelism to leverage more CPU cores
pub enable_round_robin_repartition: bool, default = true

Round-robin repartitioning is not a shuffle — it sends batch i to partition i % N, with no coordination. It is how DataFusion increases parallelism for a pipeline that has no natural partitioning (a filter over a scan, say). It is nearly free, and it is the difference between using one core and using all of them.

Spilling

/// Sort spill reservation size - the amount of memory reserved for the
/// sort's spill buffer
pub sort_spill_reservation_bytes: usize, default = 10 * 1024 * 1024

/// The maximum size of a spill file
pub max_spill_file_size_bytes: ConfigNonZeroUsize, default = non_zero_usize_default(128 * 1024 * 1024)

sort_spill_reservation_bytes is how much memory the sort keeps in reserve for its own spill buffer before it gives up and starts writing runs to disk. A larger value means fewer, bigger runs and a shallower merge tree; a smaller value means more runs and a deeper merge tree. The default of 10 MB is small — if your sorts are spilling, raising it usually helps.

max_spill_file_size_bytes caps the size of a single spill file, which bounds the largest batch a merge has to hold. This is the knob that interacts with the skewed-run re-spilling we looked at: a smaller cap means less memory pressure per merge, at the cost of more files.

There are also:

/// Compression to use for spilled data
pub spill_compression: SpillCompression, default = SpillCompression::Uncompressed

Uncompressed by default, which is the right choice for a temporary file you are about to read back — the CPU cost of compression usually exceeds the I/O saving on a local disk. On a slow network filesystem, or with Zstd, it can go the other way.

The join choices

/// Prefer hash join over sort merge join
pub prefer_hash_join: bool, default = true

Hash joins are the default because they are more predictable. A sort-merge join needs both sides sorted, and the sort is usually more expensive than the join.

/// If the join is small enough, use a single partition
pub hash_join_single_partition_threshold: usize, default = 4 * 1024 * 1024

Covered above.

/// The maximum size of the IN list to push down
pub hash_join_inlist_pushdown_max_size: usize, default = 128 * 1024
pub hash_join_inlist_pushdown_max_distinct_values: usize, default = 150

These control the hash join’s dynamic filter strategy — whether the build side’s keys are published as an IN list or as a hash-table membership test. The size limit is in bytes; the distinct-values limit is a separate cap of 150. A build side with 200 distinct keys goes to the hash-membership strategy even if it is small.

This matters because the IN list strategy has to copy the keys. For byte keys (strings), the implementation explicitly disables the IN list path so the filter borrows the hash table instead.

pub allow_symmetric_joins_without_pruning: bool, default = true

The symmetric hash join builds a hash table on both sides. It trades memory for parallelism, and this flag says whether to use it when the pruning opportunity is not there.

The Parquet knobs

/// Enable reading the page index (column index / offset index)
pub enable_page_index: bool, default = true
/// Enable pruning
pub pruning: bool, default = true
/// Read bloom filters
pub bloom_filter_on_read: bool, default = true
/// Push filters into the reader
pub pushdown_filters: bool, default = false

enable_page_index and bloom_filter_on_read are the ones that matter. The page index lets DataFusion skip pages within a row group, not just row groups, which is a big win on wide files. Bloom filters let it skip files entirely for a point lookup.

pushdown_filters defaults to false, and that surprises people. The doc explains why the default is conservative: pushing a filter into the reader means the reader evaluates it per row with its own semantics, which can differ from SQL’s. DataFusion keeps a residual filter for correctness, and enabling the pushdown without it would be wrong.

/// Maximum size of an IN list that the pruning predicate will rewrite into
/// per-value statistics checks. Longer lists skip container-level pruning.
pub max_in_list_size: usize, default = 20

Small, and it is a safety limit, not a performance one: rewriting a huge IN list into per-value statistics checks would be slow.

The sort and aggregate knobs

/// The optimizer will replace Filter(rn<=K) → Window(ROW_NUMBER) → Sort
/// patterns with a PartitionedTopKExec ...
/// When the window partition key has low cardinality, enabling this optimization
/// can improve performance. However, for high cardinality keys, it may
/// cause regressions in both memory usage and runtime.
pub enable_window_topn: bool, default = false

Off by default, and the doc explains the trade-off exactly: it is a win for low-cardinality partition keys and a regression for high-cardinality ones, because you keep K rows per partition and the number of partitions is the problem.

/// The optimizer will push TopK (Sort with fetch) below hash repartition
/// when the partition key is a prefix of the sort key, reducing data volume
/// before the shuffle.
pub enable_topk_repartition: bool, default = true

A nice one: if you are sorting by (a, b) and repartitioning by a, you can do the top-k before the shuffle, so the shuffle carries less data.

/// The optimizer will push a limit operation into grouped aggregations
/// which have no aggregate expressions, as a soft limit, emitting groups
/// once the limit is reached, before all rows in the group are read.
pub enable_distinct_aggregation_soft_limit: bool, default = true

SELECT DISTINCT with a LIMIT can stop early — a DISTINCT is a grouping, and once you have emitted N groups you are done.

The optimizer knobs

/// Maximum number of optimizer passes
pub max_passes: usize, default = 3

Small, and that is a statement of confidence: the fixed-point detection we looked at does the real work, and three passes is usually enough to reach it.

/// Skip optimizer rules that fail
pub skip_failed_rules: bool, default = false

Off by default, and it should stay off. When a rule fails, the optimizer normally propagates the error. With this on, it logs a warning and continues — which is exactly how you get a query that returns subtly wrong results. The source code’s own comment says:

Note to future readers: if you see this warning it signals a bug in the DataFusion optimizer. Please consider filing a ticket

/// The default selectivity of a filter, used when statistics are unavailable
pub default_filter_selectivity: ConfigFilterSelectivity, default = filter_selectivity_default(20)

This is a planning knob that affects real decisions: with no statistics, a filter is assumed to select 20% of the rows. That number changes join strategy, join order, and whether a repartition is worth it. If your workload has wildly different selectivities (a 0.1% filter in one place, a 90% filter in another), and you have no statistics, this default is doing a lot of damage.

pub use_statistics_registry: bool, warn = "`use_statistics_registry` is deprecated and ignored; ..."

Deprecated and ignored. A good reminder to check the deprecation notes before tuning.

The catch

• Most of these are workload-specific. The defaults are tuned for TPC-H-like workloads on a dedicated machine. Yours is not.

• Several are correctness-sensitive. pushdown_filters, skip_failed_rules and enable_physical_uncorrelated_scalar_subquery can produce wrong answers if misused. The last one has an explicit warning in the source:

  Note disabling this option is not recommended. It restores pre … behavior, which silently produces incorrect results for multi-row subqueries …

• The knobs interact. enable_dynamic_filter_pushdown suppresses three others. target_partitions changes which repartitions are inserted, which changes the memory profile, which changes whether things spill.

• Measure. EXPLAIN ANALYZE gives you per-operator metrics (rows_produced, elapsed_compute, mem_used), and the array_map_created_count metric tells you whether the perfect hash join fired. Tuning without metrics is guessing.

Why it matters

A query engine’s configuration surface is a map of its design decisions. Reading it tells you what the engine thinks is important — and for DataFusion, the interesting entries are the ones that are off by default, because those are the ones where the trade-off was close enough that the maintainers did not want to pick for you.