Re: Review: UNNEST (and other functions) WITH ORDINALITY

On Mon, Jul 29, 2013 at 8:56 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> Unless LATERAL provides a way to do lock-step iteration through a pair
> (or more) of functions I don't think we can get rid of SRFs [in select target lists] yet

You don't even need lateral. This works fine:

postgres=# select * from generate_series(1,10) with ordinality as
a(a,o) natural full outer join generate_series(1,5) with ordinality as
b(b,o) ;
o  | a  | b
----+----+--- 1 |  1 | 1 2 |  2 | 2 3 |  3 | 3 4 |  4 | 4 5 |  5 | 5 6 |  6 | 7 |  7 | 8 |  8 | 9 |  9 |10 | 10 |
(10 rows)

However it occurs to me that the plan isn't ideal:

postgres=# explain select * from generate_series(1,10) with ordinality
as a(a,o) natural full outer join generate_series(1,5) with ordinality
as b(b,o) ;                                     QUERY PLAN
---------------------------------------------------------------------------------------Merge Full Join
(cost=119.66..199.66rows=5000 width=24)  Merge Cond: (a.o = b.o)  ->  Sort  (cost=59.83..62.33 rows=1000 width=12)
 Sort Key: a.o        ->  Function Scan on generate_series a  (cost=0.00..10.00
 
rows=1000 width=12)  ->  Sort  (cost=59.83..62.33 rows=1000 width=12)        Sort Key: b.o        ->  Function Scan on
generate_seriesb  (cost=0.00..10.00
 
rows=1000 width=12)
(8 rows)


I think all that's required to avoid the sorts is teaching the planner
that the Path has a PathKey of the ordinal column. I can look at that
later but I'll go ahead and commit it without it at first. I wonder if
it's also useful to teach the planner that the column is unique.

-- 
greg