A deeper dive into EXPLAIN
1. A deeper dive into EXPLAIN
Let's continue exploring the query plan with an even deeper dive into EXPLAIN.2. EXPLAIN optional parameters
EXPLAIN has optional parameters that add to the output information. VERBOSE adds additional information to the query plan. It shows the columns available at each plan node. It provides more descriptive names, including the table schema and aliases. ANALYZE actually runs the query. This allows the query plan output to include actual run times in milliseconds instead of unitless time estimates.3. VERBOSE
A simple SELECT query has one sequential scan step. Adding the VERBOSE parameter adds the additional information underlined in yellow. The table name now shows the dairy schema. The sequential step shows the four columns (name, species, type, and age) available at this step.4. ANALYZE
Adding the ANALYZE parameter adds the actual run time information which is underlined in yellow. Actual run times and row counts are available. Planning and execution times are also included. This information is available because the query runs. Generally, ANALYZE is the most useful parameter to add since you usually care most about minimizing overall query run time.5. Query plan - aggregations
The query planner has many operations at its disposal. Adding an aggregation to the cheese query uses the HashAggregate operation. Reading bottom to top, first, the query does a sequential scan of the cheese table. Next, it aggregates the data with cheese type as the grouping. Each of these steps has both the estimates from EXPLAIN as well as the actual millisecond run times from ANALYZE. Additionally, the entire query plan's planning and execution time are calculated in milliseconds.6. Query plan - sort
Adding a sort to a simple SELECT query uses a Sort operation. Only the very top line and lines beginning with an arrow are plan steps. This query plan has two steps with all other output lines providing additional information about a step. Again reading the steps from bottom to top, the query does a sequential scan of the cheese table. Next, it sorts the data. The Sort Key and Sort Method provide additional information about the Sort step.7. Query plan - join
Joining cheese to an animals table uses a Hash Join operation. Again reading bottom to top, the query does a sequential scan of the animals table. This first scan is usually of the smaller table, regardless of which table is referenced first in the SQL. Step two is a Hash. This means that the records examined in the sequential scan step are loaded into temporary, or hash, storage. Next, the plan performs a sequential scan of the cheeses table. The final Hash Join looks if records from the just scanned cheeses table have a match in the animals table. The Hash Condition tells you that the join occurs by matching the species column between the two tables. All of the steps have both the estimates from EXPLAIN as well as the actual millisecond run times from ANALYZE. The entire query plan also has the actual planning and execution times in milliseconds.8. Let's practice!
Reading query plans takes practice. Now it's your turn.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.