Re: Understanding EXPLAIN ANALYZE output

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > I've been wondering about that. A while ago the change was made from
> > outputting a NOTICE with the EXPLAIN output to returning a resultset.
> > If you could agree on what columns to return it might not be so hard
> > for the EXPLAIN to return full tuples...
>
> The major stumbling block to that is that a table is inherently
> unordered, so you'd have to devise a labeling scheme to allow the
> node-tree structure to be represented properly. And without WITH or
> CONNECT BY, it'd be a bit of a PITA for an application to decipher the
> labeling scheme again ...

Only if we chose to represent the tree structure in a way that required it
(like Oracle). There are other ways to represent tree structures.

What if we used integer arrays in an ltree-like way:

[0] Nested Loop (cost=0.00..1369.49 rows=8230 width=945)
[0,0] Join Filter: ("outer".store_location_ids *= "inner".store_location_id)
[0,1] -> Index Scan using idx_cache_ads on cache_ads (cost=0.00..3.17 rows=1 width=219)
[0,1,0] Index Cond: ((region_id = 12159) AND (ad_id = 132094))
[0,2] -> Seq Scan on store_location (cost=0.00..1160.59 rows=16459 width=726)

Even without any special operators this can be ordered easily.
And the code needed to find parent nodes and child nodes exists.

--
greg