Dynamic optimization of queries in pivotbased indexing
 1.1k Downloads
 2 Citations
Abstract
This paper evaluates the use of standard database indexes and query processing as a way to do metric indexing in the LAESA approach. By utilizing Btrees and Rtrees as pivotbased indexes, we may use wellknown optimization techniques from the database field within metric indexing and search. The novelty of this paper is that we use a costbased approach to dynamically evaluate which and how many pivots to use in the evaluation of each query. By a series of measurements using our database prototype we are able to evaluate the performance of this approach. Compared to using all available pivots for filtering, the optimized approach gives half the response times for main memory data, but much more varied results for disk resident data. However, by use of the cost model we are able to dynamically determine when to bypass the indexes and simply perform a sequential scan of the base data. The conclusion of this evaluation is that it is beneficial to create many pivots, but to use only the most selective ones during evaluation of each query. Rtrees give better performance than Btrees when utilizing all pivots, but when being able to dynamically select the best pivots, Btrees often provide better performance.
Keywords
Similarity search Pivotbased indexing Database trees Optimized query processing1 Introduction
Similarity search is gaining interest both for structured and unstructured objects. It is also important in domains where canonical ordering of data is not possible, for instance multidimensional vector spaces or general metric spaces. The domains we will investigate contain either large vector spaces, or prohibitively expensive exact distance calculations, making a full scan to answer similarity queries costly. A query is typically formalized as a sample object, and the query is evaluated against a database of objects by issuing comparison of similarity.
There are many applications of metric indexing and search. These range from entertainment and multimedia to science and medicine, or applications that require efficient querybyexample, but where traditional spatial access methods cannot be used. Beyond direct search, similarity retrieval can be used internally in a wide spectrum of systems, such as nearest neighbor classification, compressed video streaming and multiobjective optimization [12].
Our motivation is to exploit database internals such as index structures and query processing as a means to solve similarity queries. We have chosen to work with the pivotbased LAESA approach to metric indexing and search [17]. There are several existing works that have done similar research [14, 15, 23], and we build on these by exploiting more database internal techniques, having direct access to indexes, buffers and algebraic query processing capabilities.
Our approach has been to exploit B+trees and R*trees and to use parallel hash joins in between sequential range scans of these ordered indexes. Our conclusion is that R*trees seem to be best, especially at querying time. The use of these indexes is dependent on the query, i.e., the larger the range limit of the query, the less useful the indexes become. Furthermore, the query will be most efficiently processed using statistics on how many and which indexes to use. This is well known in database systems since the SystemR days [21].
We have exploited this idea and created statistics for each access path, both for scans, B+trees and R*trees. Before we evaluate a query we calculate the estimated cost of executing the query using different access paths. This allows us to use the most optimal query execution strategy based on a dynamic optimization for each query. When sequential scans are estimated to be cheapest, this will be chosen to evaluate the query.
The main contribution of this paper is the application of statistics to support query evaluation. This is a technique that could be of use to several existing metric indexing methods, but rather than trying to compare the relative merits of of such methods, we have focused on one approach, to to demonstrate the potential for improvement. We use a pivot filtering approach similar to OMNI [23], because of its simplicity, and because the use of B+trees and R*trees highlights the strenghts of our method.
The organization of this paper is as follows. We start by comparing our approach to similar works on database indexes and pivot selections. Then, a description of the architecture and the design is given. We present a set of initial results using our database prototype. The results of our optimization is presented and, finally, some concluding remarks are given and directions for further research are outlined.
2 Related work
The starting point for this research is LAESA [17], which is based on precomputed distances between the objects in the database. Instead of indexing the distance between all pairs of objects, as done in the AESA approach [20], only a fixedsize subset is used as sample objects, or pivots. AESA is regarded as the best method for filtering, but it relies on storing precomputed distances between all object. LAESA, which relies on using a reduced pivot set, lowers the time and memory complexity, while increasing the number of distance calculations needed during query processing. This could be a tradeoff between indexing time and memory usage against query processing time. The LAESA algorithm starts with one arbitrary pivot and with this it scans every object to eliminate and select candidates. At the same time it applies a heuristic to select the next pivot to use for elimination and selections. Similar online pivot selection approaches are used in AESA and iAESA [9] as well. Unlike these methods, we store the distances from a pivot to all other object in an ordered index, which allows us to skip large parts of the object scans done in LAESA. Since LAESA calculates the next pivot based on the previous ones, it is also somewhat harder to parallelize.
Spaghettis [6] is similar to LAESA, but this method sorts the distances columnwise, and uses binary search to find ranges of candidate objects. Furthermore, Spaghettis uses links inbetween the columns such that an object in one column is linked to the same object in the next column. Unlike Spaghettis, we create these links after filtering by using standard database joins.
The work that is most similar to ours is the OMNI approach [23]. OMNI is based on the selection of several foci (pivots), and indexing the distance from all objects to all pivots. Metric range queries can then be performed using range queries from each focus and intersecting the results, while kNN queries can be performed with a predefined or estimated range query followed by a post processing step. OMNI concludes that the number of pivots should follow the intrinsic dimensionality of the data set, while our results show that the query and its radius is very important on deciding which pivots and which number of pivots to use.
iDistance [15] is based on using several pivots and partitioning the data set according to distance to the nearest pivot. The unique aspect of iDistance is how the distances are stored. The distances between pivots and their data objects are stored in intervals as a part of one large B+tree. MB+tree [14] stores two different trees: a B+tree for the objects and an additional mainmemory block tree that is used to partition the B+tree. None of these two approaches allows for using cost estimation and dynamic optimization as a technique to improve the efficiency of query processing.
Bustos et al. [4] propose to select a set of pivots carefully by having an efficiency criterion that maximizes the probability of filtering. Based on this an incremental algorithm is developed that allows to add pivots dynamically to the index. SSS [18] is another dynamic algorithm for offline selecting pivots. An object is chosen as a new pivot at insertion time, if it is far enough away from the existing pivots. Empirically they have found “far enough” to be Mα, where M is the maximum distance found and α is a number in the range from 0.35 to 0.40. These two works are combined by Bustos et al. [5]. When a new pivot is to be added to the index, it is checked whether any existing pivots has become redundant, i.e., its contribution has become low according to the efficiency criterion. All these three works show how to select good pivots offline from a large set of pivots. This is orthogonal to our work, in the sense that our algorithms may work with any set of pivots.
Ciaccia et al. [8] show how to estimate the cost of query evaluation using Mtrees. This information is used to tune the Mtree to minimize CPU and I/O cost. However, they do not consider pivotbased methods, where you may use this to dynamically optimize the processing of each query.
Baioco et al. [1] argue that the selectivity estimation techniques should consider the distribution given by the intrinsic dimension, which is usually lower than the representational dimension. They develop a cost estimation technique based on the correlation fractal dimension for Slim trees [22].
Fredriksson [11] extends the Hierarchy of Clusters (HC) tree to handle varying query radii. In other words, the optimal degree of unbalance in the structure depends on the query radius, so several indexes are built, and the one that will work best for a given query is selected. This is similar to our work; however, we may use the same indexes for any query radius.
3 Pivotbased indexing and querying
The indexing method is based on LAESA [17] where precomputed distance calculations are the foundation. The distance from some of the data objects, the pivots, are precomputed to all other objects in the database. These distances are stored such that traditional databasetype range queries are used to filter the data objects, resulting in a small set of objects that will need exact distance calculations.
3.1 Metric spaces
3.2 Indexes

