Skip site navigation (1) Skip section navigation (2)

performance issue: logical operators are slow inside SQL function: missing optimalization?

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: performance issue: logical operators are slow inside SQL function: missing optimalization?
Date: 2010-08-29 10:20:50
Message-ID: AANLkTinZSUK0uY5=8cpnW+t2r7itei1SnQynq0hv1MbC@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: Marko TiikkajaDate: 2010-08-29 10:55:08
Subject: Re: performance issue: logical operators are slow inside SQL function: missing optimalization?
Previous:From: Tom LaneDate: 2010-08-28 23:43:54
Subject: Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group