DuckDB Query Profiling: How to Find Slow Queries on Parquet Files

Parquet files are widely used for analytics because they store data in a columnar format. Instead of reading every field in every row, an analytical query engine can often read only the columns it needs. This can reduce the amount of data processed and improve query performance.

However, working with Parquet does not guarantee that every query will be fast. A query may scan millions of rows, perform an expensive join, sort a large intermediate result, or read more columns than necessary. As datasets grow, these operations can become a performance bottleneck.

DuckDB provides query profiling tools that help developers understand how a query executes and where it spends its time. By examining the query plan and operator-level metrics, you can identify expensive operations and make targeted improvements.

In this article, you will learn how to profile DuckDB queries against Parquet files, interpret execution plans, identify common bottlenecks, and optimize queries based on measured results.

What Is DuckDB Query Profiling?

DuckDB is an analytical database engine designed for workloads such as data exploration, reporting, and large-scale data processing.

It can query Parquet files directly without requiring you to import the entire dataset into a separate database table.

For example, you can query a Parquet file using SQL:

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
GROUP BY product_category
ORDER BY total_sales DESC;

Although this query looks simple, DuckDB must perform several operations:

Read the required columns from the Parquet file.
Filter rows if a filter is present.
Aggregate sales by category.
Sort the aggregated results.
Return the final output.

The amount of work depends on the file size, the number of rows, the data layout, and the query itself.

Query profiling helps you understand which operations consume the most execution time and resources.

Why Queries on Parquet Files Can Be Slow

Parquet stores data in column chunks and row groups. This layout can allow an engine to skip unnecessary columns and, when file metadata and query predicates permit, skip row groups that cannot match a filter.

However, several factors can reduce these benefits.

Problem	Possible effect
Reading unnecessary columns	Increases data access and processing
Missing or ineffective filters	Causes more rows to be scanned
Large aggregations	Requires additional CPU and memory
Expensive joins	Increases processing and intermediate data
Large sorts	Can consume substantial memory and processing time
Too many small Parquet files	Adds file-opening and metadata overhead
Poorly selective filters	Leave many rows to process
Slow storage	Increases data-reading time

These are possible causes, not conclusions you should make before profiling. The actual bottleneck depends on the query and environment.

The best approach is to measure the query first, inspect its execution plan, and then test a specific optimization.

Step 1: Prepare a Parquet Dataset

For this example, assume you have a Parquet file named sales.parquet with these columns:

Column	Description
`order_id`	Unique order identifier
`order_date`	Date of the order
`product_category`	Category of the product
`region`	Sales region
`sales_amount`	Amount generated by the sale

You can query the file directly in DuckDB.

If you use the DuckDB command-line interface, open a database session and execute:

SELECT *
FROM read_parquet('sales.parquet')
LIMIT 10;

This confirms that the file can be read and allows you to inspect a small sample.

For a large dataset, avoid running SELECT * without a limit simply to inspect the data. Reading every column and returning every row can create unnecessary work.

You can also inspect the schema:

DESCRIBE
SELECT *
FROM read_parquet('sales.parquet');

Understanding the schema helps you write queries that select only the columns required for the analysis.

Step 2: Establish a Baseline Query

Before optimizing a query, record its current performance.

Consider a report that calculates total sales by product category:

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
GROUP BY product_category
ORDER BY total_sales DESC;

This query must read the category and sales amount columns, aggregate the values, and sort the result.

The baseline should include more than execution time when possible. Record the dataset size, query parameters, DuckDB version, and relevant environment details.

For example, you might record:

The number and size of Parquet files.
The approximate number of rows.
The columns used by the query.
The query execution time.
Whether the data was already cached by the operating system.
The memory available to the process.

These details help make later comparisons meaningful.

Why a Baseline Matters

Suppose you change a query and it completes faster in one run. That does not automatically prove the change improved performance.

The difference may result from caching, background system activity, or variation in resource availability.

Run the same query several times under comparable conditions. Where possible, compare both repeated execution and first-run behavior, because they can represent different workloads.

Step 3: Profile the Query With EXPLAIN ANALYZE

DuckDB supports EXPLAIN and EXPLAIN ANALYZE for inspecting query execution.

Use EXPLAIN to examine the planned operations:

EXPLAIN
SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
GROUP BY product_category
ORDER BY total_sales DESC;

