6.2 Columnar Scanning: Pruning, Vectorization, and Deferred Materialization

The analyst needs only three columns and a small number of rows, yet the executor reads the entire wide table. Ah Hua decides to break "reading less data" into measurable mechanisms.

"Predicate pushdown" is often treated as a one-size-fits-all solution, but in reality it encompasses at least four distinct actions: reducing columns, using metadata to exclude data units, filtering at the reader level, and deferring or materializing other columns. Only by separating these components can we explain why a query still ends up reading so many bytes.

Four-Level Work Reduction

1. Projection pushdown / column pruning

The query only needs item_type and value_cents; the reader does not load other column chunks.

SELECT item_type, SUM(value_cents)
FROM items
GROUP BY item_type;

This delivers the most stable benefit from columnar storage, but nested schemas, SELECT *, UDFs, and hidden columns can expand the required set.

2. Partition pruning

Dataset paths or catalog partition expressions:

year=2025/month=01/part-....parquet
year=2005/month=02/part-....parquet

WHERE year=2025 AND month=2 can exclude other partitions before opening any file. Partition fields come from the path or catalog, not from column statistics within the file.

High-cardinality partitions generate many small files, directories, and metadata entries; low-cardinality ones may result in insufficient pruning. Partition keys should align with common filters and data lifecycle patterns.

3. Metadata pruning

If a row group’s value_cents.max < 50000, a predicate value_cents >= 50000 can prove the entire row group is non-matching.

This depends on:

• Reliable and available statistics;
• Compatible comparator types, NaN, and NULL semantics;
• Reader implementation support;
• The ability to convert the predicate into a metadata condition;
• Data clustering that produces sufficiently narrow min/max ranges.

Min/max overlap only indicates "possible match", it cannot prove that every row within the group matches. When statistics are missing or truncated, conservative reading is typically required.

4. Reader-side filter evaluation

After candidate pages are decoded, the reader evaluates the predicate on vectors or batches, generating a selection bitmap or vector. Only rows that truly match proceed to downstream operators.

This still involves reading and decoding predicate columns. Whether output-only columns can be deferred depends on format, page alignment, reader design, and engine capabilities, not all Parquet readers support low-cost gathering of arbitrary row positions.

PyArrow Dataset Example

The following APIs require PyArrow to be installed in an isolated environment. First, install PyArrow and record the version:

python3 -m pip install pyarrow

Create a sample file with two row groups:

import pyarrow as pa
import pyarrow.parquet as pq


table = pa.table({
    "item_id": pa.array(range(1, 9), type=pa.int64()),
    "item_type": [
        "weapon", "armor", "consumable", "weapon",
        "armor", "weapon", "consumable", "armor",
    ],
    "value_cents": pa.array(
        [12000, 8000, 1500, 20000, 95000, 180000, 5000, 70000],
        type=pa.int64(),
    ),
    "description": ["x" * 200] * 8,
})

pq.write_table(
    table,
    "vault-items.parquet",
    row_group_size=4,
    compression="zstd",
)

Project two columns and filter the data:

import pyarrow.dataset as ds


dataset = ds.dataset("vault-items.parquet", format="parquet")
result = dataset.to_table(
    columns=["item_type", "value_cents"],
    filter=ds.field("value_cents") >= 50000,
)

assert result.column_names == ["item_type", "value_cents"]
assert result.num_rows == 3
print(result)

The PyArrow Dataset API performs projection and filtering, and leverages partition or internal metadata when possible. Otherwise, filtering occurs on the loaded record batches. It is not possible to verify from the final result alone whether a row group was skipped; this requires cross-referencing with fragment metadata, scanner logs, traces, and I/O counters.

Official API: Apache Arrow Dataset.

Check Parquet metadata

import pyarrow.parquet as pq


parquet_file = pq.ParquetFile("vault-items.parquet")
metadata = parquet_file.metadata

print("rows", metadata.num_rows)
print("row_groups", metadata.num_row_groups)

for rg_index in range(metadata.num_row_groups):
    row_group = metadata.row_group(rg_index)
    print("row_group", rg_index, "rows", row_group.num_rows)
    for col_index in range(row_group.num_columns):
        column = row_group.column(col_index)
        stats = column.statistics
        print(
            column.path_in_schema,
            "compressed", column.total_compressed_size,
            "min", None if stats is None else stats.min,
            "max", None if stats is None else stats.max,
        )

