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

2010/8/29 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2010/8/29 Tom Lane <tgl@sss.pgh.pa.us>:
>>> The one case is inline-able and the other not (because it would result
>>> in double evaluation of the volatile function random()).
>>> See EXPLAIN VERBOSE.
>
>> I understand now. So it means general advice - don't use a boolean
>> operators in SQL function? This issue should be documented somewhere?
>
> It has nothing to do with boolean operators, just double evaluation.
>

sure. I was blind. I have a question. It is possible do following
optimalisation?

I can write a function

CREATE OR REPLACE FUNCTION estring(text)
RETURNS bool AS $$
SELECT x IS NULL || x = ''  FROM (VALUES($1)) g(x)
$$ LANGUAGE sql;

Now this function isn't inlined, because optimaliser doesn't know a
VALUES clause. But with this knowleade, this can be a protection
before double evaluation. Or different way - generate_subplan with
parameters - it is still faster, than plpgsql or not inlined sql.

p.s. this query is badly planed

postgres=# select sum((select x is null or x = '' from (values(CASE
WHEN random() < 0.5 THEN NULL ELSE 'x' END)) g(x) )::int) FROM
generate_series(1,100000); sum
--------100000
(1 row)

for corect behave a had to append a second variable
postgres=# select sum((select x is null or x = '' and i = i from
(values(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) g(x) )::int)
FROM generate_series(1,100000) x(i); sum
-------50036
(1 row)

Regards

Pavel Stehule


>                        regards, tom lane
>