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