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.96) (envelope-from ) id 1wCf7l-002Cc2-1E for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 14:52:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wCf7j-00CQIQ-2Y for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 14:52:20 +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.96) (envelope-from ) id 1wCf7j-00CQIH-0b for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 14:52:20 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wCf7h-00000000zIv-2NVr for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 14:52:19 +0000 Received: by mail-ej1-x62e.google.com with SMTP id a640c23a62f3a-b9c755b2cdeso906180266b.2 for ; Tue, 14 Apr 2026 07:52:17 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776178336; cv=none; d=google.com; s=arc-20240605; b=N8C7xT+i5dCLegRvPLm/qNirmmp2TnCxYaf4KuJZBqHA+RxS34IGMr5IDAwschMvda cNX0P3+gzYsukWIYhRMYbFg9x252Bi/NLoNeBmRL94EJd9qLWh5SMk9St21p/KbNuBz1 zJMtzCc41CgLymtd12DXng5DSBloacuNoCrnQJc6N/ItRjwH6kKI225b1KdH7hqoUBPn LwuWXREIy86h4Vo0ZLZw+uyMjl035z+I58j0sMYStp4Mfk1UNAHvTut7LwZSrdQRJw3B +uie0TXP3MpJB4IaHs/UYYmEayrkJ1ORM1OrG/QI1TaorHng8+J2MYYjwRwnm9vFvbu6 yUPQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=iQBhYE9PFTFp59E5mgngV5acWY6KQM3asLVYoxNIdYs=; fh=YVqAsyQLaqkSOJ1zvqJdlUnoB6bSkwHNHq4/MX0Vk/s=; b=AA7Ev/V7uo4J45bJtEReOeB5coHqv7qvMzcVjgUJ+iSUU0/E8RdVwWuwy2tcIq3EIY 56EjLxzPZlLtxQ6zVkyCZtH6fV7uHoxzTUmRhVSfskbSGeb+HfO5onl3to2R3JVYfVLQ eZcOqY7IWyeRIyeZhlgiu24xQVwGmXsBFrQ5iB1IqGJSsg7c8DU0PqevHnj2rOG/iYeM x/7Mzoud4pjhc6sDlCuxfITxLcE2Xb3WD0ydxmPniI8o6O3JeeJmPnByevM588JABL9b zl8ZvuHUSECvkw8fOlQnFDSYaj3bleNF6IiF9HVM6YFI2R2qnIs6Zo03VBqWM/jdF92A 2nLQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1776178336; x=1776783136; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=iQBhYE9PFTFp59E5mgngV5acWY6KQM3asLVYoxNIdYs=; b=XddHnIj2ck2nKK7YGGSoOI/JWV+f2Uqq7gHHpI4yfYV3uz/ePOmB5He2e1JrO1I4ve 6TpRjhaTBjFU0mmpJuGEb0afCY19ypyWjSwAfFZ0H25kdja6JjxGqOtx21kPm0LQP89a 4mbrhL8h2wiSCh0zEZ5bG2J1KJJcqSp74eo0UFujKd/8I4b9f6YBEftqkPNhl/H4MUco rqxj6ENPbbUZf6R0GdaMgwTY+76QaUcO3VbyYc+gS2z2yhUiOD54XzeE43Eg6XSsBnQy vtefKleNxN/Rhik4avpUSvr2fg8fK2oUW1UYQtWvnRIkrDyya7rifOvScVSVSIrAP5zM tbJg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776178336; x=1776783136; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=iQBhYE9PFTFp59E5mgngV5acWY6KQM3asLVYoxNIdYs=; b=LlCLTg8/E9uj6ENdYOWO3K458wQOUAYRDjE2GdPqLmBcFN4CoqSp58EwB4wKqHK619 nOpCnDPe9BNRU1QNQANGJGwbnfe0vRGnIPnkEZmmbPkcUkf3dw19Z18BKJdvY3mDfKG3 0/dUUIggikTXClP+ebH4opmJ5YMUSGxWxDwzMN2rZA8nLJrx868imJKcBsl4w5gIF2qB v4fOMbQNXnTtPBclqLj73AHDjbj5EjbOQ1kv+D6Utaw1KBXgQbf8gOlu5mz9rlHyU9FL 3y5HK1A8ebbDQddx4BIRCQ5pzI4KXuAVzUHCFtBVsQR6OnTog6zBsONzAhT2cQRaTwTO pD2A== X-Gm-Message-State: AOJu0YxOJEKDzx084tfTWXX+xcqMj6pO0ccI3ucT/U8b0lejBRz+Uyzj IFoQBt2ePORCG228oa5H9JuMK3HzE9jJD9pdi5CgX9YweG1HliZhZGmrnXKOdWi76NdEsVrN6rq IyJzk8GB6IIkXE4VTJcSpi8ikeQnif2de9w== X-Gm-Gg: AeBDiev25jOdbFiq0Z7tdK4vOBCVfsWVhi4wFXJMXs0q2CrdqTVN+jv7HIywK0+XBWH abrl7jXU5ka8Z0qynOwgbaVy+iizYwgyYPFnzcZPzfSdLPgzrZugdB52fx9DP9vR7dWUdoz3zWD Yx/+xs87A11MIThjQVyOMQImKh42wHa3VO/RdyuRAtPLo9hKpTUJNJ/yxItBqd0S8GmZx+NNxPI m+kvFfDLjrJbWYAYCXNLAaVFBDs43tdzMVjkETgtCT2vPdBz6qUsfb7F7kRsXl+MJ/mme4CyMLM YecLGVF0TFHkatOv7047kM3GPQtiDEv+OTc= X-Received: by 2002:a17:907:928c:b0:b98:528b:8461 with SMTP id a640c23a62f3a-b9d727ac38fmr1031843366b.52.1776178335288; Tue, 14 Apr 2026 07:52:15 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: OMPRAKASH SAHU Date: Tue, 14 Apr 2026 20:22:03 +0530 X-Gm-Features: AQROBzBiZ8S35Ci89J4WYFtCo9n0N8joiJA8A3LZxUHE5SPtjjavADYMQdyCDU4 Message-ID: Subject: =?UTF-8?B?UmU6IHBlcmZvcm1hbmNlwqBpc3N1ZcKgd2l0aMKgYcKgUEwvcGdTUUzCoGZ1bmN0aW9u?= =?UTF-8?B?wqA=?= To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000bb90ad064f6cbec3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bb90ad064f6cbec3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Ron Johnson, Thanks for the reply. >>>The call to LOWER() is utterly meaningless when checking for nullity: LOWER(pdra."OriginalDocumentNumber") IS NOT NULL >>> yes you are absolutely right on this statement but as I said previously I just add this lower() to somehow replan the query execution and if the lower is there then removing the same works and if not there and still performing poorly then adding lower in where clause work like magic, still i don't know why and I have tried it so many times. Just like somewhere adding "Int+0" to replan the query execution Seeing the query plan just by running explain is hectic in case of function having more than 4k lines of code, can you please help me if there is any tool to check the whole execution plan for a function just like we get for a select query. Regards, OM On Tue, 14 Apr 2026, 19:50 Ron Johnson, wrote: > On Tue, Apr 14, 2026 at 9:56=E2=80=AFAM OMPRAKASH SAHU > wrote: > >> Hi Team, >> >> Greetings! >> >> >> We are facing an intermittent performance issue with a PL/pgSQL function= and would appreciate your guidance. >> >> **Environment:** >> >> * PostgreSQL version: 16.9 >> * OS: Ubuntu 22 >> * Table size: ~80 million rows (~45 GB) >> * CPU/Memory/IO normal >> >> **Problem Description:** >> We have multiple PL/pgSQL functions. One of the functions is executed ar= ound 20 times per day >> and on peak days more than 400 >> times. Under normal conditions, it completes in 2=E2=80=934 minutes depe= nding on input parameters. >> >> >> However, intermittently (especially during peak load), the same function= execution takes more than 35 minutes. We typically cancel the execution at= that point. >> >> **Observation:** >> >> We noticed that making a very minor change in the function (for example,= adding/removing a `LOWER()` condition in the WHERE clause causes the funct= ion to execute again in the normal 2=E2=80=934 minutes. >> >> Example: >> >> Original condition: >> RAISE NOTICE 'Step6 : >> %', clock_timestamp()::timestamp without time zone; >> DROP TABLE IF EXISTS "TempOriginalDocument"; >> CREATE TEMP TABLE "TempOriginalDocument" AS >> SELECT >> pdra."Id" "PurchaseDocumentRecoId" >> FROM >> "Temp2BUnReconciledIds" tid >> INNER JOIN report."DocumentD" r_pd ON tid."Id" =3D r_pd."Id" >> INNER JOIN report."DocumentD" pdra >> >> ON LOWER(pdra."OriginalDocumentNumber") =3D LOWER(r_pd."DocumentNumber") >> . >> . >> WHERE >> LOWER(pdra."OriginalDocumentNumber") IS NOT NULL >> AND pdra."OriginalDocumentDate" IS NOT NULL; >> >> replace with...... >> >> RAISE NOTICE 'Step6 : >> %', clock_timestamp()::timestamp without time zone; >> DROP TABLE IF EXISTS "TempOriginalDocument"; >> CREATE TEMP TABLE "TempOriginalDocument" AS >> SELECT >> pdra."Id" "PurchaseDocumentRecoId" >> FROM >> "Temp2BUnReconciledIds" tid >> INNER JOIN report."DocumentD" r_pd ON tid."Id" =3D r_pd."Id" >> INNER JOIN report."DocumentD" pdra >> >> ON LOWER(pdra."OriginalDocumentNumber") =3D LOWER(r_pd."DocumentNumber") >> . >> . >> WHERE >> pdra."OriginalDocumentNumber" IS NOT NULL >> AND pdra."OriginalDocumentDate" IS NOT NULL; >> >> Or vice versa. >> >> After such a change, performance returns to normal. >> FYI-- these problematic functions will be executing in a sequnce one at >> time >> >> **Additional Findings:** >> >> >> * We added `RAISE NOTICE` statements and observed that the delay does no= t occur at a fixed step=E2=80=94it can happen at different parts of the fun= ction at step 6,8, 62 etc. >> * Running `VACUUM ANALYZE` on the main table did not resolve the issue. >> * Seeing the postgresql logs I have observed that catlogue tables are >> being vacuumed too frequently >> >> is it query plan caching or parameter-sensitive planning ? >> >> Any guidance on how to diagnose or resolve this issue would be greatly a= ppreciated. >> > > The call to LOWER() is utterly meaningless when checking for nullity: > *LOWER*(pdra."OriginalDocumentNumber") IS NOT NULL > > Run EXPLAIN on the two queries. I bet you see a sequential scan on the > original, and index usage on the modified query. > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --000000000000bb90ad064f6cbec3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Ron Johnson,

