PostgreSQL High Performance Cookbook

Generating an explain plan

In this recipe, we will be discussing various supported formats of the EXPLAIN command.

Getting ready

The EXPLAIN command provides multiple formats of the plan output, which gives more flexibility in parsing the explain plan. In PostgreSQL 9.6, EXPLAIN supports the following formats:

• TEXT
• XML
• YAML
• JSON

How to do it…

1. Let's generate the XML formatted explain plan as follows:

   benchmarksql=# EXPLAIN (FORMAT XML) SELECT * FROM bmsql_customer;
                           QUERY PLAN                        
   ----------------------------------------------------------
    <explain xmlns="http://www.postgresql.org/2009/explain">+
        <Query>                                               +
         <Plan>                                              +
           <Node-Type>Seq Scan</Node-Type>                   +
           <Relation-Name>bmsql_customer</Relation-Name>     +
           <Alias>bmsql_customer</Alias>                     +
    <Startup-Cost>0.00</Startup-Cost> ...