Index scans using a finite LRU buffer: a validated I/O model

Reviewer: Fazli Can

Most database optimizers try to estimate the I/O cost of a query in order to choose the cheapest access path (index or sequential scan) using information available during compile time. Studies on the I/O cost of random accesses using indexes usually rely on two assumptions: unlimited buffer and unique records for each key value. This paper studies the problem while relaxing these assumptions. The authors first introduce the problem with a good literature review and provide terminology, which improves the readability of the paper. In the main body of the paper, they derive an iterative formula for I/O cost in terms of the number of page fetches from disk to a finite LRU buffer when scanning an unclustered index. Next, they derive two easy-to-compute approximations of this formula that are applicable in the typical situation of low duplication of index keys. Numerous experiments validate the theoretical results. In the experiments, the authors compare their I/O cost estimates with the actual I/O cost using the R* distributed relational DBMS, with the System R estimate, and with another useful estimate. In the conclusion of the paper, the authors provide a list of problems as a pointer to future research. These problems are the effects of (a) data values drawn from a nonuniform distribution, (b) records not randomly placed on pages, and (c) a variable number of records per page. This well-written paper is useful for researchers working on physical database design and performance evaluation. Its results are applicable whenever random accesses are made using indexes.