Re: Add YAML option to explain

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Greg, can we see a few examples of the YAML output
> compared to both json and text?

Sure. Be warned it will make this email long. Because email may wrap things
funny, I'll post the same thing here:

Query 1:
http://pgsql.privatepaste.com/298pqiSwdH

Note that YAML quotes things like JSON does, but only when the quotes are needed.
Query 2:
http://pgsql.privatepaste.com/610uDDyMu6

greg=# explain (format text, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3;
QUERY PLAN
- --------------------------------------------------------------------------------------------------------------
Sort (cost=12.82..13.10 rows=111 width=185) (actual time=1.176..1.401 rows=105 loops=1)
Sort Key: relname, relnamespace, reltype
Sort Method: quicksort Memory: 44kB
-> Seq Scan on pg_class (cost=0.00..9.05 rows=111 width=185) (actual time=0.066..0.828 rows=105 loops=1)
Filter: (relname ~ 'x'::text)
Total runtime: 1.676 ms

greg=# explain (format json, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3;
QUERY PLAN
- -----------------------------------------------------------
[
{
"Plan": {
"Node Type": "Sort",
"Startup Cost": 12.82,
"Total Cost": 13.10,
"Plan Rows": 111,
"Plan Width": 185,
"Actual Startup Time": 1.152,
"Actual Total Time": 1.373,
"Actual Rows": 105,
"Actual Loops": 1,
"Sort Key": ["relname", "relnamespace", "reltype"],
"Sort Method": "quicksort",
"Sort Space Used": 44,
"Sort Space Type": "Memory",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Relation Name": "pg_class",
"Alias": "pg_class",
"Startup Cost": 0.00,
"Total Cost": 9.05,
"Plan Rows": 111,
"Plan Width": 185,
"Actual Startup Time": 0.067,
"Actual Total Time": 0.817,
"Actual Rows": 105,
"Actual Loops": 1,
"Filter": "(relname ~ 'x'::text)"
}
]
},
"Triggers": [
],
"Total Runtime": 1.649
}
]

greg=# explain (format yaml, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3;
QUERY PLAN
- ---------------------------------------
-
Plan:
Node Type: Sort
Startup Cost: 12.82
Total Cost: 13.10
Plan Rows: 111
Plan Width: 185
Actual Startup Time: 1.159
Actual Total Time: 1.391
Actual Rows: 105
Actual Loops: 1
Sort Key:
- relname
- relnamespace
- reltype
Sort Method: quicksort
Sort Space Used: 44
Sort Space Type: Memory
Plans:
-
Node Type: Seq Scan
Parent Relationship: Outer
Relation Name: pg_class
Alias: pg_class
Startup Cost: 0.00
Total Cost: 9.05
Plan Rows: 111
Plan Width: 185
Actual Startup Time: 0.067
Actual Total Time: 0.829
Actual Rows: 105
Actual Loops: 1
Filter: (relname ~ 'x'::text)
Triggers:
Total Runtime: 1.671

greg=# explain (format xml, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3;
QUERY PLAN
- ------------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain">
<Query>
<Plan>
<Node-Type>Sort</Node-Type>
<Startup-Cost>12.82</Startup-Cost>
<Total-Cost>13.10</Total-Cost>
<Plan-Rows>111</Plan-Rows>
<Plan-Width>185</Plan-Width>
<Actual-Startup-Time>1.154</Actual-Startup-Time>
<Actual-Total-Time>1.382</Actual-Total-Time>
<Actual-Rows>105</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Sort-Key>
<Item>relname</Item>
<Item>relnamespace</Item>
<Item>reltype</Item>
</Sort-Key>
<Sort-Method>quicksort</Sort-Method>
<Sort-Space-Used>44</Sort-Space-Used>
<Sort-Space-Type>Memory</Sort-Space-Type>
<Plans>
<Plan>
<Node-Type>Seq Scan</Node-Type>
<Parent-Relationship>Outer</Parent-Relationship>
<Relation-Name>pg_class</Relation-Name>
<Alias>pg_class</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>9.05</Total-Cost>
<Plan-Rows>111</Plan-Rows>
<Plan-Width>185</Plan-Width>
<Actual-Startup-Time>0.066</Actual-Startup-Time>
<Actual-Total-Time>0.837</Actual-Total-Time>
<Actual-Rows>105</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Filter>(relname ~ 'x'::text)</Filter>
</Plan>
</Plans>
</Plan>
<Triggers>
</Triggers>
<Total-Runtime>1.655</Total-Runtime>
</Query>
</explain>

An example with embedded quotes:

greg=# explain (format text, analyze on) select 1 from pg_class where relname = 'foo"bar"';
QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.00..8.27 rows=1 width=0) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: (relname = 'foo"bar"'::name)
Total runtime: 0.056 ms

greg=# explain (format json, analyze on) select 1 from pg_class where relname = 'foo"bar"';
QUERY PLAN
- ------------------------------------------------------
[
{
"Plan": {
"Node Type": "Index Scan",
"Scan Direction": "Forward",
"Index Name": "pg_class_relname_nsp_index",
"Relation Name": "pg_class",
"Alias": "pg_class",
"Startup Cost": 0.00,
"Total Cost": 8.27,
"Plan Rows": 1,
"Plan Width": 0,
"Actual Startup Time": 0.015,
"Actual Total Time": 0.015,
"Actual Rows": 0,
"Actual Loops": 1,
"Index Cond": "(relname = 'foo\"bar\"'::name)"
},
"Triggers": [
],
"Total Runtime": 0.046
}
]

greg=# explain (format yaml, analyze on) select 1 from pg_class where relname = 'foo"bar"';
QUERY PLAN
- --------------------------------------------------
-
Plan:
Node Type: Index Scan
Scan Direction: Forward
Index Name: pg_class_relname_nsp_index
Relation Name: pg_class
Alias: pg_class
Startup Cost: 0.00
Total Cost: 8.27
Plan Rows: 1
Plan Width: 0
Actual Startup Time: 0.019
Actual Total Time: 0.019
Actual Rows: 0
Actual Loops: 1
Index Cond: "(relname = 'foo\"bar\"'::name)"
Triggers:
Total Runtime: 0.058

greg=# explain (format xml, analyze on) select 1 from pg_class where relname = 'foo"bar"';
QUERY PLAN
- -------------------------------------------------------------
<explain xmlns="http://www.postgresql.org/2009/explain">
<Query>
<Plan>
<Node-Type>Index Scan</Node-Type>
<Scan-Direction>Forward</Scan-Direction>
<Index-Name>pg_class_relname_nsp_index</Index-Name>
<Relation-Name>pg_class</Relation-Name>
<Alias>pg_class</Alias>
<Startup-Cost>0.00</Startup-Cost>
<Total-Cost>8.27</Total-Cost>
<Plan-Rows>1</Plan-Rows>
<Plan-Width>0</Plan-Width>
<Actual-Startup-Time>0.013</Actual-Startup-Time>
<Actual-Total-Time>0.013</Actual-Total-Time>
<Actual-Rows>0</Actual-Rows>
<Actual-Loops>1</Actual-Loops>
<Index-Cond>(relname = 'foo"bar"'::name)</Index-Cond>
</Plan>
<Triggers>
</Triggers>
<Total-Runtime>0.049</Total-Runtime>
</Query>
</explain>

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200908311000
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkqb2r0ACgkQvJuQZxSWSshbEACgzAIXc6dNM/+dDmE8Xvjyg147
SrsAniMfB5RBhnq9EWY95+fiDSkLCRPy
=G8Al
-----END PGP SIGNATURE-----