EXPLAIN explained

• EXPLAIN shows the winning plan and all the costs involved in each step
   
• Can be used on any DML command, but not DDL
   
• Shows width, rows, and costs
   
• EXPLAIN ANALYZE runs the command, and shows actual times for each section

Example EXPLAIN plan:

oscon=> explain select oid from pg_proc order by 1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Sort  (cost=151.57..155.30 rows=1492 width=4)
   Sort Key: oid
   ->  Seq Scan on pg_proc  (cost=0.00..72.92 rows=1492 width=4)

• All examples are using psql
   
• Terms: plan, node, operators, filter, input set
   
• All information comes from Scan or Result operators, which are passed upwards
   
• Each operator takes an input set and passes it up until the top node is reached
   
• Parents assume their children's costs
   
• InitPlans / SubPlans for subselects

Previous: Steps of a Query         [Table of Contents]         Next: EXPLAIN: widths