Re: Understanding EXPLAIN ANALYZE output

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Martijn van Oosterhout <kleptog@svana.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