Re: right way of using case-expressions in plpgsql functions

From: Victor Dobrovolsky <booby(dot)stager(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: right way of using case-expressions in plpgsql functions
Date: 2023-10-15 20:24:27
Message-ID: CAJFsatPM9D8UJ_CcupDY7iMx6dc7w74BiWhS+E4GwV8FSArKTw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

First of all, thanks everyone for the answers.

вс, 15 окт. 2023 г. в 20:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Sunday, October 15, 2023, Victor Dobrovolsky <booby(dot)stager(at)gmail(dot)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
$$

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2023-10-15 21:14:00 Re: right way of using case-expressions in plpgsql functions
Previous Message Tom Lane 2023-10-15 17:08:16 Re: right way of using case-expressions in plpgsql functions