Re: Add YAML option to explain - Mailing list pgsql-hackers

On Mon, Aug 31, 2009 at 02:15:08PM -0000, Greg Sabino Mullane wrote:
> > Greg, can we see a few examples of the YAML output
> > compared to both json and text?
...
> greg=# explain (format json, analyze on) select * from pg_class where relname ~ 'x' order by 1,2,3;
>                         QUERY PLAN
> - -----------------------------------------------------------

An interesting property of json, it is almost exactly the same as python
data structure syntax. If I paste the following into python:

plan = [        {          "Plan": {            "Node Type": "Sort",            "Startup Cost": 12.82,
"TotalCost": 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,
"PlanWidth": 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        }      ]
 

I get a python data structure. Which can be manipulated directly, or pretty
printed:

>>> import pprint
>>> pprint.pprint(plan)
[{'Plan': {'Actual Loops': 1,          'Actual Rows': 105,          'Actual Startup Time': 1.1519999999999999,
'ActualTotal Time': 1.373,          'Node Type': 'Sort',          'Plan Rows': 111,          'Plan Width': 185,
'Plans': [{'Actual Loops': 1,                     'Actual Rows': 105,                     'Actual Startup Time':
0.067000000000000004,                    'Actual Total Time': 0.81699999999999995,                     'Alias':
'pg_class',                    'Filter': "(relname ~ 'x'::text)",                     'Node Type': 'Seq Scan',
          'Parent Relationship': 'Outer',                     'Plan Rows': 111,                     'Plan Width': 185,
                  'Relation Name': 'pg_class',                     'Startup Cost': 0.0,                     'Total
Cost':9.0500000000000007}],          'Sort Key': ['relname', 'relnamespace', 'reltype'],          'Sort Method':
'quicksort',         'Sort Space Type': 'Memory',          'Sort Space Used': 44,          'Startup Cost': 12.82,
  'Total Cost': 13.1}, 'Total Runtime': 1.649, 'Triggers': []}]
 

I'm not sure if all json can be read this way, but the python and json
notations are very similar.

-dg

--
David Gould       daveg@sonic.net      510 536 1443    510 282 0869
If simplicity worked, the world would be overrun with insects.