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 1u2crn-003Qrn-Rz for pgsql-general@arkaria.postgresql.org; Wed, 09 Apr 2025 21:21:51 +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 1u2crk-007PrL-Tt for pgsql-general@arkaria.postgresql.org; Wed, 09 Apr 2025 21:21:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u2crk-007Pr7-Bv for pgsql-general@lists.postgresql.org; Wed, 09 Apr 2025 21:21:48 +0000 Received: from poodle.tulip.relay.mailchannels.net ([23.83.218.249]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u2crh-003wf3-2m for pgsql-general@lists.postgresql.org; Wed, 09 Apr 2025 21:21:47 +0000 X-Sender-Id: dreamhost|x-authsender|nico@cryptonector.com Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id 9C114844BEB; Wed, 9 Apr 2025 21:21:43 +0000 (UTC) Received: from pdx1-sub0-mail-a309.dreamhost.com (trex-4.trex.outbound.svc.cluster.local [100.99.235.244]) (Authenticated sender: dreamhost) by relay.mailchannels.net (Postfix) with ESMTPA id 29E5084622F; Wed, 9 Apr 2025 21:21:43 +0000 (UTC) ARC-Seal: i=1; s=arc-2022; d=mailchannels.net; t=1744233703; a=rsa-sha256; cv=none; b=z29GSFg/lDsBcQ7RmJv2T43laGr7YMKeqiokBauhsi8r2FNK1qH5Gm1lyNByHYjhocjCC7 19SejQum1a33OGB+GX6lszPYry7pLnbBdtp9cjTWxZe0SNzIP8caku/90wvgJmax0Wjs1Q Z/PGXlAc/eKvZeK+/yGG48jeoqR0b5UJ+j05dELRNXp2f2ySp0P4tEecb0YkrOigcCKiO4 ijT/S+cH2rP6s+7pe/ebJR2YawlRFOvwGZsxyW4X3TAKx5h2sJBm9rLTsleifYC8rrnr3w Sb/vNC/feQgg2SjICFvRcCTYEuM93jQtwRPyjmFIrV4CuolDToQ66etg0R/BfQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=mailchannels.net; s=arc-2022; t=1744233703; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references:dkim-signature; bh=GEHyPw7BtgoTeQeJ49sd1f6cxk4c1XLhzv1wdf0YR48=; b=aNb3d0WNvIolFLkj/hbe26eAh3I7JseH1h/AmCKVvRfgr/WZgu0GwGzWMFC+yt+BYkrxAs Q9kKXNWPggG9AWvY0O5znGyyb5oINcmwBOMtjEeJSvVpkQYJmcCKzPx1rGmGrqYOfTqAqb KkZChsMJ+aC0h+kXJ+J2yE2Vx5yyP6uTVkRCpQI6XglFvK1A9oVs4bIyAVQKnOCz4ANPIE v8milf1C8SJ57PZpZlyiWNK6wykMeNYhs/pqnwha7eFC8hkawAfDXhMtGnC4wBut9wmsrK 6BnhDREHCrPoIoGlDcpFJ+2qXjorb03RKgffo0JDMb7QcyNnU/yHpyy/GNagrg== ARC-Authentication-Results: i=1; rspamd-6ddf8c4bf5-4dl6v; auth=pass smtp.auth=dreamhost smtp.mailfrom=nico@cryptonector.com X-Sender-Id: dreamhost|x-authsender|nico@cryptonector.com X-MC-Relay: Neutral X-MailChannels-SenderId: dreamhost|x-authsender|nico@cryptonector.com X-MailChannels-Auth-Id: dreamhost X-Belong-Shoe: 51e9b34f68bbc1f2_1744233703401_3858579164 X-MC-Loop-Signature: 1744233703401:2749691491 X-MC-Ingress-Time: 1744233703401 Received: from pdx1-sub0-mail-a309.dreamhost.com (pop.dreamhost.com [64.90.62.162]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384) by 100.99.235.244 (trex/7.0.3); Wed, 09 Apr 2025 21:21:43 +0000 Received: from ubby (syn-075-081-095-064.res.spectrum.com [75.81.95.64]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-256) server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) (Authenticated sender: nico@cryptonector.com) by pdx1-sub0-mail-a309.dreamhost.com (Postfix) with ESMTPSA id 4ZXwqp3qkpz5H; Wed, 9 Apr 2025 14:21:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cryptonector.com; s=dreamhost; t=1744233703; bh=GEHyPw7BtgoTeQeJ49sd1f6cxk4c1XLhzv1wdf0YR48=; h=Date:From:To:Cc:Subject:Content-Type; b=rt5snPTG8N+7/+hJOK4PQdlatzk7Sq2u4d4lB/0UV11PrDAFzEKU3C/Tb1ofwEzcv uU0TiZU4m4kbgXONbvhgJWYz1kwNgTAn+sGWEw+JMc9zmtFQRJ/vLVCB5k1yBW1bmh 2QXlp9SLtTUZjfy8dmPSAiDiILQu3ZUgCvHaX+enntLE8cvJ34h+Rz5oR7XpoQakBT FnGRw00A3MuEbJ2QrO47X3zlhg8N5kYYJArG6fmAkrl5Vz+bLpE30sRw2t3Q0bki7U e6xICZ7XvaPGXhSuZn1he7csYjT6+MUclurAQI31V50IZhCFG9gU9fF6p7hRyTn2jb 4Vk49GNIj/Hvw== Date: Wed, 9 Apr 2025 16:21:40 -0500 From: Nico Williams To: Laurenz Albe Cc: Olleg Samoylov , "pgsql-generallists.postgresql.org" Subject: Re: Interesting case of IMMUTABLE significantly hurting performance Message-ID: References: <662101e2-5ca5-4bf5-9307-2519efde8690@ya.ru> <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8687df7d49bc4b28b41c7cf4e9ad1e15d383b2fa.camel@cybertec.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, Apr 09, 2025 at 10:50:00PM +0200, Laurenz Albe wrote: > The IMMUTABLE function cannot be inlined because to_char() is not IMMUTABLE. Q: Why would to_char() not be IMMUTABLE? A: Because it makes use of locales, and I guess the guc-timezone GUC, which could change if the expression is ultimately used in a PlPgSQL fragment, or if it's in a prepared statement. (I think.) That to_char is not immutable is not documented though. Though it's clear when looking at the docs for the `jsonb_.*_tz()` functions. Nico --