Re: right way of using case-expressions in plpgsql functions
| От | Victor Dobrovolsky |
|---|---|
| Тема | Re: right way of using case-expressions in plpgsql functions |
| Дата | |
| Msg-id | CAJFsatPM9D8UJ_CcupDY7iMx6dc7w74BiWhS+E4GwV8FSArKTw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: right way of using case-expressions in plpgsql functions (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: right way of using case-expressions in plpgsql functions
|
| Список | pgsql-general |
First of all, thanks everyone for the answers.
вс, 15 окт. 2023 г. в 20:08, Tom Lane <tgl@sss.pgh.pa.us>:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, October 15, 2023, Victor Dobrovolsky <booby.stager@gmail.com>
> wrote:
>> select (case when px is not null then pf = px
>> else pf is not distinct from py
>> end);
> Every single time this function is called “px is not null” will be
> evaluated and then one of the two branches will be evaluated. Nothing the
> optimizer does will change that. The planner for the function internals
> does not know whether px will or will not be null on any given invocation.
Not necessarily --- I think the SQL-language function will get inlined
and then there would be opportunity for const-simplification if a
known value is available for px in the outer function.
At least in the px-not-null case, having "pf = px" rather than an
impenetrable CASE condition will probably be enough better for
optimization that the plancache would never choose to switch to a
generic plan. However, that might not be true for the other case,
since we aren't terribly smart about optimizing NOT DISTINCT conditions.
So the performance you get might well vary depending on which case
occurs more often during the first few query runs.
Ok. I am a "man coming from Oracle-sql" .
The first case is reminiscence of using oracle nvl "sql-function" in form of
Select *
from tbl
Where tbl.somefield = nvl(:parameter, tbl.somefield)
;
In such a case Oracle will produce generic plan like this:
Filter :parameter is not null
Select * from tbl
Where tbl.somefield = :parameter -- index scan if possible
Union All
Filter :parameter is null
Select * from tbl
Where 1=1 -- table full scan guaranteed
;
Here aligned left Filter conditions assured, that only one branch of union all will be really taken in each particular execution of query.
Regarding the "pf is not distinct from py" condition - I saw that it was translated to "not (pf is distinct from py)"
which is totally correct from a mathematical point of view.
But maybe it would be useful to have an independent translation of the statement that "pf is identical to pf",
to eliminate the condition totally at last....
(don't pay too much attention, this is a superficial newbie opinion, anyway)
In total - should I manually divide these cases in the plpgsql function if I like to avoid any prepared statements caveats, or should I use "execute"-statements, if I am lazy enough for that,
or, there is nothing to complain in terms of "generic plan"...
On the whole though, the entire question seems like solving the wrong
problem. If you have to resort to this kind of thing to get your
answers, it's past time to rethink your data representation.
Definitely Yes.
But...
My goal is "to translate" some application "as fast as possible", using "as few structure transformations as possible".
From that - "short and dirty translation" - point of view - should I prefer to divide that
$$
Select
case $1
when '1'::numeric then tbl.p1
when '2'::numeric then tbl.p2
...
end as cresult
when '1'::numeric then tbl.p1
when '2'::numeric then tbl.p2
...
end as cresult
$$
expression into:
$$
...
if $1 = '1'::numeric then (query_1)
elsif when '2'::numeric then (query_2)
...
end if;
...
$$
If I do not want to use an execute statement for that?
Thank you.
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: right way of using case-expressions in plpgsql functions