Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Database Theory in Action: Direct Access to Query Answers

Jiayin Hu, Nikolaos Tziavelis

TL;DR

The paper addresses direct access to query answers by rank under a given order, bridging theory with practice by implementing direct-access and single-access algorithms across lexicographic and sum orders. It presents SQL-based expressions (OFFSET/LIMIT and a ROW_NUMBER() window with a CTE) and an independent implementation that covers a broad class of conjunctive queries, including full and partial rankings, with automatic tractability checks. Through experiments, it compares against PostgreSQL's strategies, showing competitive performance for large results and a clear break-even point after a few accesses where direct access becomes advantageous. The work highlights practical implications for interactive querying and histogram construction, and outlines future directions for tighter DBMS integration and parallelization.

Abstract

Direct access asks for the retrieval of query answers by their ranked position, given a query and a desired order. While the time complexity of data structures supporting such accesses has been studied in depth, and efficient algorithms for many queries and common orders are known, their practical performance has received little attention. We provide an implementation covering a wide range of queries and orders; it allows us to investigate intriguing practical aspects, including the comparative performance of database systems and the relationship between direct access and its single-access counterpart.

Database Theory in Action: Direct Access to Query Answers

TL;DR

The paper addresses direct access to query answers by rank under a given order, bridging theory with practice by implementing direct-access and single-access algorithms across lexicographic and sum orders. It presents SQL-based expressions (OFFSET/LIMIT and a ROW_NUMBER() window with a CTE) and an independent implementation that covers a broad class of conjunctive queries, including full and partial rankings, with automatic tractability checks. Through experiments, it compares against PostgreSQL's strategies, showing competitive performance for large results and a clear break-even point after a few accesses where direct access becomes advantageous. The work highlights practical implications for interactive querying and histogram construction, and outlines future directions for tighter DBMS integration and parallelization.

Abstract

Direct access asks for the retrieval of query answers by their ranked position, given a query and a desired order. While the time complexity of data structures supporting such accesses has been studied in depth, and efficient algorithms for many queries and common orders are known, their practical performance has received little attention. We provide an implementation covering a wide range of queries and orders; it allows us to investigate intriguing practical aspects, including the comparative performance of database systems and the relationship between direct access and its single-access counterpart.
Paper Structure (6 sections, 1 figure)

This paper contains 6 sections, 1 figure.

Table of Contents

  1. Introduction
  2. Direct Access in Database Systems
  3. Direct Access in SQL
  4. PostgreSQL vs Theoretical Algorithms
  5. Direct Access vs Single Access
  6. Conclusion

Figures (1)

  • Figure 1: Experiments on a 3-way join $R(A,B) \bowtie_B S(B,C) \bowtie_C T(C,D)$ using synthetic data. The join result size is controlled by the sampled domain size. Left (a): Retrieving the median answer under a full lexicographic order $A \rightarrow B\rightarrow C\rightarrow D$. Middle (b): Time for different $k$ values for relation sizes equal to $10^4$ and a Uniform distribution with a Large join result. Right (c): Ratio of direct access over single access time for accessing the median answer under a full lexicographic order.