EXPLAIN plan Operators

Operator	Associated With	Startup cost?
Seq Scan	almost anything	No
Sort	ORDER BY, other operators (e.g. Unique)	Yes
Index Scan	any non-hash index	No
Result	non-table queries, WHERE constants	No
Unique	DISTINCT, UNION	Yes
Limit	LIMIT, OFFSET	Yes (if OFFSET >0)
Aggregate	COUNT, SUM, MIN, MAX, AVG, STDDEV, VARIANCE	Yes
Group	GROUP BY clause	Yes
Append	UNION, inheritance	No
Nested Loop	INNER JOIN, LEFT OUTER JOIN	No
Merge Join	INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN	Yes
Hash	Hash Join operator	Yes
Hash Join	INNER JOIN, LEFT OUTER JOIN	Yes
Subquery Scan	UNION	No
Tid Scan	"ctid =" in the query	No
Materialize	subselects, other operators	Yes
Function Scan	functions	No
SetOp Intersect	INTERSECT	Yes
SetOp Intersect All	INTERSECT ALL	Yes
SetOp Except	EXCEPT	Yes
SetOp Except All	EXCEPT ALL	Yes


oscon=> \d pg_proc
      Table "pg_catalog.pg_proc"
    Column    |   Type    | Modifiers 
--------------+-----------+-----------
 proname      | name      | not null
 pronamespace | oid       | not null
 proowner     | integer   | not null
 prolang      | oid       | not null
 proisagg     | boolean   | not null
 prosecdef    | boolean   | not null
 proisstrict  | boolean   | not null
 proretset    | boolean   | not null
 provolatile  | "char"    | not null
 pronargs     | smallint  | not null
 prorettype   | oid       | not null
 proargtypes  | oidvector | not null
 prosrc       | text      | 
 probin       | bytea     | 
 proacl       | aclitem[] | 
Indexes: 
  pg_proc_oid_index unique btree (oid),
  pg_proc_proname_args_nsp_index unique btree (proname, pronargs, proargtypes, pronamespace)

For all of the examples below, the tables used (mostly pg_proc) have been ANALYZED.

Operator: Seq Scan

Example of a Seq Scan operator:


oscon=>  explain select oid from pg_proc;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on pg_proc  (cost=0.00..72.92 rows=1492 width=4)

Most basic, simply scans from start to end
Checks each row for constraints as it goes
Mostly undesirable (Index Scan)
Width (oid), Rows (tuples)
Startup cost is 0
Total cost is 72.92 - how?

Computing the cost of a Seq Scan


oscon=> select count(*) from pg_proc;
 count 
-------
  1492

oscon=> select relpages, reltuples from pg_class where relname = 'pg_proc';
 relpages | reltuples 
----------+-----------
       58 |      1492

Estimates *only*!
Dead tuples, inserts (MVCC)
Ten/one thousand rule (without ANALYZE)


oscon=> select 72.92 - 58 as cpu_cost;
 cpu_cost 
----------
    14.92


oscon=> show cpu_tuple_cost;
 cpu_tuple_cost 
----------------
 0.01


oscon=> select 1492 * 0.01 as cpu_cost; 
 cpu_cost 
----------
    14.92

14.92 + 58.00 = 72.92

The cost of a WHERE clause

Can we trick it?


oscon=>  explain select oid from pg_proc where 1+1=2;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on pg_proc  (cost=0.00..72.92 rows=1492 width=4)


oscon=>  explain select oid from pg_proc where 1+1=3;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Result  (cost=0.00..72.92 rows=1492 width=4)
   One-Time Filter: false
   ->  Seq Scan on pg_proc  (cost=0.00..72.92 rows=1492 width=4)

Arrows, indents, and operators
Outer vs. inner


oscon=>  explain select oid from pg_proc where oid>0;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on pg_proc  (cost=0.00..76.65 rows=497 width=4)
   Filter: (oid > 0::oid)

