Possible optimization on Function Scan

I was a bit surprised to discover the difference below in calling an SRF 
as part of a target list vs part of the from clause. The from clause 
generates a Function Scan, which (apparently blindly) builds a 
tuplestore. Is there a relatively easy way to either transform this type 
of query so the SRF is back in a target list, or teach Function Scan 
that it doesn't always need to create a tuplestore? It would be nice if 
we could just not use a tuplestore at all (depending on the planner to 
add a Materialize node if necessary), but AIUI functions can directly 
return a tuplestore, so I guess that's not an option...

> ~@decina/45678# explain (analyze,verbose,buffers) select count(*) from (select generate_series(1,99999999)) c;
>                                                QUERY PLAN
> --------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=17.51..17.52 rows=1 width=8) (actual time=27085.104..27085.104 rows=1 loops=1)
>    Output: count(*)
>    ->  Result  (cost=0.00..5.01 rows=1000 width=4) (actual time=0.007..14326.945 rows=99999999 loops=1)
>          Output: generate_series(1, 99999999)
>  Planning time: 0.125 ms
>  Execution time: 27085.153 ms
> (6 rows)
>
> Time: 27087.624 ms
> ~@decina/45678# explain (analyze,verbose,buffers) select count(*) from generate_series(1,99999999);
>                                                                     QUERY PLAN
>
--------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=12.50..12.51 rows=1 width=8) (actual time=57968.811..57968.812 rows=1 loops=1)
>    Output: count(*)
>    Buffers: temp read=170900 written=170899
>    ->  Function Scan on pg_catalog.generate_series  (cost=0.00..10.00 rows=1000 width=0) (actual
time=22407.515..44908.001rows=99999999 loops=1)
 
>          Output: generate_series
>          Function Call: generate_series(1, 99999999)
>          Buffers: temp read=170900 written=170899
>  Planning time: 0.060 ms
>  Execution time: 58054.981 ms
> (9 rows)
>
> Time: 58055.929 ms


-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461