Using PostgreSQL EXPLAIN

To display the query plan, run EXPLAIN my_query using an SQL client like pgAdmin3 or any other PostgreSQL client.

Example:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=2.37..232.35 rows=106 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
         Index Cond: (unique1 < 100)

The query plan should be read on reverse:

• Bitmap Index Scan on tenk1_unique1 is processed firstly.
• Bitmap Heap Scan on tenk1 is processed secondly.

The structure of a query plan is a tree of plan nodes. Nodes at the bottom level of the tree are table scan nodes: they return raw rows from a table. There are different types of scan nodes for different table access methods: sequential scans, index scans, and bitmap index scans.

Another example:

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

Results in:

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=232.61..741.67 rows=106 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..458.00 rows=10000 width=244)
   ->  Hash  (cost=232.35..232.35 rows=106 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                     Index Cond: (unique1 < 100)

The line starting with Seq Scan indicates that PostgreSQL is processig every row. This takes quite a long time, because the product of the number of rows in each table must be examined.

The row processing of data is called a sequential scan.

Now jump to Understanding the logic behind EXPLAIN ANALYSE to continue.

Reference:
PostgreSQL EXPLAIN syntax