I was just writing a syntical example and wanted to make sure it worked.
I found this:
CREATE OR REPLACE FUNCTION RETURN_LOTS(INT) RETURNS SETOF INT AS
$$
SELECT generate_series(1,$1);
$$ COST 0.5 ROWS 10000000 SET work_mem TO '5MB' LANGUAGE 'SQL';
postgres=# explain analyze select return_lots(10000000);
QUERY
PLAN
-----------------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.057..21255.309
rows=10000000 loops=1)
Total runtime: 25784.077 ms
(2 rows)
O.k. slow, but no big deal right? Well:
postgres=# SET cpu_operator_cost to 0.5;
SET
postgres=# set work_mem to 5MB;
SET
postgres=# explain analyze SELECT generate_series(1,10000000);
QUERY
PLAN
----------------------------------------------------------------------------------------------
Result (cost=0.00..0.51 rows=1 width=0) (actual time=0.004..6796.389
rows=10000000 loops=1)
Total runtime: 11301.681 ms
(2 rows)
This is repeatable. I expect a little regression because we have to
compile the SQL but 14 seconds?
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.8 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.1-3ubuntu3) 4.4.1
(1 row)
Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
