explain_sql

0th

Percentile

Show sql and query plans.

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)

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.
Aliases
  • explain
  • explain_sql
  • show_sql
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
explain(batting)

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

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

Documentation reproduced from package dplyr, version 0.1.1, License: MIT + file LICENSE

Community examples

Looks like there are no examples yet.