The output describes the execution plan selected by the optimizer.

To execute the query and collect profiling information, use:

EXPLAIN ANALYZE
SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
GROUP BY product_category
ORDER BY total_sales DESC;

Unlike EXPLAIN, EXPLAIN ANALYZE runs the query and reports actual execution metrics for its operators.

The output can include information such as operator timing, estimated or actual cardinality depending on the displayed plan, and the structure of the operations performed.

Exact output formatting varies across DuckDB versions.

What Should You Look For?

Start with the operators that consume the most time and process the most data.

Common operators include:

TABLE_SCAN: Reads data from a table or file source.
FILTER: Applies conditions to rows.
PROJECTION: Selects or computes columns.
HASH_GROUP_BY: Performs grouped aggregation.
HASH_JOIN: Combines rows using a hash-based join.
ORDER_BY: Sorts results.

A scan that processes many rows may indicate that the query reads a large portion of the dataset. An expensive aggregation may indicate that many rows or groups must be processed.

However, operator timings require careful interpretation. Some operations execute in parallel, and the reported timings should not always be added together as if every operator ran sequentially.

Use the plan structure, operator metrics, and overall query time together rather than judging performance from a single number.

Step 4: Reduce the Number of Columns Read

One of the simplest ways to improve analytical queries is to avoid reading columns that are not needed.

For example, this query requests every column:

SELECT *
FROM read_parquet('sales.parquet')
WHERE region = 'North';

If the report only needs the category and sales amount, use a narrower projection:

SELECT
    product_category,
    sales_amount
FROM read_parquet('sales.parquet')
WHERE region = 'North';

The second query requires only three columns: product_category, sales_amount, and region.

DuckDB can often push the projection into the Parquet scan, allowing it to avoid reading unrelated columns. The exact amount of data read depends on the query and file layout.

Why Projection Pushdown Matters

Parquet stores columns separately within row groups. When the query requires only a few columns, the engine can often read just those columns rather than processing the entire record.

This is particularly valuable when a file contains many wide or rarely used fields.

For analytical workloads, select the columns required by the report instead of retrieving every column by default.

Step 5: Use Filters Effectively

Filters can reduce the number of rows that must be processed.

Suppose a report needs sales from January 2026. You could write:

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
WHERE order_date >= DATE '2026-01-01'
  AND order_date < DATE '2026-02-01'
GROUP BY product_category
ORDER BY total_sales DESC;

This query limits the results to January 2026.

DuckDB can push supported predicates into the Parquet scan. Depending on the file's metadata and physical layout, it may skip row groups whose values cannot satisfy the filter.

Why File Layout Matters

Imagine a Parquet file containing several years of sales data.

If each row group contains a mixture of dates from different years, a date filter may still require reading many row groups.

If the data is organized by date and row-group statistics are useful, DuckDB may be able to skip more data.

Sorting or partitioning data can help in some workloads, but it also adds maintenance and storage considerations. Profile the actual query before redesigning the dataset.

Avoid Unnecessary Transformations in Filters

Expressions applied to filtered columns can sometimes make it harder for an engine to use metadata-based pruning.

For example, instead of extracting the year from every date:

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
WHERE EXTRACT(YEAR FROM order_date) = 2026
GROUP BY product_category;

use a date range when it expresses the same business requirement:

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
WHERE order_date >= DATE '2026-01-01'
  AND order_date < DATE '2027-01-01'
GROUP BY product_category;

The range form can make predicate pushdown and row-group pruning easier when the file metadata supports them.

Step 6: Investigate Expensive Aggregations and Joins

Filtering and column selection do not solve every performance problem.

Some queries are expensive because they group millions of rows, join large datasets, or create large intermediate results.

Consider a join between sales and product metadata:

SELECT
    p.product_category,
    SUM(s.sales_amount) AS total_sales
FROM read_parquet('sales.parquet') AS s
JOIN read_parquet('products.parquet') AS p
    ON s.product_id = p.product_id
GROUP BY p.product_category;

If both files are large, the join can require substantial processing.

Use EXPLAIN ANALYZE to inspect the join operator, the number of rows flowing through the plan, and the cost of downstream aggregation.

Possible improvements include:

