---
title: "Query optimization"
description: "Query optimization is the process by which a database transforms a SQL query into an efficient execution plan, choosing among logically equivalent strategies for the one expected to run fastest."
canonical: "https://motherduck.com/glossary/query-optimization/"
related:
  - title: "Optimizing query performance | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/query-performance/"
  - title: "Simplicity of a Database, but the Speed of a Cache: OLAP Caches for DuckDB"
    url: "https://motherduck.com/blog/duckdb-olap-caching/"
  - title: "Cost-Based Hybrid Query Optimization in MotherDuck - MotherDuck Research Papers"
    url: "https://motherduck.com/research/cost-based-hybrid-query-optimization-in-motherduck/"
---

# Query optimization

> Query optimization is the process by which a database transforms a SQL query into an efficient execution plan, choosing among logically equivalent strategies for the one expected to run fastest.

## Overview

The same SQL query can be executed in many different ways that all produce the same result — different join orders, different join algorithms, different orders of applying filters — but with very different performance. Query optimization is the process of exploring those equivalent strategies and picking a good (not necessarily perfect) one, without requiring the person writing the SQL to think about execution details at all.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Rule-Based vs. Cost-Based Optimization

Rule-based optimization applies transformations known to always (or almost always) help, like pushing filters down before joins, regardless of the actual data. Cost-based optimization goes further, using statistics about table sizes and value distributions to estimate the cost of different plan alternatives — such as which table should be on which side of a join — and picking the cheapest estimated option. Most production databases combine both: applying safe rule-based rewrites first, then using cost estimates to choose among the remaining options.

## Common Optimizations

Typical optimizations include predicate pushdown (filtering as early as possible), projection pushdown (reading only needed columns), join reordering, common subexpression elimination, and choosing between join algorithms like hash join, merge join, and nested loop join based on estimated sizes.

## DuckDB's Optimizer

DuckDB runs a query through a pipeline of logical optimizations — including filter and projection pushdown, join order optimization driven by cardinality estimates, and constant folding — before generating a physical, vectorized execution plan. You can inspect the result of this pipeline with `EXPLAIN`, and see actual runtime behavior with `EXPLAIN ANALYZE`, which is often the fastest way to understand why a particular query is slow.