CREATE OR REPLACE FUNCTION explain(_sql text) RETURNS SETOF text AS $$BEGIN
  RETURN QUERY EXECUTE format('EXPLAIN (verbose, costs off) %s', _sql);
END;$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION describe(_sql text) RETURNS SETOF text AS $$
  SELECT regexp_split_to_table(regexp_replace(explain, '^  Output: ', ''), ', ')
   FROM (SELECT * FROM explain(_sql) WHERE explain ~ '^  Output: ') x;
$$ LANGUAGE sql;

SELECT * FROM describe($$
with t1(x) as (values (1),(2))
   , t2(x) as (values (2),(3))
select *
from t1 join t2 ON t1.x=t2.x
$$);

DROP FUNCTION IF EXISTS describe(text);
CREATE OR REPLACE FUNCTION describe(_sql text) RETURNS TABLE(line int, level int, node text, output text[], conditions text) AS $$
  WITH a AS (
    SELECT line, explain, lag(explain) OVER (ORDER BY line) scan, lead(explain) OVER (ORDER BY line) cond
      FROM (SELECT (row_number() OVER ())::int line, explain FROM explain(_sql)) e
  ), b AS (
    SELECT *, length(regexp_replace(scan, '^([ ]*).*$', '\1'))/2 as level FROM a
     WHERE explain ~ 'Output' AND scan ~ 'Scan|Join|Loop'
  )
  SELECT line,level,regexp_replace(scan, '^[ ]*(:?->  )?', '') node,
         regexp_split_to_array(regexp_replace(explain, '^[ ]*Output: ', ''), ', ') output,
         CASE WHEN scan ~ 'Join' THEN regexp_replace(cond, '^[ ]*', '') END condition
    FROM b
$$ LANGUAGE sql;

SELECT * FROM describe(
$$with t1(x) as (values (1),(2)),
       t2(x) as (values (2),(3))
select 1 from t1 join t2 ON t1.x=t2.x$$)
ORDER BY level, line;