B+trees

R*trees
3.3 Filtering and query evaluation
Filtering is the most important operation in a metric indexing system, and is where the different indexing methods we propose are important.
The filtering process must be supplied with two parameters, the query object q and a range limit r. In addition a set of indexes supporting range queries are needed. The process starts by performing a range scan on each index file. For a data object to be within the given range, the distance from the pivot to the data object must also be within a given range. This is given by the triangle inequality, and results in the inclusion only of objects o _{ i } that satisfy d(p, q) − d(p, o _{ i }) ≤ r, where p is a pivot object. As d(p, o _{ i }) is precalculated and stored in the index, only d(p, q) must be calculated and all objects between d(q, p) − r and d(q, p) + r are returned as candidates for this pivot. By combining the filtering of several pivots, we may get a candidate set that is small.
For metric range queries the intersection of candidate sets are of interest. The candidate set returned from each index file is joined with the candidate set of every other index file. Only the objects that exists in every candidate set is returned. After doing this filtering we need to calculate the exact distance to every object in the resulting candidate set, the post processing step. This could be a costly step, depending on the complexity of the objects in question. With respect to Fig. 1 this means to take every object inside the dotted area and determine whether they are inside the circle with the broken perimeter.
4 Initial performance experiments
We have performed a set of initial measurements to evaluate the use of database indexes to support metric indexing and search.
4.1 NTNUStore
We have built a small database kernel for the sake of research on databases and search technology. The aim is to easily build new indexes and search algorithms to do experiments.
NTNUStore is based on NEUStore [24]. NTNUStore is a Java library made to experiment with query processing, buffer management and indexes. Currently, we have R*trees and B+trees as indexes. On the query processing side we have focused on efficient range scans and on parallel hash join processing.
4.2 Indexes
The B+trees are implemented for insertion and updates, but without support for node deletion. For our application this suffices because we never delete data from the database. Our B+tree allows for duplicate keys because the keys in the indexes are distances, which may very well be equal. For some type of data, e.g., document similarity, the distances are not well distributed, which gives many equal distances. Our B+tree assumes random insertions and splits blocks in the middle. The records in this application are of equal size, so a simple numberwise middle is utilized. The B+tree data are kept as objects when residing in memory, but are converted to a serialized form when written to disk. The opposite happens when a block is read from disk into memory.
The R*trees are chosen due to their way of doing insertions and block splits, where the optimal way is selected according to what resides in the Rtree block. The R*tree is more CPU intensive during insertions than the B+tree. This is mainly due to the CPU intensive algorithm for calculating minimum overlap between minimum bounding rectangles in the Rtree blocks. Like the B+tree, the R*tree also does a serialization to/from memory objects while being written and read to/from disk.
The records in the R*tree are minimum bounding rectangles, MBRs. Our R*tree uses two different criteria for choosing subtrees at insertions: Minimization of overlap of areas when operating at the leaf level, and minimization of the areas covered by each MBR when being at a nonleaf level [16].
4.3 Experiment setup
Initial values for experiments
R*tree dimensions  4 
Buffers  10,000 blocks 
Block size  8 KB 
Query range  0.2 
Objects  40,150 
Pivots  12 
Number of queries  50 
We have used the NASA data found at the SISAP Metric Space library [10], which is a set of 40,150 20dimensional feature vectors, generated from images downloaded from NASA and with duplicate vectors eliminated. This set of data fits in the buffer of the database. We have used two different distances on this set of data, both the traditional euclidean distance (L _{2} ) and the quadratic form distance (QFD) [2].
For querying, we separate into the filtering and the postprocessing phase. A higher number of pivots will give better filtering, resulting in less postprocessing, but increases the filtering cost.
In our experiments we have used a static selection of pivots which is based on the one used in OMNI [23]. The basic principle is to maximize the distance between the pivots. We calculate the pivots before starting to insert the objects. When trying to find a new pivot our algorithm maximizes the distance from the current set of pivots to the candidate pivot.
4.4 Number of pivots
Average size of result of filtering
Pivots  5  12  20 
Candidate set  879  162  125 
This shows that for evaluating a query, the query radius must be considered when deciding how many pivots and which indexes to scan. The intrinsic dimensionality of the NASA data using Euclidean distance is 5.2, according to the formula found in Chávez et al. [7]. Our results suggest that not only the dimensionality of the data is important in deciding the optimal number of pivots, but the query as well. This may be addressed by having many pivots, but to use only the optimal number and the most selective indexes when issuing a query, i.e., the indexes that retrieve fewest blocks from the database for that query. To implement this we need to maintain statistics about the selectivity of the index, i.e., by maintaining equidepth histograms.
5 Dynamic optimization – access path selectivity
Based on the initial runs shown we have seen that the optimal number of pivots is dependent on the query radius. This gave us the idea that the number of pivots, and which pivots to use, could be decided at run time for each query. This could be done by using traditional database optimizations techniques. By estimating the selectivity of different access paths, we could choose to use the most optimal ones for each query. Furthermore, by applying full cost estimation of different query evaluation plans we could decide on how many pivots to use in advance. Because each pivot is just a filter to remove irrelevant results, we may freely decide how many to use.
To do this we need to maintain statistics about each access path in the database. We choose to use equidepth histograms to represent statistics for the data distribution for each access path [13, 19]. These statistics are well suited for estimating the size of the result set.
For B+trees we create equidepth histograms simply by scanning blocks at level 1, the level above the leaves. We assume each leaf level block to contain the same number of records. When estimating the cost of a range query for a specific B+tree, we calculate the distance and estimate how large portion of the B+tree is within the range by counting the number of bins.
For R*trees we do something similar to B+trees, but in this case we find the fraction of overlaps between the query’s region and the regions of the records at level 1 in the Rtree. This gives a reasonable estimate of the size of the candidate set for each R*tree. The experiments with the NASA data suggests that this is a sufficient method for estimation of the selectivity for each R*tree.
6 The effect of dynamic optimization
We have run a few sets of measurements of the dynamic optimization. In the two first sets we have used the NASA data, 12 pivots and 50 queries. All results here show the accumulated query time for 50 queries. This includes both filtering and postprocessing times, where the query is compared with the candidate result set. For the dynamic optimization methods we have, of course, included the time to select indexes in the query times.
Different access paths used in the experiments
Scan  Sequential scan of all objects 
3R  Filtering using all 3 R*trees 
O1R  Filtering using the most selective R*tree 
O2R  Filtering using the two most selective R*trees 
12B  Filtering using all 12 B+trees 
O1B  Filtering using the most selective B+tree 
O2B  Filtering using the two most selective B+trees 
O3B  Filtering using the three most selective B+trees 
O4B  Filtering using the 4 most selective B+trees 
O5B  Filtering using the 5 most selective B+trees 
We have chosen to also measure the cost of sequential scan, because this is often the best solution when the distance function is cheap [3]. In our experiments the objects themselves are stored in a separate data B+tree. Sequential scan is supported by creating a cursor on the leaf level of this B+tree.
Query time for QFD using NASA data
Method  Scan  3R  O1R  O2R  12B  O1B  O2B  O3B  O4B  O5B 

