Optimizing query performance

MotherDuck's Dual Execution architecture splits query work between your local DuckDB instance and the MotherDuck cloud service. Understanding how this works is the key to writing fast queries after your data is available to query.

This guide focuses on query tuning. For ingestion-specific choices, such as load batch size, file format tradeoffs, source data location, and Duckling size for large loads, see Loading data best practices.

This guide covers:

• Set the right expectations
• How Dual Execution affects performance
• Choosing the right Duckling size
• Reading query plans with EXPLAIN
• Common optimization patterns
• Reducing data transfer
• Monitoring query performance
• Scaling read-heavy workloads

Set the right expectations

MotherDuck is an analytical engine, not a transactional (OLTP) one. It's built for large scans, aggregations, and joins, not for many tiny single-row reads and writes. When MotherDuck feels slow from an application, the workload is usually shaped like OLTP. Reshape it and the same engine that powers your warehouse serves application queries in the sub-second range:

• Batch your writes. Loading single tables in large batches saturates the connection and is much faster than many small writes across many tables. MotherDuck is ACID compliant but is not an OLTP system: put a queue in front of high-frequency writes and load in bulk. See Loading data best practices.
• Use set-based operations. Replace per-row INSERT calls with COPY or INSERT … SELECT so each round trip does meaningful work.
• Scale reads instead of contending. For spiky application traffic, read scaling adds read-only Ducklings so concurrent users don't queue behind each other.

How Dual Execution affects performance

When you run a query against MotherDuck, the query planner decides which parts execute locally and which parts execute remotely:

• Queries on md: databases run on the MotherDuck cloud service (your Duckling).
• Queries on local databases (in-memory or file-based) run on your local DuckDB instance.
• Queries that mix both trigger data transfer between local and cloud. The planner moves data in whichever direction minimizes transfer.

This means a slow query might not be doing too much work: it might be moving too much data between local and cloud. Identifying where the bottleneck is (compute vs. transfer) is the first step in any optimization.

Choosing the right Duckling size

Your Duckling size directly affects query performance on the cloud side. Each size offers different compute resources:

Duckling	Best for	Notes
Pulse	Ad-hoc queries, dashboards, data apps	Auto-scaling, low latency for short queries. Can be expensive for sustained heavy compute.
Standard	Recurring analytical queries, dashboards, moderate transformations	Balanced performance for most query workloads.
Jumbo	Large joins, complex aggregations	More memory and CPU for heavy queries.
Mega / Giga	Very large query jobs and transformations	For workloads that exceed Jumbo capacity. Longer startup times.

tip

Start with Pulse for interactive exploration and Standard for recurring analytical query workloads. Only move to Jumbo or larger when you see queries spilling to disk or timing out. For large data loading jobs, follow the loading best-practices Duckling sizing guidance.

Reading query plans with EXPLAIN

The EXPLAIN statement shows where each operation runs without executing the query. Use it to understand the query plan before optimizing.

EXPLAIN
SELECT customer_id, sum(amount)
FROM md_database.sales
WHERE sale_date >= '2026-01-01'
GROUP BY customer_id;

In the output, look for these markers:

• (L): operation runs locally
• (R): operation runs remotely on MotherDuck
• UPLOAD_SINK / UPLOAD_SOURCE: data moving from local to cloud
• DOWNLOAD_SINK / DOWNLOAD_SOURCE: data moving from cloud to local

Using EXPLAIN ANALYZE for runtime metrics

EXPLAIN ANALYZE executes the query and shows actual timing and row counts for each operator. With FORMAT JSON, the editor below renders the plan as an interactive tree; in other clients, plain EXPLAIN ANALYZE prints the same information as text. Try it on the Hacker News sample dataset:

Profile a query with EXPLAIN ANALYZE

EXPLAIN (
  ANALYZE,
  FORMAT JSON
)
SELECT
  "by" AS author,
  COUNT(*) AS stories,
  ROUND(AVG(score), 1) AS avg_score
FROM
  sample_data.hn.hacker_news
WHERE
  type = 'story'
GROUP BY
  author
ORDER BY
  stories DESC
LIMIT
  5;

SQL Editor loading...

Login to connect

PreviewLogin for live results

This is the best way to find where time is actually spent. In the plan above:

• The SEQ_SCAN reads only the by and score columns and pushes the type='story' filter into the scan, returning 334,153 of the table's 3.9 million rows.
• The HASH_GROUP_BY takes the most time of any operator: aggregation dominates this query, not scanning.
• Only 5 rows travel from the cloud to your client (DOWNLOAD_SOURCE), so data transfer is negligible.

