This vignette assumes that the reader is familiar with data.table’s
[i, j, by] syntax, and how to perform fast key based subsets. If you’re not familiar with these concepts, please read the “Introduction to data.table”, “Reference semantics” and “Keys and fast binary search based subset” vignettes first.
We will use the same
flights data as in the “Introduction to data.table” vignette.
In this vignette, we will
discuss secondary indices and provide rationale as to why we need them by citing cases where setting keys is not necessarily ideal,
perform fast subsetting, once again, but using the new
on argument, which computes secondary indices internally for the task (temporarily), and reuses if one already exists,
and finally look at auto indexing which goes a step further and creates secondary indices automatically, but does so on native R syntax for subsetting.
Secondary indices are similar to
keys in data.table, except for two major differences:
It doesn’t physically reorder the entire data.table in RAM. Instead, it only computes the order for the set of columns provided and stores that order vector in an additional attribute called
There can be more than one secondary index for a data.table (as we will see below).
originas a secondary index in the data.table
setindex(flights, origin) head(flights) # year month day dep_delay arr_delay carrier origin dest air_time distance hour # [ reached getOption("max.print") -- omitted 7 rows ] ## alternatively we can provide character vectors to the function 'setindexv()' # setindexv(flights, "origin") # useful to program with # 'index' attribute added names(attributes(flights)) #  "names" "row.names" "class" ".internal.selfref" #  "index"
setindexv() allows adding a secondary index to the data.table.
flights is not physically reordered in increasing order of
origin, as would have been the case with
Also note that the attribute
index has been added to
setindex(flights, NULL) would remove all secondary indices.
indices() returns all current secondary indices in the data.table. If none exists,
NULL is returned.
Note that by creating another index on the columns
origin, dest, we do not lose the first index created on the column
origin, i.e., we can have multiple secondary indices.
Consider the case where you would like to perform a fast key based subset on
origin column for the value “JFK”. We’d do this as:
computing the order vector for the column(s) provided, here,
reordering the entire data.table, by reference, based on the order vector computed.
Computing the order isn’t the time consuming part, since data.table uses true radix sorting on integer, character and numeric vectors. However reordering the data.table could be time consuming (depending on the number of rows and columns).
Unless our task involves repeated subsetting on the same column, fast key based subsetting could effectively be nullified by the time to reorder, depending on our data.table dimensions.
keyat the most
Now if we would like to repeat the same operation but on
dest column instead, for the value “LAX”, then we have to
And this reorders
dest, again. What we would really like is to be able to perform the fast subsetting by eliminating the reordering step.
And this is precisely what secondary indices allow for!
Since there can be multiple secondary indices, and creating an index is as simple as storing the order vector as an attribute, this allows us to even eliminate the time to recompute the order vector if an index already exists.
onargument allows for cleaner syntax and automatic creation and reuse of secondary indices
As we will see in the next section, the
on argument provides several advantages:
enables subsetting by computing secondary indices on the fly. This eliminates having to do
setindex() every time.
allows easy reuse of existing indices by just checking the attributes.
allows for a cleaner syntax by having the columns on which the subset is performed as part of the syntax. This makes the code easier to follow when looking at it at a later point.
on argument can also be used on keyed subsets as well. In fact, we encourage to provide the
on argument even when subsetting using keys for better readability.
onargument and secondary indices
This statement performs a fast binary search based subset as well, by computing the index on the fly. However, note that it doesn’t save the index as an attribute automatically. This may change in the future.
If we had already created a secondary index, using
on would reuse it instead of (re)computing it. We can see that by using
verbose = TRUE:
setindex(flights, origin) flights["JFK", on = "origin", verbose = TRUE][1:5] # i.V1 has same type (character) as x.origin. No coercion needed. # on= matches existing index, using index # Starting bmerge ... # forder.c received 1 rows and 1 columns # bmerge done in 0.000s elapsed (0.000s cpu) # Constructing irows for '!byjoin || nqbyjoin' ... 0.000s elapsed (0.000s cpu) # year month day dep_delay arr_delay carrier origin dest air_time distance hour # [ reached getOption("max.print") -- omitted 6 rows ]
For example, if we want to subset
"JFK", "LAX" combination, then:
on argument accepts a character vector of column names corresponding to the order provided to
Since the time to compute the secondary index is quite small, we don’t have to use
setindex(), unless, once again, the task involves repeated subsetting on the same column.
All the operations we will discuss below are no different to the ones we already saw in the Keys and fast binary search based subset vignette. Except we’ll be using the
on argument instead of setting keys.
arr_delaycolumn alone as a data.table corresponding to
origin = "LGA"and
dest = "TPA"
origin = "LGA"and
dest = "TPA".
We have seen this example already in the Reference semantics and Keys and fast binary search based subset vignette. Let’s take a look at all the
hours available in the
We see that there are totally
25 unique values in the data. Both 0 and 24 hours seem to be present. Let’s go ahead and replace 24 with 0, but this time using
on instead of setting keys.
Now, let’s check if
24 is replaced with
0 in the
hour, we had to
setkey()on it, which inevitably reorders the entire data.table. With
on, the order is preserved, and the operation is much faster! Looking at the code, the task we wanted to perform is also quite clear.
origin = "JFK". Order the result by
origin, destagain, if we did not use
onwhich internally builds secondary indices on the fly.
The other arguments including
mult work exactly the same way as we saw in the Keys and fast binary search based subset vignette. The default value for
mult is “all”. We can choose, instead only the “first” or “last” matching rows should be returned.
destmatches “BOS” and “DAY”
originmatches “LGA”, “JFK”, “EWR” and
We can choose if queries that do not match should return
NA or be skipped altogether using the
First we looked at how to fast subset using binary search using keys. Then we figured out that we could improve performance even further and have more cleaner syntax by using secondary indices.
That is what auto indexing does. At the moment, it is only implemented for binary operators
%in%. An index is automatically created and saved as an attribute. That is, unlike the
on argument which computes the index on the fly each time (unless one already exists), a secondary index is created here.
Let’s start by creating a data.table big enough to highlight the advantage.
When we use
%in% on a single column for the first time, a secondary index is created automatically, and it is used to perform the subset.
## have a look at all the attribute names names(attributes(dt)) #  "names" "row.names" "class" ".internal.selfref" ## run thefirst time (t1 <- system.time(ans <- dt[x == 989L])) # user system elapsed # 0.575 0.051 0.631 head(ans) # x y # <int> <num> # 1: 989 0.7757157 # [ reached getOption("max.print") -- omitted 5 rows ] ## secondary index is created names(attributes(dt)) #  "names" "row.names" "class" ".internal.selfref" #  "index" indices(dt) #  "x"
The time to subset the first time is the time to create the index + the time to subset. Since creating a secondary index involves only creating the order vector, this combined operation is faster than vector scans in many cases. But the real advantage comes in successive subsets. They are extremely fast.
Running the first time took 0.631 seconds where as the second time took 0.001 seconds.
Auto indexing can be disabled by setting the global argument
options(datatable.auto.index = FALSE).
Disabling auto indexing still allows to use indices created explicitly with
setindexv. You can disable indices fully by setting global argument
options(datatable.use.index = FALSE).
In recent version we extended auto indexing to expressions involving more than one column (combined with
& operator). In the future, we plan to extend binary search to work with more binary operators like
We will discuss fast subsets using keys and secondary indices to joins in the next vignette, “Joins and rolling joins”.