Seq Scan formulas

startup_cost = (inherited) + tuples fetched;
cpu_per_tuple = cpu_tuple_cost + restriction cost
run_cost = cpu_per_tuple * tuples


oscon=> select 0.01 + 0.0025 AS cpu_per_tuple;
 cpu_per_tuple 
---------------
        0.0125

Multiply our cpu_per_tuple times the number of tuples for the total cpu cost:


oscon=> select 0.0125 *1492 as cpu_cost;
 cpu_cost 
----------
  18.6500

The fetch cost is simple:


oscon=> SELECT relpages*1.0 AS fetch_cost from pg_class where relname='pg_proc';
 fetch_cost 
------------
       58.0

Total cost: fetch cost + cpu_costs:


oscon=> select 18.65 + 58.0 as total_cost;
 total_cost 
------------
      76.65

Double check the row independence:


oscon=>  explain select oid from pg_proc where oid!=0;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on pg_proc  (cost=0.00..76.65 rows=1491 width=4)
   Filter: (oid <> 0::oid)

Adding to the WHERE clause:


oscon=>  explain select oid from pg_proc where oid!=0 and oid < 100;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on pg_proc  (cost=0.00..80.38 rows=497 width=4)
   Filter: ((oid <> 0::oid) AND (oid < 100::oid)

Since we have two operations going on, let's double the restriction cost in our total_cost calculation. The new cpu_per_tuple cost:


oscon=> select 0.01 + 0.0025 + 0.0025 AS cpu_per_tuple;
 cpu_per_tuple 
---------------
        0.0150

Times the number of tuples gives the cpu cost:


oscon=> select (0.01 + 0.0025 + 0.0025) * 1492 AS cpu_cost;
 cpu_cost 
----------
  22.38

Now the total cost can be computed:


oscon=> select 58.00 + ((0.01 + 0.0025 + 0.0025) * 1492) AS total_cost;
 total_cost 
------------
    80.3800

Operator: Sort


oscon=> explain select oid from pg_proc;
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on pg_proc  (cost=0.00..72.92 rows=1492 width=4)

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)

Explicit: ORDER BY clause
Implicit: Unique and other operators
Has startup cost: cannot return right away
More complicated cost formula

startup_cost = (inherited) + 2.0 * cpu_operator_cost * tuples * (ln(x) / 0.693147180559945)
run_cost = cpu_operator_cost * tuples;


oscon=> select 2 * 0.0025 * 1492 * (ln(1492) / 0.693147180559945) AS startup_cost;
   startup_cost   
------------------
 78.6510173791041

#define LOG2(x)  (log(x) / 0.693147180559945)

startup_cost = 72.92 + 78.65 = 151.57

run_cost = 0.0025 *1492 = 3.73

total_cost = 151.57 + 3.73 = 155.30

Operator: Index Scan


oscon=> explain select oid from pg_proc where oid=1;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using pg_proc_oid_index on pg_proc  (cost=0.00..5.98 rows=1 width=4)
   Index Cond: (oid = 1::oid)

Lower cost usually makes it preferred (5.98 vs 72.92)/
Very complex cost calculation

Operator: Result


oscon=>  explain select oid from pg_proc where 1+1=3;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Result  (cost=0.00..72.92 rows=1492 width=4)
   One-Time Filter: false
   ->  Seq Scan on pg_proc  (cost=0.00..72.92 rows=1492 width=4)

Shorten the output:


oscon=>  explain select oid from pg_proc where 1+1=3;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Result  (C=0.00..72.92 R=1492 W=4)
   One-Time Filter: false
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Non-table queries
Inside a WHERE clause ('true' vs. 'false')


oscon=# explain select 1492 * .0025;
           QUERY PLAN           
--------------------------------
 Result  (C=0.00..0.01 R=1 W=0)

C = cost
R = rows
W = width


oscon=# explain select oid from pg_proc where 1=1;
                   QUERY PLAN                    