In your own queries, look for:

• Operators with high row counts relative to the final result: these suggest missing filters.
• Scan operators showing large row counts: the scan may not be filtering effectively.
• Upload/Download operators with large data volumes: a sign of excessive data transfer.

info

For a deeper dive into reading query plans, see the DuckDB query profiling guide.

Common optimization patterns

Filter early

Push filters as close to the data source as possible. The query planner pushes most predicates down into table scans automatically, but predicates that reference multiple tables at once can't be pushed into either scan:

-- Good: each filter references one table, so it can be
-- pushed down into that table's scan
SELECT s.customer_id, sum(s.amount)
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
WHERE s.sale_date >= '2026-01-01'
    AND c.region = 'EU'
GROUP BY s.customer_id;

-- Less efficient: an OR across both tables can't be pushed
-- into either scan, so every joined row is checked
SELECT s.customer_id, sum(s.amount)
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
WHERE s.amount > 1000 OR c.region = 'EU'
GROUP BY s.customer_id;

Use EXPLAIN to verify that filters appear inside the TABLE_SCAN operator rather than in a separate FILTER step above it.

Keep joins lean

DuckDB's optimizer picks the join order automatically based on estimated table sizes, so you rarely need to reorder joins by hand. What the optimizer can't do is undo row explosion: the number of rows a join produces is determined by your join keys and the data.

Reduce the rows entering a join. Filter and pre-aggregate before joining so the join processes the smallest possible result sets:

-- Joins every sale to its customer row, then aggregates
SELECT c.region, SUM(s.amount) AS revenue
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY c.region;

-- Faster on large fact tables: aggregate first, so the join
-- sees one row per customer instead of one row per sale
SELECT c.region, SUM(s.customer_total) AS revenue
FROM (
    SELECT customer_id, SUM(amount) AS customer_total
    FROM sales
    GROUP BY customer_id
) s
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY c.region;

Join on keys that are unique on at least one side. If the join key has duplicates on both sides, every match multiplies: 10 matching rows on each side produce 100 output rows. This is usually a data modeling issue (deduplicate first) rather than a query tuning issue.

Watch out for accidental cross joins. A missing or incorrect join condition pairs every row of one table with every row of the other:

-- No condition relates s and c, so this is a cross join:
-- 1 million sales x 100,000 customers = 100 billion rows
SELECT *
FROM sales s, customers c
WHERE s.amount > 1000;

Run EXPLAIN ANALYZE and compare each join operator's output row count to its inputs: a join that emits far more rows than either input has a key problem.

Select only the columns you need

MotherDuck uses columnar storage. Selecting only the columns you need means less data read from disk and less data transferred.

-- Good: reads only two columns
SELECT customer_id, amount FROM sales;

-- Avoid: reads every column, even if you only need two
SELECT * FROM sales;

Use appropriate data types

Storing numbers as strings wastes storage and makes comparisons slower. Use the right types from the start:

-- Good: numeric types for numeric data
CREATE TABLE events (
    event_id INTEGER,
    event_ts TIMESTAMP,
    value DOUBLE
);

-- Avoid: everything as VARCHAR
CREATE TABLE events (
    event_id VARCHAR,
    event_ts VARCHAR,
    value VARCHAR
);

Sort tables by common filter columns

Table layout affects scan performance. Sorting by frequently filtered columns helps DuckDB skip row groups that don't match your filter.

-- Sort by date if you frequently filter by date ranges
CREATE OR REPLACE TABLE sales AS
SELECT * FROM raw_sales
ORDER BY sale_date;

tip

If you control the ingestion path, sort data during loading. For loading-time guidance, see Loading data best practices. If your table is already loaded, you can re-sort it with CREATE OR REPLACE:

CREATE OR REPLACE TABLE sales AS
SELECT * FROM sales ORDER BY sale_date;

Use LIMIT for exploration

When exploring data interactively, always add a LIMIT to avoid scanning entire tables:

-- Quick look at the data shape
SELECT * FROM large_table LIMIT 100;

Prefer Parquet for external data

If you query external files from S3, HTTPS, or another supported source, Parquet files perform significantly better than CSV or JSON for analytical queries. Parquet supports predicate pushdown and column pruning, so MotherDuck reads only the data it needs. For loading-specific file format tradeoffs, see Loading data best practices.

