Why Parquet Queries Remain Slow: Partitioning, Row-Group Statistics and Pruning Acceptance Tests

A small result does not imply a small scan

An order query selects one day of completed records for one plant. Its result is small, but latency grows with the historical file collection. Separate file discovery, metadata access, data transfer, decompression, filtering, aggregation and output. More capacity may temporarily shorten one stage without explaining why unrelated years enter the read path. The engineering goal should include correct results and reduced resources, not just a shorter elapsed time.

Apache Parquet organizes rows into row groups, each containing column chunks, which in turn contain pages. Footer metadata identifies column-chunk locations.[1] A reader may therefore avoid unneeded columns and skip groups that cannot satisfy a condition. The format supplies structure; realized optimization depends on written information, query expressions and reader implementation.

In this example, order_id is a unique integer, order_day is a DATE with an established business definition, plant is a code, qty is a nonnegative integer and status is an enumeration. Define the relevant time zone and business cutoff before ingestion; truncating a date string is not a universal conversion rule. The output is an order count and quantity sum for a date, plant and status. All candidate layouts must contain the same frozen records to isolate physical-layout effects.

Record reader and writer versions, extension versions, file and object versions, compression, row-group sizes, threads, memory limits and storage location. This article references the DuckDB 2.0 documentation available when read; it does not establish identical behavior in older releases. Keep expectations, query plans and observed reads separate. Documented support does not prove that a particular query and dataset trigger an optimization.

Partition, column and row-group pruning have different roles

Partition pruning uses directory keys or a trusted manifest to exclude entire files. With order_day directories, a November 2 query can exclude other dates through a matching partition condition. DuckDB documents reading Hive partition values from directories and pushing down partition filters.[2] Discovery can still cost time, and a date in a path does not establish that its records belong to that date. Validate membership when writing.

Column pruning retains the fields needed for computation. Even if the output is only a quantity sum, plant, status and possibly the date are needed for filtering. order_id may not be required for COUNT(*). Output-column count is not scan-column count. Conversely, fetching SELECT * into an application and filtering there can lose an opportunity to reduce reads at the scan. DuckDB explicitly supports Parquet projection and filter pushdown.[3]

Row-group pruning relies on available, trustworthy bounds. For a closed interval a≤x≤b, exclude a group when its maximum is less than a or its minimum is greater than b. An overlapping range establishes only a possible match; values still need the complete predicate. For a half-open interval a≤x<b, exclude when maximum<a or minimum≥b. Misplacing equality can remove valid boundary records.

Per-column minima and maxima generally do not describe joint distributions. A group containing plant A on November 1 and plant B on November 3 can have date and plant ranges compatible with November 2 at A without containing that combination. When statistics cannot establish absence, extra reads are acceptable; guessing absence and omitting data is not. Pruning conservatively excludes impossible matches rather than precisely predicting result counts.

Page indexes are a separate optional layer, not row-group statistics under another name. Parquet describes ColumnIndex and OffsetIndex for locating skippable pages.[4] Verify writing and consumption independently. Apache Arrow's write_table documentation states that sorting_columns declares ordering but neither sorts nor checks it. It also notes that PyArrow's reader does not yet use the page index.[5] Setting a writer option without execution evidence does not establish an optimization.

Twelve complete orders explain two physical layouts

The following twelve records are the complete teaching input. All dates are in November 2026 and every status is completed. Field order is order_id, order_day, plant, qty: 1, 11-01, A, 10; 2, 11-01, B, 20; 3, 11-01, A, 30; 4, 11-01, B, 40; 5, 11-02, A, 50; 6, 11-02, B, 60; 7, 11-02, A, 70; 8, 11-02, B, 80; 9, 11-03, A, 90; 10, 11-03, B, 100; 11, 11-03, A, 110; 12, 11-03, B, 120. Store full DATE values in files; abbreviated dates here are for readability only.

The query selects completed orders for A on November 2. IDs 5 and 7 qualify, giving count 2 and quantity 50 + 70 = 120. The full-table quantity is 780, an auxiliary baseline rather than a substitute for key and row equality. Freeze these expectations before creating files. If optimization changes the answer to 60 or 240, investigate data correctness before celebrating reduced latency.

To explain the mechanism, assume exactly four rows per teaching group. This is a manual grouping, not advice to configure DuckDB with four-row groups. Layout A follows ID order: group one contains 1–4, all November 1; group two contains 5–8, all November 2; group three contains 9–12, all November 3. Date statistics exclude groups one and three. Four rows remain candidates, and the plant predicate selects two.

