Explain details of a tbl

This is a generic function which gives more details about an object than print(), and is more focused on human readable output than str().

explain(x, ...)

show_query(x, ...)

Arguments

x	An object to explain
...	Other parameters possibly used by generic

Value

The first argument, invisibly.

Databases

Explaining a tbl_sql will run the SQL EXPLAIN command which will describe the query plan. This requires a little bit of knowledge about how EXPLAIN works for your database, but is very useful for diagnosing performance problems.

Examples

# \donttest{
if (require("dbplyr")) {

lahman_s <- lahman_sqlite()
batting <- tbl(lahman_s, "Batting")
batting %>% show_query()
batting %>% explain()

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

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

# Joins will use indexes in both tables
teams <- tbl(lahman_s, "Teams")
batting %>% left_join(teams, c("yearID", "teamID")) %>% explain()
}
#> Creating table: AllstarFull
#> Creating table: Appearances
#> Creating table: AwardsManagers
#> Creating table: AwardsPlayers
#> Creating table: AwardsShareManagers
#> Creating table: AwardsSharePlayers
#> Creating table: Batting
#> Creating table: BattingPost
#> Creating table: CollegePlaying
#> Creating table: Fielding
#> Creating table: FieldingOF
#> Creating table: FieldingOFsplit
#> Creating table: FieldingPost
#> Creating table: HallOfFame
#> Creating table: HomeGames
#> Creating table: LahmanData
#> Creating table: Managers
#> Creating table: ManagersHalf
#> Creating table: Master
#> Creating table: Parks
#> Creating table: People
#> Creating table: Pitching
#> Creating table: PitchingPost
#> Creating table: Salaries
#> Creating table: Schools
#> Creating table: SeriesPost
#> Creating table: Teams
#> Creating table: TeamsFranchises
#> Creating table: TeamsHalf
#> <SQL>
#> SELECT *
#> FROM `Batting`
#> <SQL>
#> SELECT *
#> FROM `Batting`
#> 
#> <PLAN>
#>   id parent notused             detail
#> 1  2      0       0 SCAN TABLE Batting
#> <SQL>
#> SELECT *
#> FROM `Batting`
#> WHERE (`lgID` = 'NL' AND `yearID` = 2000)
#> 
#> <PLAN>
#>   id parent notused                                                     detail
#> 1  3      0       0 SEARCH TABLE Batting USING INDEX Batting_yearID (yearID=?)
#> <SQL>
#> SELECT *
#> FROM `Batting`
#> WHERE (`lgID` = 'NL' OR `yearID` = 2000.0)
#> 
#> <PLAN>
#>   id parent notused                                                     detail
#> 1  4      0       0                                             MULTI-INDEX OR
#> 2  5      4       0                                                    INDEX 1
#> 3 11      5       0     SEARCH TABLE Batting USING INDEX Batting_lgID (lgID=?)
#> 4 16      4       0                                                    INDEX 2
#> 5 22     16       0 SEARCH TABLE Batting USING INDEX Batting_yearID (yearID=?)
#> <SQL>
#> SELECT `playerID`, `LHS`.`yearID` AS `yearID`, `stint`, `LHS`.`teamID` AS `teamID`, `LHS`.`lgID` AS `lgID.x`, `LHS`.`G` AS `G.x`, `LHS`.`AB` AS `AB.x`, `LHS`.`R` AS `R.x`, `LHS`.`H` AS `H.x`, `LHS`.`X2B` AS `X2B.x`, `LHS`.`X3B` AS `X3B.x`, `LHS`.`HR` AS `HR.x`, `RBI`, `LHS`.`SB` AS `SB.x`, `LHS`.`CS` AS `CS.x`, `LHS`.`BB` AS `BB.x`, `LHS`.`SO` AS `SO.x`, `IBB`, `LHS`.`HBP` AS `HBP.x`, `SH`, `LHS`.`SF` AS `SF.x`, `GIDP`, `RHS`.`lgID` AS `lgID.y`, `franchID`, `divID`, `Rank`, `RHS`.`G` AS `G.y`, `Ghome`, `W`, `L`, `DivWin`, `WCWin`, `LgWin`, `WSWin`, `RHS`.`R` AS `R.y`, `RHS`.`AB` AS `AB.y`, `RHS`.`H` AS `H.y`, `RHS`.`X2B` AS `X2B.y`, `RHS`.`X3B` AS `X3B.y`, `RHS`.`HR` AS `HR.y`, `RHS`.`BB` AS `BB.y`, `RHS`.`SO` AS `SO.y`, `RHS`.`SB` AS `SB.y`, `RHS`.`CS` AS `CS.y`, `RHS`.`HBP` AS `HBP.y`, `RHS`.`SF` AS `SF.y`, `RA`, `ER`, `ERA`, `CG`, `SHO`, `SV`, `IPouts`, `HA`, `HRA`, `BBA`, `SOA`, `E`, `DP`, `FP`, `name`, `park`, `attendance`, `BPF`, `PPF`, `teamIDBR`, `teamIDlahman45`, `teamIDretro`
#> FROM `Batting` AS `LHS`
#> LEFT JOIN `Teams` AS `RHS`
#> ON (`LHS`.`yearID` = `RHS`.`yearID` AND `LHS`.`teamID` = `RHS`.`teamID`)
#> 
#> 
#> <PLAN>
#>   id parent notused
#> 1  4      0       0
#> 2  6      0       0
#>                                                          detail
#> 1                                     SCAN TABLE Batting AS LHS
#> 2 SEARCH TABLE Teams AS RHS USING INDEX Teams_yearID (yearID=?)
# }