Thanks for the reply.

>>>The call to LOWER() is utterly meaningless when checking for = nullity:
=C2=A0LOWER(pdra."OriginalDocumentNumb= er") IS NOT NULL >>> yes you are absolutely right on this sta= tement but as I said previously I just add this lower() to somehow replan t= he query execution and if the lower is there then removing the same works a= nd if not there and still performing poorly then adding lower in where clau= se work like magic, still i don't know why=C2=A0 and I have tried it so= many times.
Just like somewhere adding "Int+0&= quot; to replan the query execution=C2=A0

=

Seeing the query plan just by= running explain is hectic in case of function having more than 4k lines of= code, can you please help me if=C2=A0 there is any tool to check the whole= execution plan for a function just like we get for a select query.

Regards,
OM

On Tue, 14 Apr 2026, 19:50 Ron Johnson, <= ronljohnsonjr@gmail.com> = wrote:
On Tue, Apr 14, 2026 at 9:56=E2=80=AFAM OMPRAKASH SAHU <sahuop21= 21@gmail.com> wrote:
Hi=C2=A0Team,

Greetings!
We=C2=A0are=C2=A0facing=C2=A0an=C2=A0intermittent= =C2=A0performance=C2=A0issue=C2=A0with=C2=A0a=C2=A0PL/pgSQL=C2=A0function= =C2=A0and=C2=A0would=C2=A0appreciate=C2=A0your=C2=A0guidance.