-------------------------------------------------
 Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)


oscon=# explain select oid from pg_proc where 1=2;
                      QUERY PLAN                       
-------------------------------------------------------
 Result  (C=0.00..72.92 R=1492 W=4)
   One-Time Filter: false
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Operator: Unique


oscon=> explain select distinct oid from pg_proc;
                         QUERY PLAN                          
-------------------------------------------------------------
 Unique  (C=151.57..159.03 R=149 W=4)
   ->  Sort  (C=151.57..155.30 R=1492 W=4)
         Sort Key: oid
         ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Removes duplicate values from the input set
Does not change ordering, simply fails to pass on any duplicated rows
Incoming set must be ordered (may force a Sort operator)
Two "cpu_operations" per tuple cost
Used with DISTINCT and UNION
10% rows ballpark estimate

159.03 - 151.57 = 7.46 = 1492 * (.0025 + .0025)

oscon=> explain select oid from pg_proc union select oid from pg_proc;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Unique  (C=318.06..332.98 R=298 W=4)
   ->  Sort  (C=318.06..325.52 R=2984 W=4)
         Sort Key: oid
         ->  Append  (C=0.00..145.84 R=2984 W=4)
               ->  Subquery Scan "*SELECT* 1"  (C=0.00..72.92 R=1492 W=4)
                     ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)
               ->  Subquery Scan "*SELECT* 2"  (C=0.00..72.92 R=1492 W=4)
                     ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Operator: Limit


oscon=> explain select oid from pg_proc limit 5;
                      QUERY PLAN                       
-------------------------------------------------------
 Limit  (C=0.00..0.24 R=5 W=4)
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Rows is what was specified (5)
Stays within reason: stats dependent


oscon=> explain select oid from pg_proc limit 5000;
                      QUERY PLAN                       
-------------------------------------------------------
 Limit  (C=0.00..72.92 R=1492 W=4)
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Lack of an OFFSET clause = OFFSET 0
Can return first row immediately (no startup cost)


oscon=> explain select oid from pg_proc limit 5 offset 0;
                      QUERY PLAN                       
-------------------------------------------------------
 Limit  (C=0.00..0.24 R=5 W=4)
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Offset incurs a small startup cost:

oscon=> explain select oid from pg_proc limit 5 offset 1;
                      QUERY PLAN                       
-------------------------------------------------------
 Limit  (C=0.05..0.29 R=5 W=4)
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Operator: Aggregate

oscon=# explain select count(*) from pg_proc;
                      QUERY PLAN                       
-------------------------------------------------------
 Aggregate  (C=76.65..76.65 R=1 W=0)
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=0)

Startup cost = 76.66 - 72.93 = 3.73 (familiar?)
Used with: COUNT SUM MIN MAX AVG STDDEV VARIANCE
Slight difference between GROUP and non-GROUP:


oscon=# explain select stddev(*) from pg_proc;
                      QUERY PLAN                       
-------------------------------------------------------
 Aggregate  (C=76.65..76.65 R=1 W=0)
   ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=0)


oscon=# explain select count(oid), oid FROM pg_proc group by oid;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate  (C=151.57..162.76 R=149 W=4)
   ->  Group  (C=151.57..159.03 R=1492 W=4)
         ->  Sort  (C=151.57..155.30 R=1492 W=4)
               Sort Key: oid
               ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Rows: 10% ballpark
EXPLAIN ANALYZE..


oscon=# explain analyze select count(oid), oid FROM pg_proc group by oid;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Aggregate  (C=151.57..162.76 R=149 W=4) (AT=36.80..75.47 R=1492 L=1)
   ->  Group  (C=151.57..159.03 R=1492 W=4) (AT=36.72..57.98 R=1492 L=1)
         ->  Sort  (C=151.57..155.30 R=1492 W=4) (AT=36.69..40.26 R=1492 L=1)
               Sort Key: oid
               ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4) (AT=0.03..22.60 R=1492 L=1)
 Total runtime: 80.04 msec

