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.001 rows=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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
