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