From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Victor Dobrovolsky <booby.stager@gmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: right way of using case-expressions in plpgsql functions
Date: Sun, 15 Oct 2023 13:08:16 -0400
Message-ID: <2095404.1697389696@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwbb4_XaC4PxVJEP+L8zWKFuBNRfz7+rQhub4TozcAjMBQ@mail.gmail.com>
References: <CAJFsatMhj1oX8kVcSUiTkEddk=Evh10NH9v=-Nxo8vD7xQ0mLg@mail.gmail.com>
<CAKFQuwbb4_XaC4PxVJEP+L8zWKFuBNRfz7+rQhub4TozcAjMBQ@mail.gmail.com>
"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.
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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, booby.stager@gmail.com
Subject: Re: right way of using case-expressions in plpgsql functions
In-Reply-To: <2095404.1697389696@sss.pgh.pa.us>
References: <CAJFsatMhj1oX8kVcSUiTkEddk=Evh10NH9v=-Nxo8vD7xQ0mLg@mail.gmail.com> <CAKFQuwbb4_XaC4PxVJEP+L8zWKFuBNRfz7+rQhub4TozcAjMBQ@mail.gmail.com> <2095404.1697389696@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox