# Optimizing query performance > Practical techniques for tuning query performance in MotherDuck, including reading query plans, reducing data transfer, and choosing the right Duckling size. MotherDuck's [dual execution architecture](/concepts/architecture-and-capabilities/) 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](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/). This guide covers: - [How dual execution affects performance](#how-dual-execution-affects-performance) - [Choosing the right Duckling size](#choosing-the-right-duckling-size) - [Reading query plans with EXPLAIN](#reading-query-plans-with-explain) - [Common optimization patterns](#common-optimization-patterns) - [Reducing data transfer](#reducing-data-transfer) - [Monitoring query performance](#monitoring-query-performance) - [Scaling read-heavy workloads](#scaling-read-heavy-workloads) ## 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](/about-motherduck/billing/duckling-sizes/) 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](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/#duckling-sizing). ::: ## Reading query plans with EXPLAIN The [`EXPLAIN`](/sql-reference/motherduck-sql-reference/explain/) statement shows where each operation runs without executing the query. Use it to understand the query plan before optimizing. ```sql 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`](/sql-reference/motherduck-sql-reference/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 Database: `sample_data` ```sql 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; ``` 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](https://duckdb.org/docs/stable/dev/profiling.html). ::: ## 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: ```sql -- 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: ```sql -- 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: ```sql -- 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. ```sql -- 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: ```sql -- 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. ```sql -- 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](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/#performance-optimization-strategies). If your table is already loaded, you can re-sort it with `CREATE OR REPLACE`: ```sql 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: ```sql -- 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](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/#file-format-considerations). ## 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. ```sql -- 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. ```sql -- 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 Organization admins can monitor query activity in two ways: the [Duckling overview](/getting-started/interfaces/motherduck-quick-tour/#duckling-overview) page in the MotherDuck UI (**Settings** → **Duckling overview**), which visualizes query volume, wait time, spills, and errors per Duckling with a per-query drill-down, and the SQL views described below for programmatic analysis. ### QUERY_HISTORY view The [`MD_INFORMATION_SCHEMA.QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) view gives organization admins a record of all queries across the organization (Business plans only). 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 Database: `my_db` ```sql 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; ``` 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`](/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries/) view shows running and completed queries. Use it for real-time monitoring: #### Queries running longer than 30 seconds Database: `my_db` ```sql 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; ``` :::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](/key-tasks/authenticating-and-connecting-to-motherduck/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](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) for setup instructions. ## See also - [Architecture and capabilities](/concepts/architecture-and-capabilities/): How dual execution works - [Duckling sizes](/about-motherduck/billing/duckling-sizes/): Compute options and billing - [EXPLAIN](/sql-reference/motherduck-sql-reference/explain/): Query plan reference - [EXPLAIN ANALYZE](/sql-reference/motherduck-sql-reference/explain-analyze/): Query profiling reference - [Running dual execution queries](/key-tasks/running-hybrid-queries/): Examples of local + cloud queries - [Loading data best practices](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/): Performance tips for data ingestion --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fquery-performance%2F&page_title=Optimizing%20query%20performance&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.