Layout B contains identical records in interleaved order: group one is 1, 5, 9, 2; group two is 3, 6, 10, 7; group three is 4, 8, 11, 12. Every group's minimum date is November 1 and maximum November 3, and every group contains both A and B. These statistics exclude no group. Twelve rows remain candidates, but the final answer must still be two orders and quantity 120.

Compared with B, A reduces candidate groups from three to one and candidate rows from twelve to four. This does not establish a two-thirds reduction in bytes, much less elapsed time. Compressed column sizes, page layout, coalesced reads, caching and request overhead change actual cost. The calculation establishes how ordering changes statistical exclusion, not a software speedup.

Putting A in three date directories lets the partition condition exclude two days of files. If the selected date contains one group, the plant filter still examines its four records. Splitting further by plant reduces candidates but increases files; it is plainly not a production recommendation for twelve records. This small dataset makes input-to-output reconciliation complete. Production layout selection requires representative volume and query distributions.

Inspect actual metadata and scan evidence in the engine

First, write the same frozen input into separate directories with different row orders, using identical types, compression and writer implementation. Sort complete records, never columns independently. Expand the teaching data enough to create multiple real row groups, retaining a reproducible generation rule and expected answer. Do not overwrite a serving directory in place and risk queries mixing old and new files.

Second, inspect actual groups rather than assuming parameters determine exact boundaries. DuckDB supplies parquet_metadata and parquet_schema inspection functions.[3] The diagnostic example SELECT * FROM parquet_metadata('orders_sorted.parquet'); can reveal group identifiers, row counts, statistics and compressed bytes. Use the field names returned by the deployed version. This example was not run, so it does not establish that any generated file meets expectations.

DuckDB 2.0 Parquet Tips describes ROW_GROUP_SIZE as a minimum-row parameter constrained by vector size, documenting a lower bound of 2048.[6] The four-row illustration therefore cannot be used directly as its COPY setting. PyArrow's row_group_size has its own semantics.[5] Compare actual files across writers rather than assuming similarly named settings create identical layouts. Name the writer in every test record.

Third, run the complete non-partitioned query example: SELECT COUNT(*) AS order_count, SUM(qty) AS total_qty FROM read_parquet('orders_sorted.parquet') WHERE order_day = DATE '2026-11-02' AND plant = 'A' AND status = 'completed'; Run the same condition against the interleaved version. Expected output for the twelve records is 2 and 120. After enlarging the dataset, recalculate expectations from the generator instead of retaining the small-example answer.

Fourth, inspect projections and filters with EXPLAIN, then use analysis or profiling supported by the deployed version to observe execution. If available evidence contains output rows and operator timing but not read bytes or skipped groups, report only what is visible. Storage request logs, transfer totals and metadata can help estimate candidate ranges, but label estimates. Potentially skippable groups are not observed skipped groups.

Fifth, measure first and repeated access separately and state whether caching is controlled. Do not describe warm-cache results as cold reads. Fix hardware, threads, storage and output-consumption behavior; alternate layouts and retain multiple runs rather than selecting the fastest. Alongside latency, record files, metadata volume, scanned bytes, CPU, peak memory and answer equivalence. Mark unavailable measurements as unmeasured.

Choose date partitioning or sorting using a query mix

Option one keeps a modest number of larger files ordered by frequent predicates, such as date then plant, with an appropriate number of groups. It reduces directory and small-file management and suits time-range queries with batch preparation. Appending batches can widen overlapping ranges again, however, and a secondary sort helps within equal leading-key ranges. Date-first order does not guarantee efficient whole-year searches for one plant.

Option two partitions at a low-to-moderate-cardinality date granularity, then sorts and compacts within partitions. Daily versus monthly partitions depend on volume, query spans and rewrite frequency. Range exclusion and replacement of historical partitions become easier, while enumeration, manifests and late-arriving-data maintenance become more demanding. Derive dates from the business-time contract, not upload time simply because it is convenient.

Do not create one directory for each high-cardinality order ID. Even a query matching few files may pay heavily for discovery, permission checks and metadata. Compaction should not maximize file size blindly either: large groups can limit selective pruning and usable parallelism, while tiny groups add metadata and scheduling overhead.[6] Evaluate the actual mixed workload instead of treating a default row count as universally optimal.

