SAMPLING-BASED RANDOMIZATION TECHNIQUES FOR APPROXIMATE QUERY

165 Pages·2007·0.97 MB·English

SAMPLING-BASED RANDOMIZATION TECHNIQUES FOR APPROXIMATE
QUERY PROCESSING
By
SHANTANU JOSHI
A DISSERTATION PRESENTED TO THE GRADUATE SCHOOL
OF THE UNIVERSITY OF FLORIDA IN PARTIAL FULFILLMENT
OF THE REQUIREMENTS FOR THE DEGREE OF
DOCTOR OF PHILOSOPHY
UNIVERSITY OF FLORIDA
2007
1
(cid:176)c 2007 SHANTANU JOSHI
2
To my parents, Dr Sharad Joshi and Dr Hemangi Joshi
3
ACKNOWLEDGMENTS
Firstly, my sincerest gratitude goes to my advisor, Professor Chris Jermaine for his
invaluable guidance and support throughout my PhD research work. During the initial
several months of my graduate work, Chris was extremely patient and always led me
towards the right direction whenever I would waver. His acute insight into the research
problems we worked on set an excellent example and provided me immense motivation to
work on them. He has always emphasized the importance of high-quality technical writing
and has spent several painstaking hours reading and correcting my technical manuscripts.
He has been the best mentor I could have hoped for and I shall always remain indebted to
him for shaping my career and more importantly, my thinking.
I am also very thankful to Professor Alin Dobra for his guidance during my graduate
study. His enthusiasm and constant willingness to help has always amazed me.
Thanks are also due to Professor Joachim Hammer for his support during the very
early days of my graduate study. I take this opportunity to thank Professors Tamer
Kahveci and Gary Koehler for taking the time to serve on my committee and for their
helpful suggestions.
It was a pleasure working with Subi Arumugam and Abhijit Pol on various collaborative
research projects. Several interesting technical discussions with Mingxi Wu, Fei Xu, Florin
Rusu, Laukik Chitnis and Seema Degwekar provided a stimulating work environment in
the Database Center.
This work would not have been possible without the constant encouragement and
support of my family. My parents, Dr Sharad Joshi and Dr Hemangi Joshi always
encouraged me to focus on my goals and pursue them against all odds. My brother,
Dr Abhijit Joshi has always placed trust in my abilities and has been an ideal example to
follow since my childhood. My loving sister-in-law, Dr Hetal Joshi has been supportive
since the time I decided to pursue computer science.
4
TABLE OF CONTENTS
page
ACKNOWLEDGMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
LIST OF TABLES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
LIST OF FIGURES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
ABSTRACT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
CHAPTER
1 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
1.1 Approximate Query Processing (AQP) - A Different Paradigm . . . . . . . 13
1.2 Building an AQP System Afresh . . . . . . . . . . . . . . . . . . . . . . . . 14
1.2.1 Sampling Vs Precomputed Synopses . . . . . . . . . . . . . . . . . . 15
1.2.2 Architectural Changes . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.3 Contributions in This Thesis . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2 RELATED WORK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.1 Sampling-based Estimation . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2 Estimation Using Non-sampling Precomputed Synopses . . . . . . . . . . . 28
2.3 Analytic Query Processing Using Non-standard Data Models . . . . . . . . 30
3 MATERIALIZED SAMPLE VIEWS FOR DATABASE APPROXIMATION . . 33
3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
3.2 Existing Sampling Techniques . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.2.1 Randomly Permuted Files . . . . . . . . . . . . . . . . . . . . . . . 35
3.2.2 Sampling from Indices . . . . . . . . . . . . . . . . . . . . . . . . . 36
3.2.3 Block-based Random Sampling . . . . . . . . . . . . . . . . . . . . . 37
3.3 Overview of Our Approach . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.3.1 ACE Tree Leaf Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.3.2 ACE Tree Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
3.3.3 Example Query Execution in ACE Tree . . . . . . . . . . . . . . . . 40
3.3.4 Choice of Binary Versus k-Ary Tree . . . . . . . . . . . . . . . . . . 42
3.4 Properties of the ACE Tree . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.4.1 Combinability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.4.2 Appendability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.4.3 Exponentiality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.5 Construction of the ACE Tree . . . . . . . . . . . . . . . . . . . . . . . . . 45
3.5.1 Design Goals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
3.5.2 Construction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
3.5.3 Construction Phase 1 . . . . . . . . . . . . . . . . . . . . . . . . . . 46
5
3.5.4 Construction Phase 2 . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.5.5 Combinability/Appendability Revisited . . . . . . . . . . . . . . . . 51
3.5.6 Page Alignment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
3.6 Query Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
3.6.1 Goals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
3.6.2 Algorithm Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
3.6.3 Data Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
3.6.4 Actual Algorithm . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
3.6.5 Algorithm Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.7 Multi-Dimensional ACE Trees . . . . . . . . . . . . . . . . . . . . . . . . . 59
3.8 Benchmarking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
3.8.1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
3.8.2 Discussion of Experimental Results . . . . . . . . . . . . . . . . . . 66
3.9 Conclusion and Discussion . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
4 SAMPLING-BASED ESTIMATORS FOR SUBSET-BASED QUERIES . . . . 73
4.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
4.2 The Concurrent Estimator . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
4.3 Unbiased Estimator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
4.3.1 High-Level Description . . . . . . . . . . . . . . . . . . . . . . . . . 80
4.3.2 The Unbiased Estimator In Depth . . . . . . . . . . . . . . . . . . . 81
4.3.3 Why Is the Estimator Unbiased? . . . . . . . . . . . . . . . . . . . . 85
4.3.4 Computing the Variance of the Estimator . . . . . . . . . . . . . . . 87
4.3.5 Is This Good? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
4.4 Developing a Biased Estimator . . . . . . . . . . . . . . . . . . . . . . . . 91
4.5 Details of Our Approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
4.5.1 Choice of Model and Model Parameters . . . . . . . . . . . . . . . . 92
4.5.2 Estimation of Model Parameters . . . . . . . . . . . . . . . . . . . . 95
4.5.3 Generating Populations From the Model . . . . . . . . . . . . . . . 100
4.5.4 Constructing the Estimator . . . . . . . . . . . . . . . . . . . . . . . 102
4.6 Experiments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.6.1 Experimental Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.6.1.1 Synthetic data sets . . . . . . . . . . . . . . . . . . . . . . 104
4.6.1.2 Real-life data sets . . . . . . . . . . . . . . . . . . . . . . . 106
4.6.2 Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
4.6.3 Discussion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
4.7 Related Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
4.8 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
5 SAMPLING-BASED ESTIMATION OF LOW SELECTIVITY QUERIES . . . 121
5.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
5.2 Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
5.2.1 Stratification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
5.2.2 “Optimal” Allocation and Why It’s Not . . . . . . . . . . . . . . . . 126
6
5.3 Overview of Our Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
5.4 Defining X . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Σ
5.4.1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
5.4.2 Defining X . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
cnt
5.4.3 Defining X . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Σ0
5.4.4 Combining The Two . . . . . . . . . . . . . . . . . . . . . . . . . . 135
5.4.5 Limiting the Number of Domain Values . . . . . . . . . . . . . . . . 137
5.5 Updating Priors Using The Pilot . . . . . . . . . . . . . . . . . . . . . . . 139
5.6 Putting It All Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
5.6.1 Minimizing the Variance . . . . . . . . . . . . . . . . . . . . . . . . 141
5.6.2 Computing the Final Sampling Allocation . . . . . . . . . . . . . . 142
5.7 Experiments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
5.7.1 Goals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
5.7.2 Experimental Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
5.7.3 Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
5.7.4 Discussion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
5.8 Related Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
5.9 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
6 CONCLUSION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
APPENDIX
EM ALGORITHM DERIVATION . . . . . . . . . . . . . . . . . . . . . . . . . 155
REFERENCES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
BIOGRAPHICAL SKETCH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
7
LIST OF TABLES
Table page
4-1 Observed standard error as a percentage of SUM (e.SAL) over all e ∈ EMP for
24 synthetically generated data sets. The table shows errors for three different
sampling fractions: 1%, 5% and 10% and for each of these fractions, it shows
the error for the three estimators: U - Unbiased estimator, C - Concurrent sampling
estimator and B - Model-based biased estimator. . . . . . . . . . . . . . . . . . 112
4-2 Observed standard error as a percentage of SUM (e.SAL) over all e ∈ EMP for
24 synthetically generated data sets. The table shows errors for three different
sampling fractions: 1%, 5% and 10% and for each of these fractions, it shows
the error for the three estimators: U - Unbiased estimator, C - Concurrent sampling
estimator and B - Model-based biased estimator. . . . . . . . . . . . . . . . . . 113
4-3 Observed standard error as a percentage of SUM (e.SAL) over all e ∈ EMP for
18 synthetically generated data sets. The table shows errors for three different
sampling fractions: 1%, 5% and 10% and for each of these fractions, it shows
the error for the three estimators: U - Unbiased estimator, C - Concurrent sampling
estimator and B - Model-based biased estimator. . . . . . . . . . . . . . . . . . 114
4-4 Observed standard error as a percentage of the total aggregate value of all records
in the database for 8 queries over 3 real-life data sets. The table shows errors
for three different sampling fractions: 1%, 5% and 10% and for each of these
fractions, it shows the error for the three estimators: U - Unbiased estimator,
C - Concurrent sampling estimator and B - Model-based biased estimator. . . . 115
5-1 Bandwidth (as a ratio of error bounds width to the true query answer) and Coverage
(for 1000 query runs) for a Simple Random Sampling estimator for the KDD
Cup data set. Results are shown for varying sample sizes and for three different
query selectivities - 0.01%, 0.1% and 1%. . . . . . . . . . . . . . . . . . . . . . . 146
5-2 Average running time of Neyman and Bayes-Neyman estimators over three real-world
datasets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
5-3 Bandwidth (as a ratio of error bounds width to the true query answer) and Coverage
(for 1000 query runs) for the Neyman estimator and the Bayes-Neyman estimator
for the three data sets. Results are shown for 20 strata and for varying number
of records in pilot sample per stratum (PS), and sample sizes(SS) for three different
query selectivities - 0.01%, 0.1% and 1%. . . . . . . . . . . . . . . . . . . . . . . 148
5-4 Bandwidth (as a ratio of error bounds width to the true query answer) and Coverage
(for 1000 query runs) for the Neyman estimator and the Bayes-Neyman estimator
for the three data sets. Results are shown for 200 strata with varying number
of records in pilot sample per stratum (PS), and sample sizes(SS) for three different
query selectivities - 0.01%, 0.1% and 1%. . . . . . . . . . . . . . . . . . . . . . . 149
8
LIST OF FIGURES
Figure page
1-1 Simplified architecture of a DBMS . . . . . . . . . . . . . . . . . . . . . . . . . 17
3-1 Structure of a leaf node of the ACE tree. . . . . . . . . . . . . . . . . . . . . . . 39
3-2 Structure of the ACE tree. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
3-3 Random samples from section 1 of L . . . . . . . . . . . . . . . . . . . . . . . . 41
3
3-4 Combining samples from L and L . . . . . . . . . . . . . . . . . . . . . . . . . 42
3 5
3-5 Combining two sections of leaf nodes of the ACE tree. . . . . . . . . . . . . . . 43
3-6 Appending two sections of leaf nodes of the ACE tree. . . . . . . . . . . . . . . 45
3-7 Choosing keys for internal nodes. . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3-8 Exponentiality property of ACE tree. . . . . . . . . . . . . . . . . . . . . . . . . 48
3-9 Phase 2 of tree construction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
3-10 Execution runs of query answering algorithm with (a) 1 contributing section,
(b) 6 contributing sections, (c) 7 contributing sections and (d) 16 contributing
sections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3-11 Sampling rate of an ACE tree vs. rate for a B+ tree and scan of a randomly
permuted file, with a one dimensional selection predicate accepting 0.25% of
the database records. The graph shows the percentage of database records retrieved
by all three sampling techniques versus time plotted as a percentage of the time
required to scan the relation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
3-12 Sampling rate of an ACE tree vs. rate for a B+ tree and scan of a randomly
permuted file, with a one dimensional selection predicate accepting 2.5% of the
database records. The graph shows the percentage of database records retrieved
by all three sampling techniques versus time plotted as a percentage of the time
required to scan the relation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
3-13 Sampling rate of an ACE tree vs. rate for a B+ tree and scan of a randomly
permuted file, with a one dimensional selection predicate accepting 25% of the
database records. The graph shows the percentage of database records retrieved
by all three sampling techniques versus time plotted as a percentage of the time
required to scan the relation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
3-14 Sampling rate of an ACE tree vs. rate for a B+ tree and scan of a randomly
permuted file, with a one dimensional selection predicate accepting 2.5% of the
database records. The graph is an extension of Figure 3-12 and shows results
till all three sampling techniques return all the records matching the query predicate. 63
9
3-15 Number of records needed to be buffered by the ACE Tree for queries with (a)
0.25% and (b) 2.5% selectivity. The graphs show the number of records buffered
as a fraction of the total database records versus time plotted as a percentage
of the time required to scan the relation. . . . . . . . . . . . . . . . . . . . . . . 64
3-16 Sampling rate of an ACE Tree vs. rate for an R-Tree and scan of a randomly
permuted file, with a spatial selection predicate accepting 0.25% of the database
tuples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
3-17 Sampling rate of an ACE tree vs. rate for an R-tree, and scan of a randomly
permuted file with a spatial selection predicate accepting 2.5% of the database
tuples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
3-18 Sampling rate of an ACE tree vs. rate for an R-tree, and scan of a randomly
permuted file with a spatial selection predicate accepting 25% of the database
tuples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
4-1 Sampling from a superpopulation . . . . . . . . . . . . . . . . . . . . . . . . . . 90
4-2 Six distributions used to generate for each e in EMP the number of records s in
SALE for which f (e,s) evaluates to true. . . . . . . . . . . . . . . . . . . . . . . 105
3
5-1 Beta distribution with parameters α = β = 0.5. . . . . . . . . . . . . . . . . . . 131
10