Re: Optimizer questions

I wrote:
> As far as that goes, it seems to me after thinking about it that
> non-sort-column tlist items containing SRFs should always be postponed,
> too. Performing a SRF before sorting bloats the sort data vertically,
> rather than horizontally, but it's still bloat. (Although as against
> that, when you have ORDER BY + LIMIT, postponing SRFs loses the ability
> to use a bounded sort.) The killer point though is that unless the sort
> is stable, it might cause surprising changes in the order of the SRF
> output values. Our sorts aren't stable; here's an example in HEAD:

> # select q1, generate_series(1,9) from int8_tbl order by q1 limit 7;
> q1 | generate_series
> -----+-----------------
> 123 | 2
> 123 | 3
> 123 | 4
> 123 | 5
> 123 | 6
> 123 | 7
> 123 | 1
> (7 rows)

> I think that's pretty surprising, and if we have an opportunity to
> provide more intuitive semantics here, we should do it.

Here's an updated patch (requires current HEAD) that takes care of
window functions correctly and also does something reasonable with
set-returning functions:

# explain verbose select q1, generate_series(1,9) from int8_tbl order by q1 limit 7;
QUERY PLAN
---------------------------------------------------------------------------------
Limit (cost=1.11..1.14 rows=7 width=12)
Output: q1, (generate_series(1, 9))
-> Result (cost=1.11..26.16 rows=5000 width=12)
Output: q1, generate_series(1, 9)
-> Sort (cost=1.11..1.12 rows=5 width=8)
Output: q1
Sort Key: int8_tbl.q1
-> Seq Scan on public.int8_tbl (cost=0.00..1.05 rows=5 width=8)
Output: q1
(9 rows)

# select q1, generate_series(1,9) from int8_tbl order by q1 limit 7;
q1 | generate_series
-----+-----------------
123 | 1
123 | 2
123 | 3
123 | 4
123 | 5
123 | 6
123 | 7
(7 rows)

I added some user-facing documentation too. I think this is committable,
though maybe we should add a regression test case or two.

regards, tom lane