Indexing Join Inputs for Fast Queries and Maintenance

TL;DR

This paper introduces a hybrid indexing method called the merged index that combines the advantages of traditional indexes and materialized join views, enabling fast, efficient, and non-blocking join query processing and maintenance.

Contribution

The paper presents the merged index, a novel hybrid index structure that supports all join types with high-bandwidth maintenance and non-blocking query processing, outperforming traditional indexes and views.

Findings

Query performance comparable to materialized join views.

Maintenance efficiency similar to traditional indexes.

Supports all join types with non-blocking processing.

Abstract

In database systems, joins are often expensive despite many years of research producing numerous join algorithms. Precomputed and materialized join views deliver the best query performance, whereas traditional indexes, used as pre-sorted inputs for merge joins, permit very efficient maintenance. Neither traditional indexes nor materialized join views require blocking phases, in contrast to query-time sorting and transient indexes, e.g., hash tables in hash joins, that impose high memory requirements and possibly spill to temporary storage. Here, we introduce a hybrid of traditional indexing and materialized join views. The *merged index* can be implemented with traditional b-trees, permits high-bandwidth maintenance using log-structured merge-forests, supports all join types (inner joins, all outer joins, all semi joins), and enables non-blocking query processing. Experiments across a wide range of scenarios confirm its query performance comparable to materialized join views and maintenance efficiency comparable to traditional indexes.