O'Reilly logo

PostgreSQL High Performance Cookbook by Dinesh Kumar, Chitij Chauhan

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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> ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required