One of the new features of PostgreSQL 9.0 is the ability to specify the format of an explain plan. In prior versions your only choice was text (and graphic explain with tools like PgAdmin III and other GUIS), but in 9.0 on, you have the additional options of Javascript Object Notation (JSON) which some people have a thing or two to say about them, YAML Ain't Markup Language (YAML) or eXtended Markup Language (XML). The new explain options are itemized in PostgreSQL 9.0 EXPLAIN.
The main benefit of the JSON, XML, YAML formats is that they are easier to machine parse than the default text version. This will allow for creative renderings of planner trees with minimal coding.
In Part 1 of this series, we'll demonstrate how to output the plans in these various formats and what they look like. In later parts of this series -- we'll demonstrate how to use Javascript, XSL and other scripting/markup languages to transform these into works of art you can hang on your wall.
-- START POSTGIS IN ACTION ASIDE --We just submitted the third major revision of Chapter 3 Data Modeling of our upcoming PostGIS in Action book. The second major revision we never submitted and threw it out because it wasn't worldly enough and was too involved. We may use it later on for an example. Chapter 3 should be up on Manning Early Access Program (MEAP) soon. If you haven't bought the book yet Buy now. You don't want to miss out on a major masterpiece in the making. Okay we exaggerate a bit.
-- END POSTGIS IN ACTION ASIDE --One of the benefits of slaving away trying to create real world data examples, is that then you can use them to demonstrate totally unrelated topics, such as doing artistic things with EXPLAIN PLANS. In our explain plan, we'll be using example data from this chapter 3.
Explaining the Query
For those who are not familiar with explain plans, explain plans are in a nutshell a description of how a database planner is strategizing how to complete a query. They look like trees because the master plan usually involves subplans. Any database worth its weight has such a thing though they may go by slightly different name - PostgreSQL, SQL Server, IBM DB2, Oracle, MySQL, Firebird, etc. PostgreSQL's flavor of it is fairly detailed as far as databases go.
In these examples, we will demonstrate an explain plan involving inherited tables as well as using hstore columns and indexes on those. The plan as you can see forms a tree.
The familiar plain text Explain Plan
-- PLAIN TEXT --
EXPLAIN ANALYZE
SELECT feature_name, tags->'tourism' As tourism_type FROM ch03.paris
WHERE ar_num = 8 AND tags?'tourism';
Result (cost=0.00..50.13 rows=7 width=405) (actual time=0.140..0.232 rows=4 loops=1)
-> Append (cost=0.00..50.11 rows=7 width=405) (actual time=0.132..0.219 rows=4 loops=1)
-> Index Scan using idx_paris_tags on paris (cost=0.00..8.27 rows=1 width=450) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (tags ? 'tourism'::text)
Filter: (ar_num = 8)
-> Seq Scan on paris_linestrings paris (cost=0.00..11.80 rows=1 width=450) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((tags ? 'tourism'::text) AND (ar_num = 8))
-> Index Scan using idx_paris_points_tags on paris_points paris (cost=0.00..8.27 rows=1 width=450)
(actual time=0.003..0.003 rows=0 loops=1)
Index Cond: (tags ? 'tourism'::text)
Filter: (ar_num = 8)
-> Index Scan using idx_paris_polygons_tags on paris_polygons paris (cost=0.00..8.27 rows=1 width=450)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (tags ? 'tourism'::text)
Filter: (ar_num = 8)
-> Seq Scan on paris_linestrings_ar_08 paris (cost=0.00..7.27 rows=1 width=513) (actual time=0.104..0.104 rows=0 loops=1)
Filter: ((tags ? 'tourism'::text) AND (ar_num = 8))
-> Seq Scan on paris_points_ar_08 paris (cost=0.00..5.16 rows=1 width=72) (actual time=0.009..0.085 rows=4 loops=1)
Filter: ((tags ? 'tourism'::text) AND (ar_num = 8))
-> Seq Scan on paris_polygons_ar_08 paris (cost=0.00..1.08 rows=1 width=450) (actual time=0.007..0.007 rows=0 loops=1)
Filter: ((tags ? 'tourism'::text) AND (ar_num = 8))
Total runtime: 1.314 ms
The PgAdmin III Graphical Explain Plan
JSON Format Explain Plan
--JSON --
EXPLAIN (ANALYZE true, COSTS true, FORMAT json)
SELECT feature_name, tags->'tourism' As tourism_type FROM ch03.paris
WHERE ar_num = 8 AND tags?'tourism';
[
{
"Plan": {
"Node Type": "Result",
"Startup Cost": 0.00,
"Total Cost": 50.13,
"Plan Rows": 7,
"Plan Width": 405,
"Actual Startup Time": 0.139,
"Actual Total Time": 0.231,
"Actual Rows": 4,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Append",
"Parent Relationship": "Outer",
"Startup Cost": 0.00,
"Total Cost": 50.11,
"Plan Rows": 7,
"Plan Width": 405,
"Actual Startup Time": 0.131,
"Actual Total Time": 0.218,
"Actual Rows": 4,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Scan Direction": "NoMovement",
"Index Name": "idx_paris_tags",
"Relation Name": "paris",
"Alias": "paris",
"Startup Cost": 0.00,
"Total Cost": 8.27,
"Plan Rows": 1,
"Plan Width": 450,
"Actual Startup Time": 0.009,
"Actual Total Time": 0.009,
"Actual Rows": 0,
"Actual Loops": 1,
"Index Cond": "(tags ? 'tourism'::text)",
"Filter": "(ar_num = 8)"
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Member",
"Relation Name": "paris_linestrings",
"Alias": "paris",
"Startup Cost": 0.00,
"Total Cost": 11.80,
"Plan Rows": 1,
"Plan Width": 450,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 0,
"Actual Loops": 1,
"Filter": "((tags ? 'tourism'::text) AND (ar_num = 8))"
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Scan Direction": "NoMovement",
"Index Name": "idx_paris_points_tags",
"Relation Name": "paris_points",
"Alias": "paris",
"Startup Cost": 0.00,
"Total Cost": 8.27,
"Plan Rows": 1,
"Plan Width": 450,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.003,
"Actual Rows": 0,
"Actual Loops": 1,
"Index Cond": "(tags ? 'tourism'::text)",
"Filter": "(ar_num = 8)"
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Scan Direction": "NoMovement",
"Index Name": "idx_paris_polygons_tags",
"Relation Name": "paris_polygons",
"Alias": "paris",
"Startup Cost": 0.00,
"Total Cost": 8.27,
"Plan Rows": 1,
"Plan Width": 450,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 0,
"Actual Loops": 1,
"Index Cond": "(tags ? 'tourism'::text)",
"Filter": "(ar_num = 8)"
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Member",
"Relation Name": "paris_linestrings_ar_08",
"Alias": "paris",
"Startup Cost": 0.00,
"Total Cost": 7.27,
"Plan Rows": 1,
"Plan Width": 513,
"Actual Startup Time": 0.103,
"Actual Total Time": 0.103,
"Actual Rows": 0,
"Actual Loops": 1,
"Filter": "((tags ? 'tourism'::text) AND (ar_num = 8))"
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Member",
"Relation Name": "paris_points_ar_08",
"Alias": "paris",
"Startup Cost": 0.00,
"Total Cost": 5.16,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 0.009,
"Actual Total Time": 0.085,
"Actual Rows": 4,
"Actual Loops": 1,
"Filter": "((tags ? 'tourism'::text) AND (ar_num = 8))"
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Member",
"Relation Name": "paris_polygons_ar_08",
"Alias": "paris",
"Startup Cost": 0.00,
"Total Cost": 1.08,
"Plan Rows": 1,
"Plan Width": 450,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.007,
"Actual Rows": 0,
"Actual Loops": 1,
"Filter": "((tags ? 'tourism'::text) AND (ar_num = 8))"
}
]
}
]
},
"Triggers": [
],
"Total Runtime": 1.238
}
]
XML Format Explain Plan
The XML in itself is very useful because you can navigate it with XML viewers, or the built-in XML viewers in IE and Firefox. They are also fairly easy to stylize with with XSL.
-- XML --
EXPLAIN (ANALYZE true, COSTS true, FORMAT xml)
SELECT feature_name, tags->'tourism' As tourism_type FROM ch03.paris
WHERE ar_num = 8 AND tags?'tourism';
<explain xmlns="http://www.postgresql.org/2009/explain">
<Query>
<Plan>
<Node-Type>Result</Node-Type>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>50.13</Total-Cost>
<Plan-Rows>7</Plan-Rows>
<Plan-Width>405</Plan-Width>
<Actual-Startup-Time>0.136</Actual-Startup-Time>
<Actual-Total-Time>0.230</Actual-Total-Time>
<Actual-Rows>4</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Plans>
<Plan>
<Node-Type>Append</Node-Type>
<Parent-Relationship>Outer</Parent-Relationship>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>50.11</Total-Cost>
<Plan-Rows>7</Plan-Rows>
<Plan-Width>405</Plan-Width>
<Actual-Startup-Time>0.128</Actual-Startup-Time>
<Actual-Total-Time>0.217</Actual-Total-Time>
<Actual-Rows>4</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Plans>
<Plan>
<Node-Type>Index Scan</Node-Type>
<Parent-Relationship>Member</Parent-Relationship>
<Scan-Direction>NoMovement</Scan-Direction>
<Index-Name>idx_paris_tags</Index-Name>
<Relation-Name>paris</Relation-Name>
<Alias>paris</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>8.27</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>450</Plan-Width>
<Actual-Startup-Time>0.008</Actual-Startup-Time>
<Actual-Total-Time>0.008</Actual-Total-Time>
<Actual-Rows>0</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Index-Cond>(tags ? 'tourism'::text)</Index-Cond>
<Filter>(ar_num = 8)</Filter>
</Plan>
<Plan>
<Node-Type>Seq Scan</Node-Type>
<Parent-Relationship>Member</Parent-Relationship>
<Relation-Name>paris_linestrings</Relation-Name>
<Alias>paris</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>11.80</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>450</Plan-Width>
<Actual-Startup-Time>0.001</Actual-Startup-Time>
<Actual-Total-Time>0.001</Actual-Total-Time>
<Actual-Rows>0</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Filter>((tags ? 'tourism'::text) AND (ar_num = 8))</Filter>
</Plan>
<Plan>
<Node-Type>Index Scan</Node-Type>
<Parent-Relationship>Member</Parent-Relationship>
<Scan-Direction>NoMovement</Scan-Direction>
<Index-Name>idx_paris_points_tags</Index-Name>
<Relation-Name>paris_points</Relation-Name>
<Alias>paris</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>8.27</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>450</Plan-Width>
<Actual-Startup-Time>0.002</Actual-Startup-Time>
<Actual-Total-Time>0.002</Actual-Total-Time>
<Actual-Rows>0</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Index-Cond>(tags ? 'tourism'::text)</Index-Cond>
<Filter>(ar_num = 8)</Filter>
</Plan>
<Plan>
<Node-Type>Index Scan</Node-Type>
<Parent-Relationship>Member</Parent-Relationship>
<Scan-Direction>NoMovement</Scan-Direction>
<Index-Name>idx_paris_polygons_tags</Index-Name>
<Relation-Name>paris_polygons</Relation-Name>
<Alias>paris</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>8.27</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>450</Plan-Width>
<Actual-Startup-Time>0.002</Actual-Startup-Time>
<Actual-Total-Time>0.002</Actual-Total-Time>
<Actual-Rows>0</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Index-Cond>(tags ? 'tourism'::text)</Index-Cond>
<Filter>(ar_num = 8)</Filter>
</Plan>
<Plan>
<Node-Type>Seq Scan</Node-Type>
<Parent-Relationship>Member</Parent-Relationship>
<Relation-Name>paris_linestrings_ar_08</Relation-Name>
<Alias>paris</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>7.27</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>513</Plan-Width>
<Actual-Startup-Time>0.102</Actual-Startup-Time>
<Actual-Total-Time>0.102</Actual-Total-Time>
<Actual-Rows>0</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Filter>((tags ? 'tourism'::text) AND (ar_num = 8))</Filter>
</Plan>
<Plan>
<Node-Type>Seq Scan</Node-Type>
<Parent-Relationship>Member</Parent-Relationship>
<Relation-Name>paris_points_ar_08</Relation-Name>
<Alias>paris</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>5.16</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>72</Plan-Width>
<Actual-Startup-Time>0.008</Actual-Startup-Time>
<Actual-Total-Time>0.086</Actual-Total-Time>
<Actual-Rows>4</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Filter>((tags ? 'tourism'::text) AND (ar_num = 8))</Filter>
</Plan>
<Plan>
<Node-Type>Seq Scan</Node-Type>
<Parent-Relationship>Member</Parent-Relationship>
<Relation-Name>paris_polygons_ar_08</Relation-Name>
<Alias>paris</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>1.08</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>450</Plan-Width>
<Actual-Startup-Time>0.007</Actual-Startup-Time>
<Actual-Total-Time>0.007</Actual-Total-Time>
<Actual-Rows>0</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Filter>((tags ? 'tourism'::text) AND (ar_num = 8))</Filter>
</Plan>
</Plans>
</Plan>
</Plans>
</Plan>
<Triggers>
</Triggers>
<Total-Runtime>1.208</Total-Runtime>
</Query>
</explain>
YAML Format Explain Plan
-- YAML --
EXPLAIN (ANALYZE true, COSTS true, FORMAT yaml)
SELECT feature_name, tags->'tourism' As tourism_type FROM ch03.paris
WHERE ar_num = 8 AND tags?'tourism';
- Plan:
Node Type: "Result"
Startup Cost: 0.00
Total Cost: 50.13
Plan Rows: 7
Plan Width: 405
Actual Startup Time: 0.139
Actual Total Time: 0.232
Actual Rows: 4
Actual Loops: 1
Plans:
- Node Type: "Append"
Parent Relationship: "Outer"
Startup Cost: 0.00
Total Cost: 50.11
Plan Rows: 7
Plan Width: 405
Actual Startup Time: 0.131
Actual Total Time: 0.219
Actual Rows: 4
Actual Loops: 1
Plans:
- Node Type: "Index Scan"
Parent Relationship: "Member"
Scan Direction: "NoMovement"
Index Name: "idx_paris_tags"
Relation Name: "paris"
Alias: "paris"
Startup Cost: 0.00
Total Cost: 8.27
Plan Rows: 1
Plan Width: 450
Actual Startup Time: 0.009
Actual Total Time: 0.009
Actual Rows: 0
Actual Loops: 1
Index Cond: "(tags ? 'tourism'::text)"
Filter: "(ar_num = 8)"
- Node Type: "Seq Scan"
Parent Relationship: "Member"
Relation Name: "paris_linestrings"
Alias: "paris"
Startup Cost: 0.00
Total Cost: 11.80
Plan Rows: 1
Plan Width: 450
Actual Startup Time: 0.001
Actual Total Time: 0.001
Actual Rows: 0
Actual Loops: 1
Filter: "((tags ? 'tourism'::text) AND (ar_num = 8))"
- Node Type: "Index Scan"
Parent Relationship: "Member"
Scan Direction: "NoMovement"
Index Name: "idx_paris_points_tags"
Relation Name: "paris_points"
Alias: "paris"
Startup Cost: 0.00
Total Cost: 8.27
Plan Rows: 1
Plan Width: 450
Actual Startup Time: 0.002
Actual Total Time: 0.002
Actual Rows: 0
Actual Loops: 1
Index Cond: "(tags ? 'tourism'::text)"
Filter: "(ar_num = 8)"
- Node Type: "Index Scan"
Parent Relationship: "Member"
Scan Direction: "NoMovement"
Index Name: "idx_paris_polygons_tags"
Relation Name: "paris_polygons"
Alias: "paris"
Startup Cost: 0.00
Total Cost: 8.27
Plan Rows: 1
Plan Width: 450
Actual Startup Time: 0.002
Actual Total Time: 0.002
Actual Rows: 0
Actual Loops: 1
Index Cond: "(tags ? 'tourism'::text)"
Filter: "(ar_num = 8)"
- Node Type: "Seq Scan"
Parent Relationship: "Member"
Relation Name: "paris_linestrings_ar_08"
Alias: "paris"
Startup Cost: 0.00
Total Cost: 7.27
Plan Rows: 1
Plan Width: 513
Actual Startup Time: 0.104
Actual Total Time: 0.104
Actual Rows: 0
Actual Loops: 1
Filter: "((tags ? 'tourism'::text) AND (ar_num = 8))"
- Node Type: "Seq Scan"
Parent Relationship: "Member"
Relation Name: "paris_points_ar_08"
Alias: "paris"
Startup Cost: 0.00
Total Cost: 5.16
Plan Rows: 1
Plan Width: 72
Actual Startup Time: 0.008
Actual Total Time: 0.086
Actual Rows: 4
Actual Loops: 1
Filter: "((tags ? 'tourism'::text) AND (ar_num = 8))"
- Node Type: "Seq Scan"
Parent Relationship: "Member"
Relation Name: "paris_polygons_ar_08"
Alias: "paris"
Startup Cost: 0.00
Total Cost: 1.08
Plan Rows: 1
Plan Width: 450
Actual Startup Time: 0.007
Actual Total Time: 0.007
Actual Rows: 0
Actual Loops: 1
Filter: "((tags ? 'tourism'::text) AND (ar_num = 8))"
Triggers:
Total Runtime: 1.301
Stay tuned for our next parts when we demonstrate how to add a little spice to these with some basic web scripting techniques.