EXPLAIN

EXPLAIN causes POSTGRESQL to display how a query will be executed, rather than executing it. As an example, Figure  shows a SELECT query preceeded by the word EXPLAIN.  

        test=> EXPLAIN SELECT customer_id FROM customer;
        NOTICE:  QUERY PLAN:
         
        Seq Scan on customer  (cost=0.00..15.00 rows=1000 width=4)
         
        EXPLAIN  

In the figure, POSTGRESQL reports a sequential scan  will be used on customer, meaning it will read the entire table. The cost is an estimate of the work required to execute the query (the numbers are only meaningful for comparison). The rows indicates the number of result rows expected. The width is the number of bytes per row.

Figure  shows more interesting examples of EXPLAIN.  

        test=> EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55;
        NOTICE:  QUERY PLAN:
         
        Seq Scan on customer  (cost=0.00..22.50 rows=10 width=4)
         
        EXPLAIN
        test=> VACUUM ANALYZE customer;
        VACUUM
        test=> EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55;
        NOTICE:  QUERY PLAN:
         
        Seq Scan on customer  (cost=0.00..17.50 rows=1 width=4)
         
        EXPLAIN
        test=> CREATE UNIQUE INDEX customer_custid_idx ON customer (customer_id);
        CREATE
        test=> EXPLAIN SELECT customer_id FROM customer WHERE customer_id = 55;
        NOTICE:  QUERY PLAN:
         
        Index Scan using customer_custid_idx on customer  (cost=0.00..2.01 rows=1 width=4)
         
        EXPLAIN
        test=> EXPLAIN SELECT customer_id FROM customer;
        NOTICE:  QUERY PLAN:
         
        Seq Scan on customer  (cost=0.00..15.00 rows=1000 width=4)
         
        EXPLAIN
        test=> EXPLAIN SELECT * FROM customer ORDER BY customer_id;
        NOTICE:  QUERY PLAN:
         
        Index Scan using customer_custid_idx on customer  (cost=0.00..42.00 rows=1000 width=4)
         
        EXPLAIN  

The first EXPLAIN shows a SELECT with the restriction customer_id = 55. The command reports another sequential scan, but the restriction causes POSTGRESQL to estimate that ten rows will be returned. A VACUUM  ANALYZE  command is then run, causing the next query to properly estimate that one row will be returned instead of ten.  An index is created, and the query rerun. This time, an index scan  is used, allowing POSTGRESQL to go directly to the rows where customer_id equals 55. The next EXPLAIN shows a query with no WHERE restriction. POSTGRESQL realizes that the index is useless and performs a sequential scan. The last query has an ORDER BY  that matches an index, so POSTGRESQL uses an index scan. 

Even more complex queries can be studied using EXPLAIN, as shown in Figure .  

        test=> EXPLAIN SELECT * FROM tab1, tab2 WHERE col1 = col2;
        NOTICE:  QUERY PLAN:
         
        Merge Join  (cost=139.66..164.66 rows=10000 width=8)
          ->  Sort  (cost=69.83..69.83 rows=1000 width=4)
                ->  Seq Scan on tab2  (cost=0.00..20.00 rows=1000 width=4)
          ->  Sort  (cost=69.83..69.83 rows=1000 width=4)
                ->  Seq Scan on tab1  (cost=0.00..20.00 rows=1000 width=4)
         
        EXPLAIN  

 In this example, tab1 and tab2 are joined on col1 and col2. Each table is sequentially scanned, and the result sorted. The two results are then merge joined  to produce output. It also supports hash join  and nested loop  join methods. It chooses the join method it believes to be the fastest.