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, ...)An object to explain
Other parameters possibly used by generic
The first argument, invisibly.
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.
# \donttest{
lahman_s <- dbplyr::lahman_sqlite()
#> 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: 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
batting <- tbl(lahman_s, "Batting")
batting %>% show_query()
#> <SQL>
#> SELECT *
#> FROM `Batting`
batting %>% explain()
#> <SQL>
#> SELECT *
#> FROM `Batting`
#>
#> <PLAN>
#> id parent notused detail
#> 1 2 0 0 SCAN Batting
# The batting database has indices on all ID variables:
# SQLite automatically picks the most restrictive index
batting %>% filter(lgID == "NL" & yearID == 2000L) %>% explain()
#> <SQL>
#> SELECT *
#> FROM `Batting`
#> WHERE (`lgID` = 'NL' AND `yearID` = 2000)
#>
#> <PLAN>
#> id parent notused detail
#> 1 3 0 0 SEARCH Batting USING INDEX Batting_yearID (yearID=?)
# OR's will use multiple indexes
batting %>% filter(lgID == "NL" | yearID == 2000) %>% explain()
#> <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 Batting USING INDEX Batting_lgID (lgID=?)
#> 4 16 4 0 INDEX 2
#> 5 22 16 0 SEARCH Batting USING INDEX Batting_yearID (yearID=?)
# Joins will use indexes in both tables
teams <- tbl(lahman_s, "Teams")
batting %>% left_join(teams, c("yearID", "teamID")) %>% explain()
#> <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 detail
#> 1 4 0 0 SCAN LHS
#> 2 6 0 0 SEARCH RHS USING INDEX Teams_yearID (yearID=?)
# }