Freeze at least three query classes: narrow date-plus-plant filters, cross-month aggregations and order-ID investigations. Record frequency, deadlines, scan cost and maintenance for each. If a date layout helps daily reports but makes critical investigations unacceptable, retain a specialized index or another serving path rather than forcing every query into one layout. Using Parquet does not require abandoning database indexes.

Include sorting, compression, compaction, historical rewrites and additional storage in total cost. An internal account can compare resources saved by queries with resources added by preparation and maintenance, using consistent units. CPU seconds cannot be directly subtracted from currency. Infrequent queries or small datasets may justify the simpler layout. Optimize the full workload rather than only the fastest demonstration.

Failure tests require expected results, evidence and retesting

Test one targets temporal boundaries. Include records at the interval start, immediately before its end and exactly at its end. For an inclusive-start, exclusive-end condition, only the first two classes qualify. Inspect keys and converted times, not counts alone. Correct mistaken equality in both filters and partition rules, then retest day and month transitions. Timestamp cases additionally require verified time-zone semantics; the DATE example does not supply that assurance.

Test two removes usable statistics or widens their ranges. Write the same data without statistics and with shuffled order. Results must remain identical, while absent bounds cannot justify exclusion. Inspect metadata and execution evidence. Correct by rewriting statistics, changing order or explicitly accepting a full scan. Retest both matching and nonmatching predicates so optimization cannot silently remove legitimate records.

Test three introduces null dates and unknown partitions. Add an order with a null date, then query both a particular date and null dates. The equality query excludes the new row, while the null query finds it under the agreed policy. Missing minima are not earliest dates, and unrecognized directories must not silently disappear. Inspect null counts, manifests and keys; establish explicit unknown-value handling and reconcile full and grouped counts under the contract.

Test four changes direct date predicates to complex formatting or user-defined functions. Results may remain equivalent while pushdown becomes unavailable. The expected invariant is semantics, not presumed pruning. Inspect filter placement and transfer measurements. If needed, store a canonical field in advance or use an equivalent expression the engine recognizes. After correction, compare results first and scans second; performance does not justify changing business meaning.

Test five places a November 2 record in a November 1 directory. Directory pruning can then exclude valid data. Independently verify partition membership against the original business date, not merely an identically named field the reader inferred from the path. Quarantine the wrong file, rebuild the partition and manifest, and compare keys in both directions between a complete scan and partition-filtered results. Empty differences establish no omissions or additions only within the tested scope.

Also inject missing files, duplicate files and interrupted compaction. Keeping old small files alongside their replacement can double-count orders even when each file is valid. Failure should block the new serving version while the old manifest remains available. Inspect key sets, duplicate counts, quantity totals and release version. Do not hide duplicate writes with indiscriminate DISTINCT; restore a complete, unique file collection.

Separate physical optimization from data publication

A reusable acceptance record contains the input version and complete manifest; types, units and null rules; partition-key provenance; actual group counts and ranges; queries and expected keys; visible plan and scan evidence; cold/warm conditions; preparation and maintenance cost; failure-test results; and cutover and rollback owners. Save real values or explicit unmeasured fields, not one headline optimization percentage.

Use a new directory and fixed manifest for a new version. Verify completeness before switching consumers together. Whether storage and the query engine provide atomic manifest changes depends on the actual system; directory renaming is not a universal cross-platform transaction. Retain old data according to access and retention policies. Confirm the old manifest remains readable and identify incoming records it does not contain before rollback.

Physical changes should not silently change business fields. If compaction also changes types, monetary units or time definitions, perform separate schema-compatibility and semantic-contract acceptance for the data pipeline. Performance tests establish behavior for specified data and queries, not meaning or consumer compatibility. For AI analytics, test actual generated predicates rather than only an engineer's ideal SQL.

Monitor small-file counts, files per partition, widening statistical ranges, scan volume for common queries and compaction backlog. Late arrivals can fragment a previously organized partition, so design repair frequency alongside business freshness requirements. Do not defer publication indefinitely for compression efficiency. If new queries predominantly filter another dimension, reassess the layout rather than assuming the original order remains effective.

An experiment with no improvement is an acceptable result. If volume cannot justify preparation or queries mostly need full scans, keep the simple approach and record why. Expand only after answer equivalence, scan evidence, representative workload benefits and sustainable maintenance are established. An explainable decision not to optimize is a stronger engineering deliverable than an unsupported speed promise.