Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qs4bH-006hkP-QV for pgsql-general@arkaria.postgresql.org; Sun, 15 Oct 2023 17:08:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qs4bF-00BufZ-9c for pgsql-general@arkaria.postgresql.org; Sun, 15 Oct 2023 17:08:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qs4bE-00BufR-V1 for pgsql-general@lists.postgresql.org; Sun, 15 Oct 2023 17:08:21 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qs4bB-000p4X-Ga for pgsql-general@lists.postgresql.org; Sun, 15 Oct 2023 17:08:21 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 39FH8GYf2095405; Sun, 15 Oct 2023 13:08:16 -0400 From: Tom Lane To: "David G. Johnston" cc: Victor Dobrovolsky , "pgsql-general@lists.postgresql.org" Subject: Re: right way of using case-expressions in plpgsql functions In-reply-to: References: Comments: In-reply-to "David G. Johnston" message dated "Sun, 15 Oct 2023 09:37:49 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <2095403.1697389696.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Sun, 15 Oct 2023 13:08:16 -0400 Message-ID: <2095404.1697389696@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "David G. Johnston" writes: > On Sunday, October 15, 2023, Victor Dobrovolsky > 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