Query time  1,701  182  115  89  1,336  371  218  214  248  285 
We have merely used the quadratic form distance (QFD) as an example of an expensive comparison function. In our measurements we have used the identity matrix, thus letting the similarity score be equal to the traditional L _{2} distance.
Query time for L _{2} using NASA data
Method  Scan  3R  O1R  O2R  12B  O1B  O2B  O3B  O4B  O5B 

Query time  213  174  77  78  1,350  230  183  192  246  283 
In this experiment the most optimal solution is to use the most selective R*tree (77 milliseconds). Sequential scan is 213 milliseconds and the best B+tree solution is using the two most selective B+trees (192 milliseconds). Using all 12 pivots is not optimal here either, but when using R*trees it is still better than sequential scan.
Which B+tree is most selective?
B+tree  0  1  2  3  4  5  6  7  8  9  10  11 
Queries  4  5  12  5  6  5  2  2  3  1  5  0 
Varying radii using the NASA data
Method  Scan  12B  O3B  Result size 

r = 0.05  1,707  442  28  58 
r = 0.1  1,950  824  72  87 
r = 0.2  1,714  1,323  202  884 
r = 0.4  1,882  3,156  690  6,490 
r = 0.8  1,882  7,720  2,872  14,902 
Query time for QFD using COLORS data
Method  Scan  3R  O1R  O2R  12B  O1B  O3B  O6B 

Query time  193,478  6,950  29,502  15,435  10,016  34,594  7,493  5,862 
Query time for QFD using COLORS data with 24 available pivots
Method  6R  O1R  O5R  24B  O1B  O4B 

Query time  2,879  18,265  2,240  9,304  10,114  1,531 
Query time for QFD using NASA with small buffer
Method  Scan  12B  O1B  O3B  O4B  O5B  O6B  O7B  O8B 

Query time  3,066  3,021  15,387  2,984  2,416  2,059  1,920  1,952  1,992 
Query time for L _{2} using vectors data
Method  Scan  3R  O1R  12B  O1B 

Query time  7,485  1,447,182  1,411,492  1,561,764  1,428,549 
All in all, by using statistics we are able to pick the most selective access paths for the queries issued, resulting in better response time. According to our measurements it doubles the performance for main memory data, and may give some improvements for disk resident data, but will often rely on sequential scan when there is bad selectivity in the indexes for the queries issued.
7 Conclusions and further work
The basic idea behind our research was to exploit knowledge of database structures and processing to support similarity search. We chose the LAESA method as a testbed for our approach.
We have performed experiments with various parameters and access structures. The initial conclusion is that Rtrees seem to be the winner. This is mainly due to the fact that many pivots are prejoined in each Rtree. It also has less demand on memory and disk I/O, because there are fewer blocks to scan. However, when being able to dynamically choose the most selective pivots, B+trees sometimes provide better performance because there are more pivots to choose from.
We discovered that the optimal number of pivots to be used is very dependent on the distribution of the data and on the query itself, i.e., the range limit used. Therefore, the system needs to consider how many and which of the indexes to use when evaluating the query. This is done by maintaining statistics, equidepth histograms, for each index and by using a cost model. By doing this we were able to chose the most selective indexes for each query dynamically. Our performance measurements show that this gives better performance than using a fixed set of pivots for most types and number of indexes we have tested. For disk resident data sequential scan is often the best solution.
By registering which pivots are most selective according to a query log, we could dynamically remove some pivots and try to create some new better ones. Our current plan is to extend our work by performing experiments with different types of data. By this we hope to gain further insight into the area and possibly to improve our method. We also plan to integrate the similarity search with traditional database type of queries, such that it becomes an integrated platform for the next generation search.
In our experiments, we have demonstrated improvements over direct pivot filtering, using all pivots, when applied in an OMNIlike setting. While this is perhaps the setting that most resembles the origins of our selection method, the method may well have wider applicability. In the future, it would be interesting to examine whether similar statisticsbased online pivot selection would be beneficial in other indexing methods, where the pivot filtering is based in inmemory distance tables.
Notes
Open Access
This article is distributed under the terms of the Creative Commons Attribution Noncommercial License which permits any noncommercial use, distribution, and reproduction in any medium, provided the original author(s) and source are credited.
References
 1.Baioco GB, Traina AJM, Traina C Jr (2007) An effective cost model for similarity queries in metric spaces. In: SAC ’07: proceedings of the 2007 ACM symposium on applied computing. New York, NY, USA, ACM pp 527–528CrossRefGoogle Scholar
 2.Bernas T, Asem EK, Robinson JP, Rajwa B (2008) Quadratic form: a robust metric for quantitative comparison of flow cytometric histograms. Cytometry, Part A 73A(8):715–726CrossRefGoogle Scholar
 3.Beyer K, Goldstein J, Ramakrishnan R, Shaft U (1999) When is “nearest neighbor” meaningful? In: Proceedings of the 7th international conference on database theory. Lecture Notes In Computer Science, vol 1540. SpringerVerlag, London, UK, pp 217–235Google Scholar
 4.Bustos B, Navarro G, Chávez E (2003) Pivot selection techniques for proximity searching in metric spaces. Pattern Recogn Lett 24(14):2357–2366zbMATHCrossRefGoogle Scholar
 5.Bustos B, Pedreira O, Brisaboa N (2008) A dynamic pivot selection technique for similarity search. In: SISAP ’08: proceedings of the first international workshop on similarity search and applications (sisap 2008). Washington, DC, USA, IEEE Computer Society, pp 105–112CrossRefGoogle Scholar
 6.Chávez E, Marroquín JL, BaezaYates R (1999) Spaghettis: an array based algorithm for similarity queries in metric spaces. In: Proceedings of the string processing and information retrieval symposium & international workshop on groupware (SPIRE). IEEE Computer Society, pp 38–46Google Scholar
 7.Chávez E, Navarro G, BaezaYates R, Luis J (2001) Searching in metric spaces. ACM Comput Surv 33(3):273–321CrossRefGoogle Scholar
 8.Ciaccia P, Patella M, Zezula P (1998) A cost model for similarity queries in metric spaces. In: Proc. 17th ACM SIGACTSIGMODSIGART symposium on principles of database systems (PODS’98), pp 59–68Google Scholar
 9.Figueroa K, Chávez E, Navarro G, Paredes R (2006) On the least cost for proximity searching in metric spaces. In: Àlvarez C, Serna M (eds) Proceedings of the 5th international workshop on experimental algorithms. Lecture notes in computer science, vol 4007. Springer, pp 279–290Google Scholar
 10.Figuerora K, Navarro G, Chavez E (2010) SISAP: metric space library. http://sisap.org/Home.html
 11.Fredriksson K (2007) Engineering efficient metric indexes. Pattern Recogn Lett 28(1):75–84CrossRefGoogle Scholar
 12.Hetland ML (2009) The basic principels of metric indexing. In: Coello Coello C, Dehuri S, Ghosh S (eds) Swarm intelligence for multiobjective problems in data mining, 2009. Published by SpringerVerlag, SpringerVerlagGoogle Scholar
 13.Ioannidis Y (2003) The history of histograms (abridged). In: VLDB ’2003: proceedings of the 29th international conference on very large data bases. VLDB Endowment, pp 19–30Google Scholar
 14.Ishikawa M, Chen H, Furuse K, Yu JX, Ohbo N (2000) Mb+tree: a dynamically updatable metric index for similarity searches. In: WAIM ’00: proceedings of the first international conference on webage information management. SpringerVerlag, London, UK, pp 356–373Google Scholar
 15.Jagadish HV, Ooi BC, Tan KL, Yu C, Zhang R (2005) idistance: an adaptive b+tree based indexing method for nearest neighbor search. ACM Trans Database Syst 30(2):364–397CrossRefGoogle Scholar
 16.Manolopoulos Y, Nanopoulos A, Papadopoulos AN, Theodoridis Y (2005) RTrees: theory and applications (advanced information and knowledge processing), 1st edn. SpringerGoogle Scholar
 17.Micó L, Oncina J, Vidal E (1994) A new version of the nearestneighbour approximating and eliminating search algorithm (aesa) with linear preprocessing time and memory requirements. Pattern Recogn Lett 15(1):9–17CrossRefGoogle Scholar
 18.Pedreira O, Brisaboa NR (2007) Spatial selection of sparse pivots for similarity search in metric spaces. In: SOFSEM ’07: proceedings of the 33rd conference on current trends in theory and practice of computer science. SpringerVerlag, Berlin, Heidelberg, pp 434–445CrossRefGoogle Scholar
 19.PiatetskyShapiro G, Connell C (1984) Accurate estimation of the number of tuples satisfying a condition. SIGMOD Rec 14(2):256–276CrossRefGoogle Scholar
 20.Ruiz EV (1986) An algorithm for finding nearest neighbours in (approximately) constant average time. Pattern Recogn Lett 4(3):145–157CrossRefGoogle Scholar
 21.Selinger PG, Astrahan MM, Chamberlin DD, Lorie RA, Price TG (1979) Access path selection in a relational database management system. In: SIGMOD ’79: proceedings of the 1979 ACM SIGMOD international conference on management of data. ACM, New York, NY, USA, pp 23–34CrossRefGoogle Scholar
 22.Traina C Jr, Traina A, Faloutsos C, Seeger B (2002) Fast indexing and visualization of metric data sets using slimtrees. IEEE Trans Knowl Data Eng 14(2):244–260CrossRefGoogle Scholar
 23.Traina C Jr, Filho RF, Traina AJ, Vieira MR, Faloutsos C (2007) The omnifamily of allpurpose access methods: a simple and effective way to make similarity search more efficient. VLDB J 16(4):483–505CrossRefGoogle Scholar
 24.Zhang D (2008) Neustore: a simple java package for the construction of diskbased, paginated, and buffered indices. http://www.ccs.neu.edu/home/donghui/research/neustore/