Operator: Group


oscon=> explain select count(oid), oid FROM pg_proc group by oid;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate  (C=151.57..162.76 R=149 W=4)
   ->  Group  (C=151.57..159.03 R=1492 W=4)
         ->  Sort  (C=151.57..155.30 R=1492 W=4)
               Sort Key: oid
               ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Used with the GROUP BY clause. Often used with aggregate commands
If without aggregate, 10% rule. With = exact


oscon=> explain select oid from pg_proc group by oid;
                         QUERY PLAN                          
-------------------------------------------------------------
 Group  (C=151.57..159.03 R=149 W=4)
   ->  Sort  (C=151.57..155.30 R=1492 W=4)
         Sort Key: oid
         ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Operator: Append


oscon=> explain select oid from pg_proc union select oid from pg_proc;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Unique  (C=318.06..332.98 R=298 W=4)
   ->  Sort  (C=318.06..325.52 R=2984 W=4)
         Sort Key: oid
         ->  Append  (C=0.00..145.84 R=2984 W=4)
               ->  Subquery Scan "*SELECT* 1"  (C=0.00..72.92 R=1492 W=4)
                     ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)
               ->  Subquery Scan "*SELECT* 2"  (C=0.00..72.92 R=1492 W=4)
                     ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Triggered by UNIONS, inheritance
No startup cost
Cost is simply the sum of all inputs


oscon=> explain select oid from pg_proc union all select oid from pg_proc;
                          QUERY PLAN                          
--------------------------------------------------------------
 Append  (C=0.00..145.84 R=2984 W=4)
   ->  Subquery Scan "*SELECT* 1"  (C=0.00..72.92 R=1492 W=4)
         ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)
   ->  Subquery Scan "*SELECT* 2"  (C=0.00..72.92 R=1492 W=4)
         ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Operator: Nested Loop


oscon=> explain select relname,nspname from pg_class 
oscon-> left join pg_namespace on (pg_class.relnamespace = pg_namespace.oid);
                       QUERY PLAN                        
---------------------------------------------------------
 Nested Loop  (C=0.00..139.40 R=124 W=136)
   Join Filter: ("outer".relnamespace = "inner".oid)
   ->  Seq Scan on pg_class  (C=0.00..4.24 R=124 W=68)
   ->  Seq Scan on pg_namespace  (C=0.00..1.04 R=4 W=68)


oscon=> explain select relname,nspname from pg_class 
oscon-> join pg_namespace on (pg_class.relnamespace = pg_namespace.oid);
                       QUERY PLAN                        
---------------------------------------------------------
 Nested Loop  (C=0.00..24.20 R=124 W=136)
   Join Filter: ("inner".relnamespace = "outer".oid)
   ->  Seq Scan on pg_namespace  (C=0.00..1.04 R=4 W=68)
   ->  Seq Scan on pg_class  (C=0.00..4.24 R=124 W=68)

Joins two tables (two input sets)
Used with INNER JOIN and LEFT OUTER JOIN
Scans 'outer' table, finds matches in 'inner' table
No startup cost

Operator: Merge Join


oscon=> explain select relname,nspname from pg_class 
oscon-> left join pg_namespace on (pg_class.relnamespace = pg_namespace.oid);
                          QUERY PLAN                           
---------------------------------------------------------------
 Merge Join  (C=9.63..11.82 R=124 W=136)
   Merge Cond: ("outer".relnamespace = "inner".oid)
   ->  Sort  (C=8.55..8.86 R=124 W=68)
         Sort Key: pg_class.relnamespace
         ->  Seq Scan on pg_class  (C=0.00..4.24 R=124 W=68)
   ->  Sort  (C=1.08..1.09 R=4 W=68)
         Sort Key: pg_namespace.oid
         ->  Seq Scan on pg_namespace  (C=0.00..1.04 R=4 W=68)

