Evaluating Learned Indexes for External-Memory Joins

TL;DR

This paper investigates whether learned CDF-based indexes can accelerate external-memory joins. It implements learned-indexed nested-loop joins and compares them to hash, sort, and traditional indexed joins across HDD/SSD, sorted/unsorted data, and memory constraints, using PGM, RadixSpline, RMI, and ALEX designs. The key finding is that, unlike in-memory settings, learned indexes primarily trade space for accuracy and do not substantially reduce I/O costs in external memory; however, the PGM index with sampling offers the best tradeoff for some workloads, delivering 1.1–1.7× faster joins than B-trees at the cost of 4× less space, while construction times remain significantly longer. The results emphasize that practitioners must account for I/O bottlenecks and memory budgets when considering learned indexes for external-memory query engines, with offline analytics scenarios being the most favorable use case.

Abstract

Joins are among the most time-consuming and data-intensive operations in relational query processing. Much research effort has been applied to the optimization of join processing due to their frequent execution. Recent studies have shown that CDF-based learned models can create smaller and faster indexes, accelerating in-memory joins. However, their effectiveness for external-memory joins, which are crucial for large-scale databases, remains underexplored. This paper evaluates the impact of learned indexes on external-memory joins for both sorted and unsorted data. We compare learned index-based joins against traditional join methods such as hash joins, sort joins, and indexed nested-loop joins on real-world and simulated datasets. Additionally, we analyze learned index-based joins across multiple dimensions, including storage device types, data sorting, parallelism, constrained memory environments, and varying model error. The detailed evaluation enables us to determine the most appropriate learned index to employ for external-memory joins. Our experiments reveal that, unlike in-memory settings, learned indexes in external-memory joins can trade off accuracy for space without significantly degrading performance. While learned indexes provide smaller index sizes and faster lookups, they perform similarly to B-trees in external-memory joins since the total amount of I/O, which dominates runtime, remains unchanged. Additionally, the construction times of learned indexes are approximately $1000\times$ longer, and although they are $2-4\times$ smaller than the internal nodes of a B-tree, these nodes only represent $0.4%-1%$ of the data size and typically fit in main memory.

Figures (10)

• Figure 1: Modeling the CDF using piecewise linear approximation and indexing into the individual linear models.
• Figure 2: Query path using a learned index. The inner levels direct the query to the approximate location in the dataset. A last-mile search is performed to find the query key.
• Figure 3: Illustration of the learned index-based join: Tables $R$ and $S$ (with $S$ being the larger table) reside on disk, while the learned index for $S$ is kept in memory. Pages of table $R$ are sequentially loaded into memory, and for each key, the learned index predicts the corresponding page in $S$ to perform the final lookup.
• Figure 4: An unclustered index using a learned index is built in two passes: the first pass samples the data to train the learned index, and the second pass uses the index to assign each data item to its predicted position.
• Figure 5: Index size, build time and performance of learned indexes and B-tree. Index Size is the space used by the index in main memory. ALEX did not finish building the index using 32GB of RAM for OSM/Books datasets.