dplyr (version 0.1)

explain_sql: Show sql and query plans.

Description

Any queries run inside this function will automatically be explained: displaying information about which indexes are used to optimise the query. This requires a little bit of knowledge about how EXPLAIN works for your database, but is very useful for diagnosing performance problems.

Usage

explain_sql(code)
show_sql(code)
explain_tbl(tbl)

Arguments

code

code to run. All sql queries executed during the running of the code will be shown and explained.

tbl

an sql based table to explain.

Examples

if (require("RSQLite") && has_lahman("sqlite")) {

batting <- tbl(lahman_sqlite(), "Batting")

# Note that you have to do something that actually triggers a query
# inside the explain function
explain_sql(nrow(batting))
explain_sql(nrow(batting))

# nrow requires two queries the first time because it's the same as dim(x)[1]
# but the results are cached

show_sql(head(batting))
explain_sql(head(batting))

# If you just want to understand the sql for a tbl, use explain_tbl
explain_tbl(batting)

# The batting database has indices on all ID variables:
# SQLite automatically picks the most restrictive index
explain_tbl(filter(batting, lgID == "NL" & yearID == 2000))

# OR's will use multiple indexes
explain_tbl(filter(batting, lgID == "NL" | yearID == 2000))
}

Run the code above in your browser using DataCamp Workspace