**Environment:**

*=C2=A0PostgreSQL=C2=A0version:=C2=A016.9
*=C2=A0OS:=C2=A0Ubuntu=C2=A022
= *=C2=A0Table=C2=A0size:=C2=A0~80=C2=A0million=C2=A0rows=C2=A0(~45=C2=A0GB)<= /div>
*=C2=A0CPU/Memory/IO=C2=A0normal

**Problem=C2=A0Description:**
We=C2=A0have=C2=A0multiple=C2=A0PL/pgSQL=C2=A0functions.=C2=A0One= =C2=A0of=C2=A0the=C2=A0functions=C2=A0is=C2=A0executed=C2=A0around=C2=A020= =C2=A0times=C2=A0per=C2=A0day and on peak days more than 400 times.=C2=A0Un= der=C2=A0normal=C2=A0conditions,=C2=A0it=C2=A0completes=C2=A0in=C2=A02=E2= =80=934=C2=A0minutes=C2=A0depending=C2=A0on=C2=A0input=C2=A0parameters.

However,=C2=A0intermittentl= y=C2=A0(especially=C2=A0during=C2=A0peak=C2=A0load),=C2=A0the=C2=A0same=C2= =A0function=C2=A0execution=C2=A0takes=C2=A0more=C2=A0than=C2=A035=C2=A0minu= tes.=C2=A0We=C2=A0typically=C2=A0cancel=C2=A0the=C2=A0execution=C2=A0at=C2= =A0that=C2=A0point.

