[HACKERS] Amazing performance failure with SQL function

[Joshua D. Drake]

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 1000 SET work_mem TO '5MB' LANGUAGE 'SQL';

postgres=# explain analyze select return_lots(1000);
  QUERY
PLAN   
---
 Result  (cost=0.00..0.26 rows=1 width=0) (actual time=0.057..21255.309
rows=1000 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,1000);
  QUERY
PLAN  
--
 Result  (cost=0.00..0.51 rows=1 width=0) (actual time=0.004..6796.389
rows=1000 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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Amazing performance failure with SQL function

[Tom Lane]

Joshua D. Drake j...@commandprompt.com writes:
 This is repeatable. I expect a little regression because we have to
 compile the SQL but 14 seconds? 

generate_series is a quite efficient C function.  I think it's pretty
damn good that the overhead of a SQL function on top of that is only 2X.

Or were you expecting the SRF to be inlined?  If so, you need to
(a) be using 8.4, (b) mark it STABLE or IMMUTABLE.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers