nuno-faria commented on code in PR #209:
URL: https://github.com/apache/datafusion-site/pull/209#discussion_r4366998791
##########
content/blog/2026-10-17-point-queries.md:
##########
@@ -0,0 +1,629 @@
+---
+layout: post
+title: Best Practices for Single Row Lookups
+date: 2026-10-17
+author: Nuno Faria (INESC TEC)
+categories: [performance]
+---
+
+<!--
+{% comment %}
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to you under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+{% endcomment %}
+-->
+
+[TOC]
+
+This blog post covers different techniques to improve the performance of point
+queries in DataFusion. It is based on a
+[talk](https://nuno-faria.github.io/papers/df-point-queries.pdf) given at the
+Boston DataFusion meetup, in September 2026.
+
+
+## Introduction
+
+While DataFusion is commonly seen as an engine to run analytical workloads --
+which are comprised by queries that access large sets of data -- some of its
+users regularly require random lookups to large files. During the DataFusion
+meetup in Boston, I met several community members that shared their experiences
+with these types of workloads, and how they hit some of the same challenges I
+highlighted in my talk.
+
+For this blog post, I will cover some best practices on how to handle random
+lookups (aka point reads) based on my past experience with these workloads in
+DataFusion. Since we usually have many more queries to execute comparatively to
+analytical workloads, the challenge here is completing each query as fast as
+possible (< 1ms) to achieve a reasonable throughput.
+
+The first part goes over optimizations in the storage (in this case, Parquet
+files), while the second optimizes the execution in DataFusion.
+
+
+## Methodology
+
+The dataset we will be using here is formed by a single Parquet file with 100M
+rows (unless otherwise stated), using a key (`bigint`, unique) and value
+(`varchar`, md5 of the key) columns. Unless otherwise stated, the queries
tested
+retrieve a random row from the table by key. Each test builds on the last.
+
+
+## Storage
+
+### File Ordering
+
+Having the Parquet file sorted by key will allow random lookups to access the
+minimum amount of data, assuming our predicate is also based on the key. To
+confirm this, the first test measures the throughput of random reads against a
+sorted (`True`) and non sorted (`False`) file:
+
+<div align="center">
+ <img
+ src="/blog/images/point-queries/parquet_sort.svg"
+ width="400px"
+ class="img-fluid"
+ alt="Barchart comparing throughput with and without a sorted file."
+ />
+ <p style="max-width: 500px; font-style: italic">
+ <b>Figure 1</b>: Throughput with (<code>True</code>) and without
+ (<code>False</code>) a sorted Parquet file.
+ </p>
+</div>
+
+The overall throughput of the **sorted version is 6000x faster** than the
+non-sorted one (which is not even visible in the plot). In this case, the
+execution plans show that the non-sorted version needs to access, on average,
67
+row groups and 122 total pages, while the sorted version only accesses one page
+in one group.
+
+In case you are not familiar, Parquet files are divided into multiple row
+groups, with each column in each group divided into multiple pages (more
details
+in the official Parquet
+[documentation](https://parquet.apache.org/docs/file-format/)). DataFusion uses
+the min/max statistics for each group/page to determine if it needs to access
it
+or not. If the file is not sorted, the min/max bounds will be wide, forcing
more
+groups/pages to be accessed, as illustrated in the diagram below:
+
+<div align="center">
+ <img
+ src="/blog/images/point-queries/sort.svg"
+ width="400px"
+ class="img-fluid"
+ alt="Example structure of a sorted and non sorted Parquet file."
+ />
+ <p style="max-width: 500px; font-style: italic">
+ <b>Figure 2</b>: Example structure of a sorted and non sorted Parquet
+ file. The orange pages are the ones matched by the predicate
+ <code>k=20</code>.
+ </p>
+</div>
+
+### Additional Metadata
+
+By default, DataFusion writes Parquet files with dictionary encoding enabled.
+[Dictionary
+encoding](https://parquet.apache.org/docs/file-format/data-pages/encodings/)
+maps column values to integers in an attempt to compact the space used, working
+pretty well for columns that have many duplicate values. The figure below shows
+a small example of how it works in practice:
+
+<div align="center">
+ <img
+ src="/blog/images/point-queries/dictionary_encoding.svg"
+ width="450px"
+ class="img-fluid"
+ alt="Example of a column with and without dictionary encoding."
+ />
+ <p style="max-width: 500px; font-style: italic">
+ <b>Figure 4</b>: Illustration of dictionary encoding.
+ </p>
+</div>
+
+However, if a column contains many distinct values (which is the case in our
+dataset), the dictionary will only add overhead. The following test compares
+enabling (`True`, default) and disabling (`False`) the dictionary, showing that
+the latter achieves a higher throughput:
+
+<div align="center">
+ <img
+ src="/blog/images/point-queries/parquet_dictionary.svg"
+ width="400px"
+ class="img-fluid"
+ alt="Barchart comparing throughput with and without dictionary
encoding."
+ />
+ <p style="max-width: 500px; font-style: italic">
+ <b>Figure 5</b>: Throughput with and without dictionary encoding.
+ </p>
+</div>
+
+Another optional metadata commonly used in Parquet files are [bloom
+filters](https://parquet.apache.org/docs/file-format/bloomfilter/), which much
+like min/max statistics help speedup predicates. In the context of Parquet
+files, a bloom filter is an array of bits that can determine if a value is
+_definitely not_ or _maybe is_ in a specific row group. Figure 6 provides a
+small example of a bloom filter and two different predicates.
+
+<div align="center">
+ <img
+ src="/blog/images/point-queries/bloom_filters.svg"
+ width="300px"
+ class="img-fluid"
+ alt="Example of a bloom filter and two predicates."
+ />
+ <p style="max-width: 500px; font-style: italic">
+ <b>Figure 6</b>: Example of a bloom filter and two predicates: the
value
+ <code>orange</code> is not present since one of the bits it maps to is
+ 0; <code>blue</code> might be present since all the bits it maps to are
+ 1.
+ </p>
+</div>
+
+In our case, since the column we filter by only contains unique values and is
+already sorted, the min/max statistics suffice, and thus there is no need to
add
+bloom filters. By default, DataFusion already does not add bloom filters when
+writing Parquet files. The figure below checks the impact on throughput of
point
+reads on a file with (`True`) and without (`False`, default) these structures:
+
+<div align="center">
+ <img
+ src="/blog/images/point-queries/parquet_bloom_filter.svg"
+ width="400px"
+ class="img-fluid"
+ alt="Barchart comparing throughput with and without bloom filters."
+ />
+ <p style="max-width: 500px; font-style: italic">
+ <b>Figure 7</b>: Throughput with and without bloom filters.
+ </p>
+</div>
+
+In summary, at least for sorted files with many unique values, it is better to
+**disable both of these structures** when writing Parquet files.
Review Comment:
I added the links to that section and also to the structure one, as well as
some code examples.
<img width="707" height="500" alt="image"
src="https://github.com/user-attachments/assets/efc80ba5-3046-456e-9be1-5668ba1a89e3";
/>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
