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