Optimized query evaluation using cooperative sorts

Abstract

Abstract-Many applications require sorting a table over multiple sort orders: generation of multiple reports from a table, evaluation of a complex query that involves multiple instances of a relation, and batch processing of a set of queries. In this paper, we study how multiple sortings of a table can be efficiently performed. We introduce a new evaluation technique, called cooperative sort, that exploits the relationships among the input set of sort orders to minimize I/O operations for the collection of sort operations. To demonstrate the efficiency of the proposed scheme, we implemented it in PostgreSQL and evaluated its performance using both TPC-DS benchmark and synthetic data. Our experimental results show significant performance improvement over the traditional non-cooperative sorting scheme.

Permuting data on random-access block storage

“…Our work can be seen as a more general framework that systematically exploits such opportunities and other less obvious ones-such as using data sorted by (C, D) to produce data sorted by (D, C) more efficiently. Recent work on cooperative sorting [3] shares a similar motivation. The main idea there is to perform multiple sorts simultaneously in order to share work; the approach is still sorting-based and targets hard drives.…”

“…We see that there are (shaded) "gaps" in the decomposed address spaces. For instance, address 0, 1, 2 in T 3, (2,3) has no record associated with it, even though all digit values are less than their respective radices; however, the same address 0, 1, 2 is valid in T (3,2),3 , a decomposition with the same radices as T 3,(2,3) but nonetheless different. Eq.…”

“…However, dummy records take space in files, making each pass more expensive; injecting dummy records into the original input and removing them from the final output also incur extra cost. Worse, the space overhead of padding is exponential in the 3 Intuitively, segment and block boundaries divide the input file into parts. Each part incurs a block read, and there are a total of number of digits-given an n-digit original address space, if all digits need to be decomposed and if, for each digit, valid addresses make up for a fraction f < 1 of the address space decomposed from this digit, then only f n fraction of the addresses in the overall decomposed address space are valid.…”

“…Having seen how the algorithm operates, we can now justify our calculation of partition size in Eq. (3). Consider the output address space.…”

“…Among the four-pass strategies, ADP sr will consider the one that admits (4, 4, 3, 3) digits in each of the four passes. Less balanced strategies, e.g., (3,3,3,5) and (4, 4, 4, 2), will not be considered.…”