This is a generic function which gives more details about an object than print(), and is more focussed 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: 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> #> addr opcode p1 p2 p3 p4 p5 comment #> 1 0 Init 0 29 0 00 NA #> 2 1 OpenRead 0 12031 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 #> 13 12 Column 0 9 10 00 NA #> 14 13 Column 0 10 11 00 NA #> 15 14 Column 0 11 12 00 NA #> 16 15 Column 0 12 13 00 NA #> 17 16 Column 0 13 14 00 NA #> 18 17 Column 0 14 15 00 NA #> 19 18 Column 0 15 16 00 NA #> 20 19 Column 0 16 17 00 NA #> 21 20 Column 0 17 18 00 NA #> 22 21 Column 0 18 19 00 NA #> 23 22 Column 0 19 20 00 NA #> 24 23 Column 0 20 21 00 NA #> 25 24 Column 0 21 22 00 NA #> 26 25 ResultRow 1 22 0 00 NA #> 27 26 Next 0 3 0 01 NA #> 28 27 Close 0 0 0 00 NA #> 29 28 Halt 0 0 0 00 NA #> 30 29 Transaction 0 0 109 0 01 NA #> 31 30 TableLock 0 12031 0 Batting 00 NA #> 32 31 Goto 0 1 0 00 NA
#> <SQL> #> SELECT * #> FROM `Batting` #> WHERE (`lgID` = 'NL' AND `yearID` = 2000)
#>
#> <PLAN> #> addr opcode p1 p2 p3 p4 p5 comment #> 1 0 Init 0 36 0 00 NA #> 2 1 OpenRead 0 12031 0 22 00 NA #> 3 2 OpenRead 1 19269 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 Seek 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 #> 13 12 Column 0 3 7 00 NA #> 14 13 Copy 2 8 0 00 NA #> 15 14 Column 0 5 9 00 NA #> 16 15 Column 0 6 10 00 NA #> 17 16 Column 0 7 11 00 NA #> 18 17 Column 0 8 12 00 NA #> 19 18 Column 0 9 13 00 NA #> 20 19 Column 0 10 14 00 NA #> 21 20 Column 0 11 15 00 NA #> 22 21 Column 0 12 16 00 NA #> 23 22 Column 0 13 17 00 NA #> 24 23 Column 0 14 18 00 NA #> 25 24 Column 0 15 19 00 NA #> 26 25 Column 0 16 20 00 NA #> 27 26 Column 0 17 21 00 NA #> 28 27 Column 0 18 22 00 NA #> 29 28 Column 0 19 23 00 NA #> 30 29 Column 0 20 24 00 NA #> 31 30 Column 0 21 25 00 NA #> 32 31 ResultRow 4 22 0 00 NA #> 33 32 Next 1 5 1 00 NA #> 34 33 Close 0 0 0 00 NA #> 35 34 Close 1 0 0 00 NA #> 36 35 Halt 0 0 0 00 NA #> 37 36 Transaction 0 0 109 0 01 NA #> 38 37 TableLock 0 12031 0 Batting 00 NA #> 39 38 String8 0 3 0 NL 00 NA #> 40 39 Goto 0 1 0 00 NA
#> <SQL> #> SELECT * #> FROM `Batting` #> WHERE (`lgID` = 'NL' OR `yearID` = 2000.0)
#>
#> <PLAN> #> addr opcode p1 p2 p3 p4 p5 #> 1 0 Init 0 50 0 00 #> 2 1 OpenRead 0 12031 0 22 00 #> 3 2 Null 0 2 0 00 #> 4 3 Integer 23 1 0 00 #> 5 4 OpenRead 1 21523 0 k(2,,) 02 #> 6 5 String8 0 4 0 NL 00 #> 7 6 SeekGE 1 13 4 1 00 #> 8 7 IdxGT 1 13 4 1 00 #> 9 8 Seek 1 0 0 [0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0] 00 #> 10 9 IdxRowid 1 3 0 00 #> 11 10 RowSetTest 2 12 3 0 00 #> 12 11 Gosub 1 24 0 00 #> 13 12 Next 1 7 1 00 #> 14 13 ReopenIdx 1 19269 0 k(2,,) 02 #> 15 14 Real 0 5 0 2000 00 #> 16 15 Affinity 5 1 0 D 00 #> 17 16 SeekGE 1 23 5 1 00 #> 18 17 IdxGT 1 23 5 1 00 #> 19 18 Seek 1 0 0 [0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0] 00 #> 20 19 IdxRowid 1 3 0 00 #> 21 20 RowSetTest 2 22 3 -1 00 #> 22 21 Gosub 1 24 0 00 #> 23 22 Next 1 17 1 00 #> 24 23 Goto 0 48 0 00 #> 25 24 Column 0 0 6 00 #> 26 25 Column 0 1 7 00 #> 27 26 Column 0 2 8 00 #> 28 27 Column 0 3 9 00 #> 29 28 Column 0 4 10 00 #> 30 29 Column 0 5 11 00 #> 31 30 Column 0 6 12 00 #> 32 31 Column 0 7 13 00 #> 33 32 Column 0 8 14 00 #> 34 33 Column 0 9 15 00 #> 35 34 Column 0 10 16 00 #> 36 35 Column 0 11 17 00 #> 37 36 Column 0 12 18 00 #> 38 37 Column 0 13 19 00 #> 39 38 Column 0 14 20 00 #> 40 39 Column 0 15 21 00 #> 41 40 Column 0 16 22 00 #> 42 41 Column 0 17 23 00 #> 43 42 Column 0 18 24 00 #> 44 43 Column 0 19 25 00 #> 45 44 Column 0 20 26 00 #> 46 45 Column 0 21 27 00 #> 47 46 ResultRow 6 22 0 00 #> 48 47 Return 1 0 0 00 #> 49 48 Close 0 0 0 00 #> 50 49 Halt 0 0 0 00 #> 51 50 Transaction 0 0 109 0 01 #> 52 51 TableLock 0 12031 0 Batting 00 #> 53 52 Goto 0 1 0 00 #> comment #> 1 NA #> 2 NA #> 3 NA #> 4 NA #> 5 NA #> 6 NA #> 7 NA #> 8 NA #> 9 NA #> 10 NA #> 11 NA #> 12 NA #> 13 NA #> 14 NA #> 15 NA #> 16 NA #> 17 NA #> 18 NA #> 19 NA #> 20 NA #> 21 NA #> 22 NA #> 23 NA #> 24 NA #> 25 NA #> 26 NA #> 27 NA #> 28 NA #> 29 NA #> 30 NA #> 31 NA #> 32 NA #> 33 NA #> 34 NA #> 35 NA #> 36 NA #> 37 NA #> 38 NA #> 39 NA #> 40 NA #> 41 NA #> 42 NA #> 43 NA #> 44 NA #> 45 NA #> 46 NA #> 47 NA #> 48 NA #> 49 NA #> 50 NA #> 51 NA #> 52 NA #> 53 NA
#> <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 96 0 00 NA #> 2 1 OpenRead 0 12031 0 22 00 NA #> 3 2 OpenRead 1 56568 0 48 00 NA #> 4 3 OpenRead 2 57166 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 Seek 2 0 1 00 NA #> 12 11 Column 0 1 3 00 NA #> 13 12 Column 1 0 4 00 NA #> 14 13 Ne 4 86 3 (BINARY) 53 NA #> 15 14 Integer 1 1 0 00 NA #> 16 15 Column 0 0 5 00 NA #> 17 16 Column 0 1 6 00 NA #> 18 17 Column 0 2 7 00 NA #> 19 18 Column 0 3 8 00 NA #> 20 19 Column 0 4 9 00 NA #> 21 20 Column 0 5 10 00 NA #> 22 21 Column 0 6 11 00 NA #> 23 22 Column 0 7 12 00 NA #> 24 23 Column 0 8 13 00 NA #> 25 24 Column 0 9 14 00 NA #> 26 25 Column 0 10 15 00 NA #> 27 26 Column 0 11 16 00 NA #> 28 27 Column 0 12 17 00 NA #> 29 28 Column 0 13 18 00 NA #> 30 29 Column 0 14 19 00 NA #> 31 30 Column 0 15 20 00 NA #> 32 31 Column 0 16 21 00 NA #> 33 32 Column 0 17 22 00 NA #> 34 33 Column 0 18 23 00 NA #> 35 34 Column 0 19 24 00 NA #> 36 35 Column 0 20 25 00 NA #> 37 36 Column 0 21 26 00 NA #> 38 37 Column 1 1 27 00 NA #> 39 38 Column 1 3 28 00 NA #> 40 39 Column 1 4 29 00 NA #> 41 40 Column 1 5 30 00 NA #> 42 41 Column 1 6 31 00 NA #> 43 42 Column 1 7 32 00 NA #> 44 43 Column 1 8 33 00 NA #> 45 44 Column 1 9 34 00 NA #> 46 45 Column 1 10 35 00 NA #> 47 46 Column 1 11 36 00 NA #> 48 47 Column 1 12 37 00 NA #> 49 48 Column 1 13 38 00 NA #> 50 49 Column 1 14 39 00 NA #> 51 50 Column 1 15 40 00 NA #> 52 51 Column 1 16 41 00 NA #> 53 52 Column 1 17 42 00 NA #> 54 53 Column 1 18 43 00 NA #> 55 54 Column 1 19 44 00 NA #> 56 55 Column 1 20 45 00 NA #> 57 56 Column 1 21 46 00 NA #> 58 57 Column 1 22 47 00 NA #> 59 58 Column 1 23 48 00 NA #> 60 59 Column 1 24 49 00 NA #> 61 60 Column 1 25 50 00 NA #> 62 61 Column 1 26 51 00 NA #> 63 62 Column 1 27 52 00 NA #> 64 63 Column 1 28 53 00 NA #> 65 64 RealAffinity 53 0 0 00 NA #> 66 65 Column 1 29 54 00 NA #> 67 66 Column 1 30 55 00 NA #> 68 67 Column 1 31 56 00 NA #> 69 68 Column 1 32 57 00 NA #> 70 69 Column 1 33 58 00 NA #> 71 70 Column 1 34 59 00 NA #> 72 71 Column 1 35 60 00 NA #> 73 72 Column 1 36 61 00 NA #> 74 73 Column 1 37 62 00 NA #> 75 74 Column 1 38 63 00 NA #> 76 75 Column 1 39 64 00 NA #> 77 76 RealAffinity 64 0 0 00 NA #> 78 77 Column 1 40 65 00 NA #> 79 78 Column 1 41 66 00 NA #> 80 79 Column 1 42 67 00 NA #> 81 80 Column 1 43 68 00 NA #> 82 81 Column 1 44 69 00 NA #> 83 82 Column 1 45 70 00 NA #> 84 83 Column 1 46 71 00 NA #> 85 84 Column 1 47 72 00 NA #> 86 85 ResultRow 5 68 0 00 NA #> 87 86 Next 2 9 1 00 NA #> 88 87 IfPos 1 91 0 00 NA #> 89 88 NullRow 1 0 0 00 NA #> 90 89 NullRow 2 0 0 00 NA #> 91 90 Goto 0 14 0 00 NA #> 92 91 Next 0 5 0 01 NA #> 93 92 Close 0 0 0 00 NA #> 94 93 Close 1 0 0 00 NA #> 95 94 Close 2 0 0 00 NA #> 96 95 Halt 0 0 0 00 NA #> 97 96 Transaction 0 0 109 0 01 NA #> 98 97 TableLock 0 12031 0 Batting 00 NA #> 99 98 TableLock 0 56568 0 Teams 00 NA #> 100 99 Goto 0 1 0 00 NA