Hello I testing a simple use case and I was surprised with very slow execution of SQL functions
create or replace function empty_string1(text) returns bool as $$ select $1 is NULL or $1 = '' $$ language sql; postgres=# select count(empty_string1(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,100000); count -------- 100000 (1 row) Time: 448.616 ms little bit updated function is much faster create or replace function empty_string2(text) returns bool as $$ select coalesce($1,'') = '' $$ language sql; postgres=# select count(empty_string2(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,100000); count -------- 100000 (1 row) Time: 64.437 ms just null test function is fast too (or just empty str function) postgres=# create or replace function empty_string1(text) returns bool as $$select $1 is NULL $$ language sql; CREATE FUNCTION Time: 21.929 ms postgres=# select count(empty_string1(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,100000); count -------- 100000 (1 row) Time: 48.554 ms Is strange - so slow function can be replaced by plpgsql function and it's faster postgres=# create or replace function empty_string1(text) returns bool as $$begin return $1 is null or $1 = ''; end$$ language plpgsql immutable; CREATE FUNCTION Time: 70.359 ms postgres=# select count(empty_string1(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) FROM generate_series(1,100000); count -------- 100000 (1 row) Time: 220.131 ms Tested on 9.1 without assertions Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