Metadata inspection only displays information recorded by the writer. Minimum and maximum values of sensitive columns might leak information; the format or platform may allow disabling or truncating statistics.

Vectorized execution

The row-at-a-time "volcano" model processes one row per operator invocation, leading to function calls, branching, and poor cache locality, each contributing to CPU overhead. In contrast, a vectorized engine processes a batch or vector at once:

values:    [12000, 8000, 95000, 180000, ...]
predicate: value >= 50000
selection: [false, false, true, true, ...]
aggregate selected values in tight loop

Benefits:

• Amortizes the cost of virtual function calls and overhead;
• Contiguous typed arrays improve cache locality and prefetching;
• Enables more effective use of SIMD instructions by compiler and runtime;
• Selection vectors avoid the need to copy entire rows immediately.

Vectorization does not equate to automatic SIMD usage, nor does it guarantee that larger batches are always better. Large batches can increase latency and memory pressure, and CPU performance may still be limited by branch-heavy UDFs, variable-length strings, or decompression operations.

Late materialization

Early materialization combines columns into rows or tuples earlier in the process. In contrast, late materialization preserves column vectors and selection vectors as much as possible, only combining values into full rows when necessary, such as during output generation or join operations.

decode predicate columns
  -> evaluate filter -> selection vector
  -> aggregate/join using selected positions
  -> fetch/decode output-only columns when useful and supported
  -> materialize final result

However, even when only a few values from a row are needed (such as reading specific fields by row index) additional work may still be required, including:

• Locating the pages that contain those rows;
• Reading compressed pages;
• Decompressing and decoding page prefixes and dictionaries;
• Processing nested data levels.

In cases of high selectivity or fragmented position sets, late gathering can end up more expensive than sequentially decoding entire batches. The optimizer must therefore compare the selectivity of the query against the cost of decoding versus gathering operations.

Data Skipping and Sorting

If value_cents is randomly distributed across files and each row group's min/max spans a wide range, predicates struggle to skip irrelevant data. Sorting or clustering by frequently used filter keys can significantly narrow the search ranges:

RG0 value: 0..9,999
RG1 value: 10,000..19,999
RG2 value: 20,000..29,999

However, a single physical ordering cannot optimize all dimensions simultaneously. Sorting by date helps with date-based pruning but may hinder performance for tenant or item_type filters. A balanced approach can be achieved through partitioning with sorting, data skipping indexes, or multiple projections and materialized views.

Small-file problem

A large number of tiny Parquet files can lead to:

• Larger listing/catalog metadata;
• Increased object-store requests;
• A higher proportion of footer reads and scheduler tasks;
• Too small compression and dictionary scopes;
• Higher query startup latency.

Compaction or coalescing must preserve partitioning, ordering, and statistics, while also accounting for concurrent writers and failure atomicity. Simply concatenating files is insufficient.

The Real Boundary Between Column Store and OLTP

A columnar store can support updates through mechanisms like delta stores, delete bitmaps, primary-key indexes, MVCC, and background merges. Similarly, a row store can perform analytics using covering indexes, columnar replicas, and vectorized scans.

When selecting a system, evaluate the workload characteristics across these dimensions:

• Point lookups and narrow updates
• Scan projection width or selectivity
• Ingest batch size and data freshness
• Join, grouping, and sorting operations
• Transactional and constraint requirements
• Compaction, merge, and mutation tail behavior
• Concurrency, replication, and recovery capabilities

Acceptance Checklist

• [ ] Do not count column pruning as storage compression;
• [ ] Distinguish between partition pruning, metadata pruning, and row filtering;
• [ ] Clearly state that statistics can only conservatively prove "no match is possible";
• [ ] Explain why late materialization does not equate to zero-cost random reads of any row;
• [ ] Demonstrate skip decisions using actual metadata and I/O data, not by guessing from results;
• [ ] Report row-group size, sort/partition structure, codec, and reader version.

Chapter Summary

Columnar performance stems from the synergy of layout, encoding, metadata, and execution: read as few columns as possible, use statistics to eliminate mismatched units, filter and aggregate on vectors, and materialize results only when absolutely necessary. The next chapter compares hash indexing and external sort, each leveraging equality hashing and bounded-memory run merge, respectively.