On Mon, Jul 29, 2013 at 8:56 AM, Craig Ringer <[email protected]> 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
