From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: performance issue: logical operators are slow inside SQL function: missing optimalization? |
Date: | 2010-08-29 15:42:44 |
Message-ID: | AANLkTimVG6ttcFaTQEUg09-qG2vuDyExhGU8THbVg3BM@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
2010/8/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/8/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2010-08-29 15:46:27 | Re: performance issue: logical operators are slow inside SQL function: missing optimalization? |
Previous Message | Tom Lane | 2010-08-29 15:23:29 | Re: performance issue: logical operators are slow inside SQL function: missing optimalization? |