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() }
#> <SQL> #> SELECT * #> FROM `Batting`
#> <SQL> #> SELECT * #> FROM `Batting`
#>
#> <PLAN> #> addr opcode p1 p2 p3 p4 p5 comment #> 1 0 Init 0 28 0 00 NA #> 2 1 OpenRead 0 2984 0 22 00 NA #> 3 2 Rewind 0 27 0 00 NA #> 4 3 Column 0 0 1 00 NA #> 5 4 Column 0 1 2 00 NA #> 6 5 Column 0 2 3 00 NA #> 7 6 Column 0 3 4 00 NA #> 8 7 Column 0 4 5 00 NA #> 9 8 Column 0 5 6 00 NA #> 10 9 Column 0 6 7 00 NA #> 11 10 Column 0 7 8 00 NA #> 12 11 Column 0 8 9 00 NA #> [ reached getOption("max.print") -- omitted 18 rows ]
#> <SQL> #> SELECT * #> FROM `Batting` #> WHERE (`lgID` = 'NL' AND `yearID` = 2000)
#>
#> <PLAN> #> addr opcode p1 p2 p3 p4 p5 comment #> 1 0 Init 0 34 0 00 NA #> 2 1 OpenRead 0 2984 0 22 00 NA #> 3 2 OpenRead 1 4745 0 k(2,,) 02 NA #> 4 3 Integer 2000 1 0 00 NA #> 5 4 SeekGE 1 33 1 1 00 NA #> 6 5 IdxGT 1 33 1 1 00 NA #> 7 6 DeferredSeek 1 0 0 00 NA #> 8 7 Column 0 4 2 00 NA #> 9 8 Ne 3 32 2 (BINARY) 52 NA #> 10 9 Column 0 0 4 00 NA #> 11 10 Column 1 0 5 00 NA #> 12 11 Column 0 2 6 00 NA #> [ reached getOption("max.print") -- omitted 25 rows ]
#> <SQL> #> SELECT * #> FROM `Batting` #> WHERE (`lgID` = 'NL' OR `yearID` = 2000.0)
#>
#> <PLAN> #> addr opcode p1 p2 p3 p4 #> 1 0 Init 0 49 0 #> 2 1 OpenRead 0 2984 0 22 #> 3 2 Null 0 2 0 #> 4 3 Integer 23 1 0 #> 5 4 ReopenIdx 1 5304 0 k(2,,) #> 6 5 String8 0 4 0 NL #> 7 6 SeekGE 1 13 4 1 #> 8 7 IdxGT 1 13 4 1 #> 9 8 DeferredSeek 1 0 0 [0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0] #> 10 9 IdxRowid 1 3 0 #> 11 10 RowSetTest 2 12 3 0 #> 12 11 Gosub 1 24 0 #> p5 comment #> 1 00 NA #> 2 00 NA #> 3 00 NA #> 4 00 NA #> 5 02 NA #> 6 00 NA #> 7 00 NA #> 8 00 NA #> 9 00 NA #> 10 00 NA #> 11 00 NA #> 12 00 NA #> [ reached getOption("max.print") -- omitted 39 rows ]
#> <SQL> #> SELECT `TBL_LEFT`.`playerID` AS `playerID`, `TBL_LEFT`.`yearID` AS `yearID`, `TBL_LEFT`.`stint` AS `stint`, `TBL_LEFT`.`teamID` AS `teamID`, `TBL_LEFT`.`lgID` AS `lgID.x`, `TBL_LEFT`.`G` AS `G.x`, `TBL_LEFT`.`AB` AS `AB.x`, `TBL_LEFT`.`R` AS `R.x`, `TBL_LEFT`.`H` AS `H.x`, `TBL_LEFT`.`X2B` AS `X2B.x`, `TBL_LEFT`.`X3B` AS `X3B.x`, `TBL_LEFT`.`HR` AS `HR.x`, `TBL_LEFT`.`RBI` AS `RBI`, `TBL_LEFT`.`SB` AS `SB.x`, `TBL_LEFT`.`CS` AS `CS.x`, `TBL_LEFT`.`BB` AS `BB.x`, `TBL_LEFT`.`SO` AS `SO.x`, `TBL_LEFT`.`IBB` AS `IBB`, `TBL_LEFT`.`HBP` AS `HBP.x`, `TBL_LEFT`.`SH` AS `SH`, `TBL_LEFT`.`SF` AS `SF.x`, `TBL_LEFT`.`GIDP` AS `GIDP`, `TBL_RIGHT`.`lgID` AS `lgID.y`, `TBL_RIGHT`.`franchID` AS `franchID`, `TBL_RIGHT`.`divID` AS `divID`, `TBL_RIGHT`.`Rank` AS `Rank`, `TBL_RIGHT`.`G` AS `G.y`, `TBL_RIGHT`.`Ghome` AS `Ghome`, `TBL_RIGHT`.`W` AS `W`, `TBL_RIGHT`.`L` AS `L`, `TBL_RIGHT`.`DivWin` AS `DivWin`, `TBL_RIGHT`.`WCWin` AS `WCWin`, `TBL_RIGHT`.`LgWin` AS `LgWin`, `TBL_RIGHT`.`WSWin` AS `WSWin`, `TBL_RIGHT`.`R` AS `R.y`, `TBL_RIGHT`.`AB` AS `AB.y`, `TBL_RIGHT`.`H` AS `H.y`, `TBL_RIGHT`.`X2B` AS `X2B.y`, `TBL_RIGHT`.`X3B` AS `X3B.y`, `TBL_RIGHT`.`HR` AS `HR.y`, `TBL_RIGHT`.`BB` AS `BB.y`, `TBL_RIGHT`.`SO` AS `SO.y`, `TBL_RIGHT`.`SB` AS `SB.y`, `TBL_RIGHT`.`CS` AS `CS.y`, `TBL_RIGHT`.`HBP` AS `HBP.y`, `TBL_RIGHT`.`SF` AS `SF.y`, `TBL_RIGHT`.`RA` AS `RA`, `TBL_RIGHT`.`ER` AS `ER`, `TBL_RIGHT`.`ERA` AS `ERA`, `TBL_RIGHT`.`CG` AS `CG`, `TBL_RIGHT`.`SHO` AS `SHO`, `TBL_RIGHT`.`SV` AS `SV`, `TBL_RIGHT`.`IPouts` AS `IPouts`, `TBL_RIGHT`.`HA` AS `HA`, `TBL_RIGHT`.`HRA` AS `HRA`, `TBL_RIGHT`.`BBA` AS `BBA`, `TBL_RIGHT`.`SOA` AS `SOA`, `TBL_RIGHT`.`E` AS `E`, `TBL_RIGHT`.`DP` AS `DP`, `TBL_RIGHT`.`FP` AS `FP`, `TBL_RIGHT`.`name` AS `name`, `TBL_RIGHT`.`park` AS `park`, `TBL_RIGHT`.`attendance` AS `attendance`, `TBL_RIGHT`.`BPF` AS `BPF`, `TBL_RIGHT`.`PPF` AS `PPF`, `TBL_RIGHT`.`teamIDBR` AS `teamIDBR`, `TBL_RIGHT`.`teamIDlahman45` AS `teamIDlahman45`, `TBL_RIGHT`.`teamIDretro` AS `teamIDretro` #> FROM `Batting` AS `TBL_LEFT` #> LEFT JOIN `Teams` AS `TBL_RIGHT` #> ON (`TBL_LEFT`.`yearID` = `TBL_RIGHT`.`yearID` AND `TBL_LEFT`.`teamID` = `TBL_RIGHT`.`teamID`)
#>
#> <PLAN> #> addr opcode p1 p2 p3 p4 p5 comment #> 1 0 Init 0 93 0 00 NA #> 2 1 OpenRead 0 2984 0 22 00 NA #> 3 2 OpenRead 1 13917 0 48 00 NA #> 4 3 OpenRead 2 14055 0 k(2,,) 02 NA #> 5 4 Rewind 0 92 0 00 NA #> 6 5 Integer 0 1 0 00 NA #> 7 6 Column 0 3 2 00 NA #> 8 7 IsNull 2 87 0 00 NA #> 9 8 SeekGE 2 87 2 1 00 NA #> 10 9 IdxGT 2 87 2 1 00 NA #> 11 10 DeferredSeek 2 0 1 00 NA #> 12 11 Column 0 1 3 00 NA #> [ reached getOption("max.print") -- omitted 83 rows ]