**Ob= servation:**
We=C2=A0noticed=C2=A0that=C2=A0making= =C2=A0a=C2=A0very=C2=A0minor=C2=A0change=C2=A0in=C2=A0the=C2=A0function=C2= =A0(for=C2=A0example,=C2=A0adding/removing=C2=A0a=C2=A0`LOWER()`=C2=A0condi= tion=C2=A0in=C2=A0the=C2=A0WHERE=C2=A0clause=C2=A0causes=C2=A0the=C2=A0func= tion=C2=A0to=C2=A0execute=C2=A0again=C2=A0in=C2=A0the=C2=A0normal=C2=A02=E2= =80=934=C2=A0minutes.

Ex= ample:

Original=C2=A0con= dition:
=C2=A0RAISE=C2=A0NOTICE=C2=A0'Step6 : %&= #39;,=C2=A0clock_timestamp()::timestamp=C2=A0without=C2=A0time=C2=A0zone;= =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=C2=A0 DROP=C2=A0T= ABLE=C2=A0IF=C2=A0EXISTS=C2=A0"TempOriginalDocument";
=C2=A0 CREATE=C2=A0TEMP=C2=A0TABLE=C2=A0"TempOriginalDocum= ent"=C2=A0AS
=C2=A0 SELECT=C2=A0
=C2=A0 =C2=A0pdra."Id"=C2=A0"PurchaseDocumentRecoI= d"
=C2=A0 FROM=C2=A0
=C2= =A0 =C2=A0"Temp2BUnReconciledIds"=C2=A0tid
=C2=A0 =C2=A0INNER=C2=A0JOIN=C2=A0report."DocumentD"=C2=A0r_pd= =C2=A0 ON=C2=A0tid."Id"=C2=A0=3D=C2=A0r_pd."Id"
=C2=A0 =C2=A0INNER=C2=A0JOIN=C2=A0report."DocumentD&qu= ot;=C2=A0pdra=C2=A0=C2=A0
=C2=A0 =C2=A0 ON=C2=A0LOWE= R(pdra."OriginalDocumentNumber")=C2=A0=3D=C2=A0LOWER(r_pd."D= ocumentNumber")=C2=A0
=C2=A0 =C2=A0 .
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0.
=C2=A0 =C2=A0WHERE
=C2=A0 =C2=A0 LOWER(p= dra."OriginalDocumentNumber")=C2=A0IS=C2=A0NOT NULL
=C2=A0 =C2=A0 AND=C2=A0pdra."OriginalDocumentDate"=C2= =A0IS=C2=A0NOT NULL;
=C2=A0 =C2=A0=C2=A0
replace=C2=A0with......=C2=A0 =C2=A0=C2=A0
=C2=A0
=C2=A0RAISE=C2=A0NOTICE=C2=A0'Step6 : = %',=C2=A0clock_timestamp()::timestamp=C2=A0without=C2=A0time=C2=A0zone;= =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0
=C2=A0 DROP=C2=A0T= ABLE=C2=A0IF=C2=A0EXISTS=C2=A0"TempOriginalDocument";
=C2=A0 CREATE=C2=A0TEMP=C2=A0TABLE=C2=A0"TempOriginalDocum= ent"=C2=A0AS
=C2=A0 SELECT=C2=A0
=C2=A0 =C2=A0pdra."Id"=C2=A0"PurchaseDocumentRecoI= d"
=C2=A0 FROM=C2=A0
=C2= =A0 =C2=A0"Temp2BUnReconciledIds"=C2=A0tid
=C2=A0 =C2=A0INNER=C2=A0JOIN=C2=A0report."DocumentD"=C2=A0r_pd= =C2=A0 ON=C2=A0tid."Id"=C2=A0=3D=C2=A0r_pd."Id"
=C2=A0 =C2=A0INNER=C2=A0JOIN=C2=A0report."DocumentD&qu= ot;=C2=A0pdra=C2=A0=C2=A0
=C2=A0 =C2=A0 ON=C2=A0LOWE= R(pdra."OriginalDocumentNumber")=C2=A0=3D=C2=A0LOWER(r_pd."D= ocumentNumber")=C2=A0
=C2=A0 =C2=A0 .
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0.
=C2=A0 =C2=A0WHERE
=C2=A0 =C2=A0 pdra.&q= uot;OriginalDocumentNumber"=C2=A0IS=C2=A0NOT NULL
=C2=A0 =C2=A0 AND=C2=A0pdra."OriginalDocumentDate"=C2=A0IS=C2= =A0NOT NULL;

Or=C2=A0vic= e=C2=A0versa.

After=C2= =A0such=C2=A0a=C2=A0change,=C2=A0performance=C2=A0returns=C2=A0to=C2=A0norm= al.
FYI-- these problematic functions will be execut= ing in a sequnce one at time

**Additional=C2=A0Findings:**

*=C2=A0We=C2=A0added=C2=A0`RAISE=C2=A0NOTICE`=C2=A0statements=C2= =A0and=C2=A0observed=C2=A0that=C2=A0the=C2=A0delay=C2=A0does=C2=A0not=C2=A0= occur=C2=A0at=C2=A0a=C2=A0fixed=C2=A0step=E2=80=94it=C2=A0can=C2=A0happen= =C2=A0at=C2=A0different=C2=A0parts=C2=A0of=C2=A0the=C2=A0function=C2=A0at= =C2=A0step=C2=A06,8,=C2=A062=C2=A0etc.
*=C2=A0Runnin= g=C2=A0`VACUUM=C2=A0ANALYZE`=C2=A0on=C2=A0the=C2=A0main=C2=A0table=C2=A0did= =C2=A0not=C2=A0resolve=C2=A0the=C2=A0issue.
* Seeing= the postgresql logs I have observed that catlogue tables are being vacuume= d too frequently=C2=A0

i= s=C2=A0it=C2=A0query=C2=A0plan=C2=A0caching=C2=A0or=C2=A0parameter-sensitiv= e=C2=A0planning=C2=A0?
Any=C2=A0guidance=C2=A0on=C2= =A0how=C2=A0to=C2=A0diagnose=C2=A0or=C2=A0resolve=C2=A0this=C2=A0issue=C2= =A0would=C2=A0be=C2=A0greatly=C2=A0appreciated.

The call to LOWER() is utterly meaningles= s when checking for nullity:
=C2=A0LOWER(pdra."Origin= alDocumentNumber")=C2=A0IS=C2=A0NOT NULL

Run = EXPLAIN on the two queries.=C2=A0 I bet you see a sequential scan on the or= iginal, and index usage on the modified query.

--
Death to <Redacted>, and butter sauce.<= div>Don't boil me, I'm still alive.
<Redacted> l= obster!
--000000000000bb90ad064f6cbec3--