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

On Mon, Jul 29, 2013 at 8:56 AM, Craig Ringer <craig(at)2ndquadrant(dot)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.66 rows=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_series b (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