Reducing data transfer

Data transfer between local and cloud is often the biggest performance bottleneck in Dual Execution queries. Here is how to minimize it.

Keep data and compute in the same place

If your query only touches cloud data, keep all tables in md: databases. Mixing local and cloud tables in the same query forces data transfer.

-- All cloud: no transfer needed
SELECT s.customer_id, c.name, sum(s.amount)
FROM md_db.sales s
JOIN md_db.customers c ON s.customer_id = c.customer_id
GROUP BY s.customer_id, c.name;

Be mindful of join placement

When you join a local table with a cloud table, MotherDuck transfers the smaller side to where the larger side lives. You can help by:

• Keeping large tables in the cloud and small lookup tables local (or vice versa).
• Pre-filtering before the join to reduce the volume of data that needs to move.

-- Filter the local table first, then join with the cloud table
-- Only the filtered rows get uploaded
SELECT s.*, p.price
FROM md_db.sales s
JOIN (
    SELECT item, price FROM local_db.pricing
    WHERE price > 2.0
) p ON s.item = p.item;

Use EXPLAIN to confirm that upload/download operators are handling a small number of rows.

Attach databases strategically

• Use md: databases when your workload is primarily cloud-based or shared with others.
• Use local databases for data that only you need and that is frequently joined with other local data.
• Avoid attaching large local databases just to run a single query: consider loading the data into MotherDuck instead.

Monitoring query performance

Monitoring query activity across an organization requires permission to view query history or organization-wide Duckling activity. The Admin and Builder preset roles include both permissions by default. Use the Duckling overview page in the MotherDuck UI (Settings → Duckling overview) to visualize query volume, wait time, spills, and errors per Duckling with a per-query drill-down, or use the SQL views described below for programmatic analysis.

QUERY_HISTORY view

Reading the MD_INFORMATION_SCHEMA.QUERY_HISTORY view requires permission to view query history. The Admin and Builder preset roles include this permission by default. On Business plans, the view contains a record of all queries across the organization. Use it to find slow or expensive queries. The example results below show what the output looks like; run the query to see your own organization's data.

Slowest queries in the past 24 hours

SELECT
  query_id,
  user_name,
  execution_time,
  wait_time,
  bytes_uploaded,
  bytes_downloaded,
  bytes_spilled_to_disk,
  instance_type,
  left(query_text, 200) AS query_preview
FROM
  md_information_schema.query_history
WHERE
  start_time >= now() - INTERVAL 1 DAY
ORDER BY
  execution_time DESC
LIMIT
  20;

SQL Editor loading...

Login to connect

PreviewLogin for live results

Key columns to watch:

Column	What it tells you
EXECUTION_TIME	Time spent actively running the query.
WAIT_TIME	Time waiting for resources (other queries, data uploads). High wait time may mean your Duckling is overloaded.
BYTES_UPLOADED / BYTES_DOWNLOADED	Volume of data transferred between local and cloud. High values indicate Dual Execution overhead.
BYTES_SPILLED_TO_DISK	Data spilled because it did not fit in memory. Consider a larger Duckling size.
INSTANCE_TYPE	Which Duckling size ran the query.

RECENT_QUERIES view

The MD_INFORMATION_SCHEMA.RECENT_QUERIES view shows running and completed queries. Use it for real-time monitoring:

Queries running longer than 30 seconds

SELECT
  query_id,
  user_name,
  execution_time,
  left(query_text, 200) AS query_preview
FROM
  md_information_schema.recent_queries
WHERE
  end_time IS NULL
  AND total_elapsed_time > INTERVAL 30 SECOND;

SQL Editor loading...

Login to connect

PreviewLogin for live results

tip

You can also monitor and cancel running queries in the MotherDuck UI under Settings > Running Queries.

Scaling read-heavy workloads

If your performance bottleneck is query concurrency rather than individual query speed, read scaling lets you spin up additional read-only Ducklings. This is useful for:

• Data applications with many concurrent users
• BI tools with simultaneous dashboard viewers
• API backends serving analytics queries

Read scaling ducklings are eventually consistent (results may lag a few minutes behind writes). See the read scaling guide for setup instructions.

See also

• Architecture and capabilities: How Dual Execution works
• Duckling sizes: Compute options and billing
• EXPLAIN: Query plan reference
• EXPLAIN ANALYZE: Query profiling reference
• Running Dual Execution queries: Examples of local + cloud queries
• Loading data best practices: Performance tips for data ingestion