Joins two sets: outer and an inner
Can do INNER AND OUTER joins
Set must be pre-ordered, walk through both simultaneously

Operator: Hash

Simply creates a hash table for use by the Hash Join operator
Always used on the 'inner' table

Operator: Hash Join


oscon=> explain select relname,nspname from pg_class 
oscon-> left join pg_namespace on (pg_class.relnamespace = pg_namespace.oid);
                          QUERY PLAN                           
---------------------------------------------------------------
 Hash Join  (C=1.05..7.46 R=124 W=136)
   Hash Cond: ("outer".relnamespace = "inner".oid)
   ->  Seq Scan on pg_class  (C=0.00..4.24 R=124 W=68)
   ->  Hash  (C=1.04..1.04 R=4 W=68)
         ->  Seq Scan on pg_namespace  (C=0.00..1.04 R=4 W=68)

Compares two input sets by building hash tables
Used for INNER JOIN and LEFT JOIN
The Hash operator is always the 'inner' table
Creating the hash incurs a startup cost
Similar to Nested Loop, but much more efficient

Operator: Subquery Scan


oscon=> explain select oid from pg_proc union select oid from pg_proc;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Unique  (C=318.06..332.98 R=298 W=4)
   ->  Sort  (C=318.06..325.52 R=2984 W=4)
         Sort Key: oid
         ->  Append  (C=0.00..145.84 R=2984 W=4)
               ->  Subquery Scan "*SELECT* 1"  (C=0.00..72.92 R=1492 W=4)
                     ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)
               ->  Subquery Scan "*SELECT* 2"  (C=0.00..72.92 R=1492 W=4)
                     ->  Seq Scan on pg_proc  (C=0.00..72.92 R=1492 W=4)

Used with UNIONS, not terribly important

Operator: Tid Scan


oscon=> explain select * from pg_proc where ctid = '(0,1)';
                  QUERY PLAN                   
-----------------------------------------------
 Tid Scan on pg_proc  (C=0.00..4.01 R=1 W=311)
   Filter: (ctid = '(0,1)'::tid)

Column tuple ID
Only used when "ctid =" appears in your query
Very rare, very fast
Deleting rows example

Operator: Materialize


oscon=> explain select oid from pg_proc where proowner in (select 1);
                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on pg_proc  (C=0.00..91.57 R=746 W=4)
   Filter: (subplan)
   SubPlan
     ->  Materialize  (C=0.01..0.01 R=1 W=0)
           ->  Result  (C=0.00..0.01 R=1 W=0)

Used with subselects and Merge Joins
Puts a result set into a temporary table
Allows forward and back movement

Operator: Function Scan


oscon=> explain select * FROM foo(12);
                   QUERY PLAN                    
-------------------------------------------------
 Function Scan on foo  (C=0.00..12.50 R=1000 W=4)

Seen when a function is gathering data

Operator: SetOp Intersect

Operator: SetOp Intersect All

Operator: SetOp Except

Operator: SetOp Except All

Finds groups of duplicate rows
Used (obviously) for INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL

oscon=> explain select oid FROM pg_class INTERSECT select oid from pg_class;
                               QUERY PLAN                               
------------------------------------------------------------------------
 SetOp Intersect  (C=18.34..19.58 R=25 W=4)
   ->  Sort  (C=18.34..18.96 R=248 W=4)
         Sort Key: oid
         ->  Append  (C=0.00..8.48 R=248 W=4)
               ->  Subquery Scan "*SELECT* 1"  (C=0.00..4.24 R=124 W=4)
                     ->  Seq Scan on pg_class  (C=0.00..4.24 R=124 W=4)
               ->  Subquery Scan "*SELECT* 2"  (C=0.00..4.24 R=124 W=4)
                     ->  Seq Scan on pg_class  (C=0.00..4.24 R=124 W=4)

Previous: EXPLAIN ANALYZE [Table of Contents] Next: Forcing a plan