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 1wCg7A-002DS0-0R for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 15:55:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wCg76-00CcOx-1g for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 15:55:45 +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.96) (envelope-from ) id 1wCg76-00CcOp-0L for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 15:55:45 +0000 Received: from mail-oa1-x2b.google.com ([2001:4860:4864:20::2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wCg75-0000000133V-065S for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 15:55:44 +0000 Received: by mail-oa1-x2b.google.com with SMTP id 586e51a60fabf-40ea611d1a4so2373786fac.2 for ; Tue, 14 Apr 2026 08:55:42 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776182140; cv=none; d=google.com; s=arc-20240605; b=fev+84lj2o4sqtdGuOS/fEb7GTxkJj7bQQ9kTUFEYbECAw9Eo48c4tYjNWa/oRAf3O gv0J28KOI9ZGLQSrxAt55v3yu9nB56tMM3bVe+T2EjdvzCMo82rjmE1kDsW2+UlUqYyU cqlpttECwmZyaVURxwXm28Cn4VY2U11h4CFeKfGcM+jPLoKmqjMK2EDufxLHbUu2ZTcv B2dQc/va93j6hd2vQgDmohEHAZwS2QINkJAgrlycwjWMVE5nZHxGcON4Fg0Mg83trahT qxjhKMKTmRxMTMt7TX2/1a6kq/TgmiKGAGpbQ1IurulefsGTAQ3TfVcfapj9MG/bFvBw z/8Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=Cu/f5Q/mtmHTis/ivqAv1BiNhS/SxlByaHNoOGxuoRw=; fh=druxZHa2fk4e6MLibibygn9AWWgeaAPo4m8Gpo2MBXU=; b=QWjCNWLRKLr1m6o2reaQrylkdBVOemlc6JJct+KYUlekHS1w9SbciA4E9RL89JHf2b hdcx+9Z1WWZSwo+GvAlgWSPFJYFmThBhGfGrFBHnXnjrx1C/eOl0hhwoVjSgd+u0JUrD PbQR2gP4cFnRBAmyBGKhMdSWmuFJHNS1f6g88M+hXLxRv2RozayTqlfOajUbISxeImtw Oa5c4jREUQEYiEK6z0SbC6QggxPhTVLgoU82dHoBxexXgjyq56QJT5yCJ8lIs5sNi7ID P4rdNsAnobZgOBCyqXyorD9mCfrZTyOoeHJiNLtCgmxlHr+ONDqgLYSEbPFipKbpebc4 oGPg==; 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=1776182140; x=1776786940; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Cu/f5Q/mtmHTis/ivqAv1BiNhS/SxlByaHNoOGxuoRw=; b=KQC7EgOIdN6DWDM7q76aQlyJScFtpuw2mjeLmDgFHOameHtmgiQRf4HnLAOCgM/DYT QCvTx7uRfgCj7AplBfQYX5uuo2pnpBj03Zi0+FqqvEa34Ljp79E379gqhhXpU08JMnY3 ng+XS9ICs56gu56T3v1ToLs/tpsRWz5BE9pC4tV1KGWDeOezDOlAvk5JBKfsplZtpDoc A0B+0b1UP0R4WTuE7zTUCkC/6EQOCi6Ee3IwFgRi1FfkoGvPe2w4IetgRxUFp0pMk6Ih cb4XzMUMfxcHtz5S/pmW3FGcI8D+1uyk+S7jbyqKfGEUG5WH2UJ7R8/MR5t3HvPDu00P 6SWA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776182140; x=1776786940; h=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=Cu/f5Q/mtmHTis/ivqAv1BiNhS/SxlByaHNoOGxuoRw=; b=jb/uJr4Cm9dc0dspJQm1wmPBdxmRmPh7j3Qe9R6cp+e3tQOHCaQpplYFDohA6wNqhA ay0kSfipswJ/svdYLO7m0iF0C0lNslBsbTKdfqXzbCAUPKGAQpV7dogcLhLkNPwXy452 ENUjHw5V+UIfp80j4gGo8lVrHc3+bcKxSeC4HhpDl1ZADvY9/axGFNNw1hgOOW2yuYAw 7pqZrDWeq5obvIe8sgT+48CuLLHLmKO5XO5A4o0AgAjNiUzLmaDP0RrNkDsY3MXYU3g9 iBAM331TY4jAAoORJu0fJUDC39oVv9JEas5Oe/cvMkmu1SLkbj7y6gFS/6d459nK3+nT cwgw== X-Gm-Message-State: AOJu0Yzx8mzBjdYtyxQbbMtuZK4OKsSXiO+bktZcOPQ27eReodMXfTNt BZ+46rL9aAFSmBp5nyOLt4YwSDaEoerjINkz7b7+InLLbG/xI7G1USIIoDRBVcsbvzsfPtkedr0 jBaD7GIGKB7gWxYIIDyqCPgiB91tAOmvV7pp7 X-Gm-Gg: AeBDievnPvgFeMSp2mKmrd7XsvFpB/5NeS+6mjnTzlNbjUeCVTzq60WYApAAkUcFQPm 1vsTWPu1utSomTxyTy6qjaq2FHBM/oGn+awD1dZcLtEzMSeAzKfs0QsI5TW7SCKpz2t+4kPDZXa O+E6rNvWNqcg9GmHZzczhT4u37swj6Mq8yNHiTU870l/tQDZ0x4QA4fgPOrER2m9kLPdYaMMyd3 PCGt4jJRpwxftgkUEuXPnygQ2aCYgaWJ65W44ktQwajqS+Y5eCaiOg++ZQeTMGPKjojU1ixnaGB 5dK9mxKn X-Received: by 2002:a05:6870:e6c4:b0:417:33a7:1032 with SMTP id 586e51a60fabf-423e100b6a8mr11107013fac.25.1776182140350; Tue, 14 Apr 2026 08:55:40 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 14 Apr 2026 11:55:27 -0400 X-Gm-Features: AQROBzDd7HVJk_ifM9SzxAkS_jBza_FRI-LH5KUL4tUJSDB-urLmLVxxZT_ktBk Message-ID: Subject: =?UTF-8?B?UmU6IHBlcmZvcm1hbmNlwqBpc3N1ZcKgd2l0aMKgYcKgUEwvcGdTUUzCoGZ1bmN0aW9u?= =?UTF-8?B?wqA=?= To: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000088341a064f6da17e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000088341a064f6da17e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 1. Ok, I missed the "*adding*/removing a `LOWER()`" part. 2. Is that SELECT statement 4000+ lines long, or is there a lot of other stuff, too? 3. Is the SELECT statement called in a loop? Because I've seen where that scenario causes the query planner-optimizer to switch to a generic plan after about 5 iterations. Executing "set plan_cache_mode =3D force_custom_plan" just before the stored procedure fixed that problem. On Tue, Apr 14, 2026 at 10:52=E2=80=AFAM OMPRAKASH SAHU wrote: > 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 th= is > 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 functio= n 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 a= round 20 times per day >>> and on peak days more than 400 >>> times. Under normal conditions, it completes in 2=E2=80=934 minutes dep= ending on input parameters. >>> >>> >>> However, intermittently (especially during peak load), the same functio= n execution takes more than 35 minutes. We typically cancel the execution a= t 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 func= tion 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 n= ot occur at a fixed step=E2=80=94it can happen at different parts of the fu= nction 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 = appreciated. >>> >> >> 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! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000088341a064f6da17e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
1. Ok, I missed the "adding/removing=C2= =A0a=C2=A0`LOWER()`" part.
2. Is that SELECT statement 4000+= lines long, or is there a lot of other stuff, too?
3. Is the SEL= ECT statement called in a loop?=C2=A0 Because I've seen where that scen= ario causes the query planner-optimizer to switch to a generic plan after a= bout 5 iterations.=C2=A0 Executing "set plan_cache_mode =3D force_cust= om_plan" just before the stored procedure fixed that problem.

On Tue, Apr 14, 2026 at 10:52=E2=80=AFAM OMPRA= KASH SAHU <sahuop2121@gmail.com<= /a>> wrote:
Hi Ron Johnso= n,

Thanks for the reply.
=

>>>The call to LOWER= () is utterly meaningless when checking for nullity:
=C2=A0LOWER(pdra."OriginalDocumentNumber") IS NOT NULL >>&= gt; 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 l= ower is there then removing the same works and if not there and still perfo= rming poorly then adding lower in where clause 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" to replan the query execu= tion=C2=A0


Seeing the query plan just by running explain is hectic in c= ase of function having more than 4k lines of code, can you please help me i= f=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 <sahuop2121@gmail.com= > wrote:
Hi=C2=A0Team,
Greetings!

We=C2=A0are=C2=A0facing=C2=A0an=C2=A0intermittent=C2=A0performa= nce=C2=A0issue=C2=A0with=C2=A0a=C2=A0PL/pgSQL=C2=A0function=C2=A0and=C2=A0w= ould=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)
*=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=A0Under=C2=A0no= rmal=C2=A0conditions,=C2=A0it=C2=A0completes=C2=A0in=C2=A02=E2=80=934=C2=A0= minutes=C2=A0depending=C2=A0on=C2=A0input=C2=A0parameters.

However,=C2=A0intermittently=C2=A0(espec= ially=C2=A0during=C2=A0peak=C2=A0load),=C2=A0the=C2=A0same=C2=A0function=C2= =A0execution=C2=A0takes=C2=A0more=C2=A0than=C2=A035=C2=A0minutes.=C2=A0We= =C2=A0typically=C2=A0cancel=C2=A0the=C2=A0execution=C2=A0at=C2=A0that=C2=A0= point.

**Observation:**<= /div>
We=C2=A0noticed=C2=A0that=C2=A0making=C2=A0a=C2=A0ve= ry=C2=A0minor=C2=A0change=C2=A0in=C2=A0the=C2=A0function=C2=A0(for=C2=A0exa= mple,=C2=A0adding/removing=C2=A0a=C2=A0`LOWER()`=C2=A0condition=C2=A0in=C2= =A0the=C2=A0WHERE=C2=A0clause=C2=A0causes=C2=A0the=C2=A0function=C2=A0to=C2= =A0execute=C2=A0again=C2=A0in=C2=A0the=C2=A0normal=C2=A02=E2=80=934=C2=A0mi= nutes.

Example:

Original=C2=A0condition:
=C2=A0RAISE=C2=A0NOTICE=C2=A0'Step6 : %',=C2=A0cloc= k_timestamp()::timestamp=C2=A0without=C2=A0time=C2=A0zone;=C2=A0 =C2=A0 =C2= =A0 =C2=A0=C2=A0
=C2=A0 DROP=C2=A0TABLE=C2=A0IF=C2= =A0EXISTS=C2=A0"TempOriginalDocument";
=C2= =A0 CREATE=C2=A0TEMP=C2=A0TABLE=C2=A0"TempOriginalDocument"=C2=A0= AS
=C2=A0 SELECT=C2=A0
=C2=A0= =C2=A0pdra."Id"=C2=A0"PurchaseDocumentRecoId"
=C2=A0 FROM=C2=A0
=C2=A0 =C2=A0"= Temp2BUnReconciledIds"=C2=A0tid
=C2=A0 =C2=A0IN= NER=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"=C2=A0pdra=C2= =A0=C2=A0
=C2=A0 =C2=A0 ON=C2=A0LOWER(pdra."Ori= ginalDocumentNumber")=C2=A0=3D=C2=A0LOWER(r_pd."DocumentNumber&qu= ot;)=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(pdra."Or= iginalDocumentNumber")=C2=A0IS=C2=A0NOT NULL
= =C2=A0 =C2=A0 AND=C2=A0pdra."OriginalDocumentDate"=C2=A0IS=C2=A0N= OT 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=A0TABLE=C2=A0= IF=C2=A0EXISTS=C2=A0"TempOriginalDocument";
=C2=A0 CREATE=C2=A0TEMP=C2=A0TABLE=C2=A0"TempOriginalDocument"= =C2=A0AS
=C2=A0 SELECT=C2=A0
= =C2=A0 =C2=A0pdra."Id"=C2=A0"PurchaseDocumentRecoId"
=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"=C2=A0= pdra=C2=A0=C2=A0
=C2=A0 =C2=A0 ON=C2=A0LOWER(pdra.&q= uot;OriginalDocumentNumber")=C2=A0=3D=C2=A0LOWER(r_pd."DocumentNu= mber")=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."Origi= nalDocumentNumber"=C2=A0IS=C2=A0NOT NULL
=C2=A0= =C2=A0 AND=C2=A0pdra."OriginalDocumentDate"=C2=A0IS=C2=A0NOT NUL= L;

Or=C2=A0vice=C2=A0ver= sa.

After=C2=A0such=C2= =A0a=C2=A0change,=C2=A0performance=C2=A0returns=C2=A0to=C2=A0normal.
<= div dir=3D"auto">FYI-- these problematic functions will be executing in a s= equnce one at time

**Add= itional=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=A0occur=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=A0Running=C2=A0`VA= CUUM=C2=A0ANALYZE`=C2=A0on=C2=A0the=C2=A0main=C2=A0table=C2=A0did=C2=A0not= =C2=A0resolve=C2=A0the=C2=A0issue.
* Seeing the post= gresql logs I have observed that catlogue tables are being vacuumed too fre= quently=C2=A0

is=C2=A0it= =C2=A0query=C2=A0plan=C2=A0caching=C2=A0or=C2=A0parameter-sensitive=C2=A0pl= anning=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 meaningless when chec= king for nullity:
=C2=A0LOWER(pdra."OriginalDocumentN= umber")=C2=A0IS=C2=A0NOT NULL

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

--
Death to <Redacted>, and butter sauce.
Don'= ;t boil me, I'm still alive.
<Redacted> lobster!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--00000000000088341a064f6da17e--