Selecting only the required columns from each input.
Filtering input data before joining when the business logic allows it.
Verifying that join keys use compatible data types.
Reducing duplicate records when duplicates are not part of the intended data model.
Reviewing whether the query needs all joined rows or can aggregate earlier without changing the result.

Do not assume that moving an aggregation before a join is always valid. The transformation must preserve the query's semantics, especially when joins are one-to-many or when filters depend on joined columns.

Step 7: Use DuckDB's Profiling Output

For more detailed analysis, DuckDB provides profiling configuration options that can produce query profiles in machine-readable formats.

The exact available settings can vary by DuckDB version, so check the configuration supported by the version used in your environment.

For example, a profiling session may use settings similar to these:

SET enable_profiling = 'json';
SET profiling_output = 'query-profile.json';

SELECT
    product_category,
    SUM(sales_amount) AS total_sales
FROM read_parquet('sales.parquet')
WHERE order_date >= DATE '2026-01-01'
  AND order_date < DATE '2026-02-01'
GROUP BY product_category;

This configuration requests JSON profiling output for the query.

The resulting profile can be used in scripts or dashboards to compare operator metrics across repeated runs. Confirm the supported profiling settings and output fields for your installed DuckDB version before integrating them into automation.

Machine-readable profiles are especially useful when a team wants to track query performance over time rather than inspecting every plan manually.

For example, a regression check could flag a significant increase in execution time or the number of rows processed. Such checks should account for normal variation and changes in the dataset.

Best Practices for Profiling Parquet Queries

Measure Before Changing the Query

Capture the original query plan and execution metrics. Change one important factor at a time so that you can identify which modification produced the improvement.

Use Representative Data

A query that performs well on a small sample may behave differently on a dataset containing millions of rows or many more distinct groups.

Use representative file sizes, row counts, data distributions, and filter selectivity when evaluating performance.

Keep Query Results Consistent

An optimization is only useful if it preserves the intended result.

Compare row counts, aggregate values, and other relevant outputs before and after changing a query.

Watch Memory Consumption

Large aggregations, joins, and sorts can use substantial memory. If a query is slow, inspect memory-related behavior as well as CPU time.

Avoid Excessive File Fragmentation

Thousands of very small Parquet files can introduce file-opening and metadata overhead. Combining files may help some workloads, but the right file size depends on storage, query patterns, and parallelism.

Record DuckDB and Dataset Versions

Performance can change when the database engine, query, or data layout changes.

Record enough information to reproduce important benchmarks and investigate regressions.

Common Mistakes to Avoid

Assuming the Table Scan Is Always the Problem

A scan may process many rows but still be relatively efficient. The expensive part could be a join, sort, or aggregation later in the plan.

Inspect the full execution plan before choosing an optimization.

Treating One Benchmark as Definitive

A single run can be affected by caching and other system activity. Repeat measurements under comparable conditions.

Using SELECT * for Analytical Reports

Retrieving unnecessary columns can increase data access and processing. Select only the fields needed for the result.

Ignoring Data Layout

A filter can be logically selective but still read many row groups if the physical layout and metadata do not support effective pruning.

Optimizing Without Checking Correctness

Changes to filters, joins, and aggregation order can alter the result. Verify correctness as part of every optimization.

Advantages and Disadvantages of DuckDB Query Profiling

Advantages

Reveals the operations performed by a query.
Provides execution metrics for investigating bottlenecks.
Helps identify unnecessary scans, expensive joins, and costly aggregations.
Supports profiling output that can be used in automated analysis.
Makes performance tuning more evidence-based.

Disadvantages

Query plans and profiling output require interpretation.
Profiling adds some overhead and may affect measurements.
A successful optimization for one dataset may not help another.
Changes to file layout can introduce additional storage and maintenance work.
Operator timings alone do not always explain end-to-end performance.

Summary

DuckDB query profiling helps developers understand why a query against Parquet files takes longer than expected. By using EXPLAIN and EXPLAIN ANALYZE, you can inspect the execution plan, identify expensive operators, and measure how much data is processed.

Start with a baseline query, then investigate column selection, filter pushdown, row-group pruning, joins, and aggregations. Make one change at a time and verify that the results remain correct.

The most effective optimization is the one supported by measurements. Rather than guessing which SQL expression is slow, use DuckDB's profiling tools to identify the actual bottleneck and focus your effort where it can make a measurable difference.