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

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: FieldingPost
#> Creating table: HallOfFame
#> 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> #> selectid order from detail #> 1 0 0 0 SCAN TABLE Batting #> <SQL> #> SELECT * #> FROM `Batting` #> WHERE (`lgID` = 'NL' AND `yearID` = 2000) #> #> <PLAN> #> selectid order from #> 1 0 0 0 #> detail #> 1 SEARCH TABLE Batting USING INDEX Batting_yearID (yearID=?) #> <SQL> #> SELECT * #> FROM `Batting` #> WHERE (`lgID` = 'NL' OR `yearID` = 2000.0) #> #> <PLAN> #> selectid order from #> 1 0 0 0 #> 2 0 0 0 #> detail #> 1 SEARCH TABLE Batting USING INDEX Batting_lgID (lgID=?) #> 2 SEARCH TABLE Batting USING INDEX Batting_yearID (yearID=?) #> <SQL> #> SELECT `LHS`.`playerID` AS `playerID`, `LHS`.`yearID` AS `yearID`, `LHS`.`stint` AS `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`, `LHS`.`RBI` AS `RBI`, `LHS`.`SB` AS `SB.x`, `LHS`.`CS` AS `CS.x`, `LHS`.`BB` AS `BB.x`, `LHS`.`SO` AS `SO.x`, `LHS`.`IBB` AS `IBB`, `LHS`.`HBP` AS `HBP.x`, `LHS`.`SH` AS `SH`, `LHS`.`SF` AS `SF.x`, `LHS`.`GIDP` AS `GIDP`, `RHS`.`lgID` AS `lgID.y`, `RHS`.`franchID` AS `franchID`, `RHS`.`divID` AS `divID`, `RHS`.`Rank` AS `Rank`, `RHS`.`G` AS `G.y`, `RHS`.`Ghome` AS `Ghome`, `RHS`.`W` AS `W`, `RHS`.`L` AS `L`, `RHS`.`DivWin` AS `DivWin`, `RHS`.`WCWin` AS `WCWin`, `RHS`.`LgWin` AS `LgWin`, `RHS`.`WSWin` AS `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`, `RHS`.`RA` AS `RA`, `RHS`.`ER` AS `ER`, `RHS`.`ERA` AS `ERA`, `RHS`.`CG` AS `CG`, `RHS`.`SHO` AS `SHO`, `RHS`.`SV` AS `SV`, `RHS`.`IPouts` AS `IPouts`, `RHS`.`HA` AS `HA`, `RHS`.`HRA` AS `HRA`, `RHS`.`BBA` AS `BBA`, `RHS`.`SOA` AS `SOA`, `RHS`.`E` AS `E`, `RHS`.`DP` AS `DP`, `RHS`.`FP` AS `FP`, `RHS`.`name` AS `name`, `RHS`.`park` AS `park`, `RHS`.`attendance` AS `attendance`, `RHS`.`BPF` AS `BPF`, `RHS`.`PPF` AS `PPF`, `RHS`.`teamIDBR` AS `teamIDBR`, `RHS`.`teamIDlahman45` AS `teamIDlahman45`, `RHS`.`teamIDretro` AS `teamIDretro` #> FROM `Batting` AS `LHS` #> LEFT JOIN `Teams` AS `RHS` #> ON (`LHS`.`yearID` = `RHS`.`yearID` AND `LHS`.`teamID` = `RHS`.`teamID`) #> #> #> <PLAN> #> selectid order from #> 1 0 0 0 #> 2 0 1 1 #> detail #> 1 SCAN TABLE Batting AS LHS #> 2 SEARCH TABLE Teams AS RHS USING INDEX Teams_yearID (yearID=?)