This section provides an analytical model of the operators in order to better grasp the behavior of different access path alternatives, and to answer the critical questions of which policy and mode Smooth Scan should employ and when. Smooth Scan trades CPU for I/O cost reduction, thus the proposed model includes the cost of the operators both in terms of the number of disk I/O accesses, and the CPU cost. Since one I/O operation corresponds to an order of million CPU cycles [99], it is expected that the overall cost is dominated by the I/O component; nonetheless, CPU costs are also considered for completeness. Moreover, we make a distinction between the cost of a sequential and random access, since the nature of the accesses drives the overall query performance.
#TP = PS TS (8.3) #P = #T #TP (8.4) f anout = PS 1.2× KS (8.5) #l eaves = #T f anout (8.6)
hei g ht = logf anout(#l eaves) + 1 (8.7)
c ar d = sel × #T (8.8)
#l eavesr es =
c ar d
f anout (8.9)
OPcost = OPi o_cost+OPc pu_cost (8.10)
Table8.1contains the parameters of the cost model. Formulas calculating the cost of the non-clustered index scan and the full scan are presented for comparison purposes (similar cost model formulas are found in database text books [203]). Indexes are implemented as B+-trees, with k as the tree fanout. Equations (8.3) to (8.9) are base formulas used for all access path operators. The final cost of every operator is a sum of its I/O and CPU costs. We simplify the calculations by assuming every page is filled completely(100%) and that heap pages and index pages are of the same size (PS). Lastly, we assume that TSalready includes a
8.5. Modeling Smooth Scan
Table 8.1: Smooth Scan: Cost model parameters
Parameter Description
TS Tuple size (bytes).
#T Number of tuples in the relation.
PS Page size (bytes).
#TP Number of tuples per page.
#P Number of pages the relation occupies.
KS Size of the indexing key (bytes).
sel Selectivity of the query predicate(s) (%).
c ar d Number of result tuples.
c ar dmX Number of tuples obtained with Mode X.
m0check 0 or 1. Was a traditional index employed first?
r andcost Cost of a random I/O access (per page).
seqcost Cost of a sequential I/O access (per page).
c pucost Cost of a CPU operation (per tuple).
#Pr es Number of pages containing result tuples.
#Pr es_r eg i on Number of pages with result in current region.
#Pseen Number of pages seen so far.
#Pseen_r eg i on Number of pages in the current region.
#r andi o Number of random accesses.
#seqi o Number of sequential accesses. Derived values
f anout B+-tree fanout.
#l eaves Number of leaf pages in B+-tree. #l eavesr es Number of leaf pages with pointers to results.
hei g ht Height of B+-tree.
OPi o_cost Cost of an operator in terms of I/O.
OPc pu_cost Cost of an operator in terms of CPU.
C R Competitive ratio.
the B+-tree by adding 20% of space per key for a pointer to a lower level. For Eq. (8.6) and (8.9), we assume that every tuple stored in a heap page has a pointer to it in a leaf page of the index.
Full Table Scan. The cost of full scan does not depend on the number of tuples that qualify
for the given predicate(s). Thus, regardless of the selectivity of the operator its cost remains constant. As shown in Eq. (8.11), the I/O cost is the cost of fetching all pages of the relation sequentially (as we expect each table to be stored sequentially on disk). Once full scan fetches a page, it performs a tuple comparison for all tuples from the page to find the ones that qualify. Assuming that each comparison invokes one CPU operation, the overall CPU cost is given by Eq. (8.12).
Chapter 8. Predictable Data Analytics
F Si o_cost = #P × seqcost (8.11)
F Sc pu_cost = #T × cpucost (8.12)
Index Scan. To fetch the tuples, the (non-clustered) index scan traverses the tree once to find
the first tuple that qualifies (hei g ht in Eq. (8.13)). For the remaining tuples, it continues traversing the leaf pages from the index (#l eavesr es× seqcost) and uses tuple ID found to
access the heap pages, potentially triggering a random I/O operation per look-up (c ar d in Eq. (8.13)). While traversing the tree, within every index page, the index scan performs a binary search in order to find a pointer of interest to the next level (hei g ht× log2f anoutin Eq. (8.14)). For each tuple obtained by following the pointers from the leaf it then performs a tuple comparison to see whether the tuple qualifies (the second part of Eq. (8.14)).
I Si o_cost =
hei g ht+ card× r andcost
+ #leavesr es× seqcost (8.13)
I Sc pu_cost =
hei g ht× log2f anout+ card× cpucost (8.14)
Smooth Scan. Having defined the cost of the basic access path operators, we move on to
defining the cost of Smooth Scan. We calculate the cost of Smooth Scan for each mode separately. Overall result cardinality is split between the modes (Eq. (8.15)). Like the index scan, the cost of the smooth scan access is driven by selectivity. Assuming uniform distribution of the result tuples (the worst case for Smooth Scan), the number of pages containing the result is calculated in Eq. (8.16).
c ar d = cardm0+ cardm1+ cardm2 (8.15)
#Pr es = min(card,#P) (8.16)
Mode 0: Index Scan. If the traditional index is employed prior to morphing, the I/O cost to
obtain first c ar dm0tuples is identical to the cost of the index scan for the same number of
8.5. Modeling Smooth Scan
operator in Mode 0 (the multiplier 2 in Eq. (8.17)), to add tuple IDs to the Tuple ID cache.
SSc pu_cost _m0 = (hei ght × log2
f anout
+ cardm0× 2) × cpucost (8.17)
Mode 1: Entire Page Probe. The number of tuples for which Mode 1 is going to be employed
is calculated in Eq. (8.18) (again the worst case). Every page is assumed to be fetched with a random access (Eq. (8.19)). Once Smooth Scan obtains a page, it performs a tuple comparison checking all tuples from the page (the first part of Eq. (8.20)). Before fetching the page Smooth Scan checks whether the page is already processed, and upon its processing Smooth Scan adds it to the Page Cache (the second part of Eq. (8.20)). Finally, if Smooth Scan started with the traditional index, for each tuple Smooth Scan has to perform a check whether the tuple has already been produced in Mode 0 (the third part of Eq. (8.20)). In case Smooth Scan needs to support an interesting order, the Result Cache will be used as a replacement for the Tuple ID cache functionality. In that case Smooth Scan only marks the tuple ID as a key in the cache, without copying the actual tuple as a hash value; the probe match without the actual result thus signifies that the tuple has already been produced. Thus, the CPU cost remains (roughly) the same in both cases.
#Pm1 = min(cardm1, #P ) (8.18)
SSi o_cost _m1 = #Pm1× r andcost (8.19)
SSc pu_cost _m1 = (#Pm1× #TP+ #Pm1× 2
+ #Pm1× #TP× m0check)× cpucost (8.20) Mode 2: Flattening Access. We calculate the maximum number of pages to fetch with Mode 2
in Eq. (8.21). Notice that pages processed in Mode 1 are skipped in Mode 2. The nature of the morphing expansion in Mode 2 of Smooth Scan is described with Eq. (8.22). The solution of the recurrence equation is shown in Eq. (8.23). In this case, n is the number of times Smooth Scan expands the morphing region size (i.e., the number of times Smooth Scan performs a random I/O access) and f (n) translates to the number of pages to fetch with Mode 2 (#Pm2).
The minimum number of random accesses (jumps) to fetch all pages containing the results is given by Eq. (8.25). This number is the best case scenario, when the access pattern is such that all pages are fetched with the flattening pattern without repeated accesses. The worst case scenario number of random accesses is shown in Eq. (8.26). When selectivity is low, the number of random I/O accesses is at worst equal to the number of pages that contain the results. Nonetheless, there is an upper bound to it, equal to the logarithm of the number of pages in total, since after this number all pages will be accessed.
Since both Eq. (8.25) and Eq. (8.26) converge to the same value equal to l og2(#P+ 1), we use
Chapter 8. Predictable Data Analytics
Eq. (8.27), and is equal to the cost of the number of jumps with a random access pattern, plus the cost to fetch the remaining number of pages with a sequential pattern. The CPU cost per page in Mode 2 is identical to the cost per page in Mode 1 (Eq. (8.28)).
#Pm2 = min(cardm2, #P− #Pm1) (8.21) f (i+ 1) = 2 × f (i),i = 0..n (8.22) f (0) = 0, f (n) = 2n, n>= 0 (8.23) #Pm2 = #r andi o(m2_mi n) i=0 2i (8.24)
#r andi o(m2_mi n) = log2(#Pm2+ 1) (8.25)
#r andi o(m2_max) = min
#Pm2, log2(#P+ 1)
(8.26)
SSi o_cost _m2 = #r andi o(m2)× r andcost
+ (#Pm2− #r andi o(m2))× seqcost (8.27)
SSc pu_cost _m2 = (#Pm2× #TP+ #Pm2× 2
+ #Pm2× #TP∗ m0check)× cpucost (8.28)
Finally, the overall cost is the sum of the operator CPU and I/O costs for all employed modes(Eg. (8.29)).
SSi o_cost = SSi o_cost _m0+ SSi o_cost _m1+ SSi o_cost _m2
SSc pu_cost = SSc pu_cost _m0+ SSc pu_cost _m1+ SSc pu_cost _m2
SScost = SSi o_cost+ SSc pu_cost (8.29)
The cost model enables the estimation of the cost of Smooth Scan policies, in order to decide when is the time to trigger a mode change. For instance, for the SLA driven strategy the overall operator cost is defined by an SLA. Based on that cost, Eq. (8.29) computes the cardinality, i.e., the triggering point for Smooth Scan to start morphing calculated for the worst case scenario (selectivity 100%).