Re: Add YAML option to explain - Mailing list pgsql-hackers
| From | Greg Sabino Mullane |
|---|---|
| Subject | Re: Add YAML option to explain |
| Date | |
| Msg-id | 26198c84d0c325a6126a8368e99b4fe9@biglumber.com Whole thread Raw |
| In response to | Re: Add YAML option to explain (Ron Mayer <rm_pg@cheapcomplexdevices.com>) |
| Responses |
Re: Add YAML option to explain
Re: Add YAML option to explain |
| List | pgsql-hackers |
-----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.10rows=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, "ActualRows": 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 ActualLoops: 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 PlanRows: 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 Scanusing 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@turnstep.com PGP Key: 0x14964AC8 200908311000 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkqb2r0ACgkQvJuQZxSWSshbEACgzAIXc6dNM/+dDmE8Xvjyg147 SrsAniMfB5RBhnq9EWY95+fiDSkLCRPy =G8Al -----END PGP SIGNATURE-----
pgsql-hackers by date: