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 1wCeFr-002Brj-2R for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 13:56:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wCeFq-00C7Eo-03 for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 13:56:38 +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 1wCeFp-00C7Eg-1t for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 13:56:38 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wCeFo-0000000128P-0yfg for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 13:56:37 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-671a2f4fd28so2705268a12.3 for ; Tue, 14 Apr 2026 06:56:35 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776174995; cv=none; d=google.com; s=arc-20240605; b=OKDtm5Ku3wHNXaPEGStVBZ5+i/XryL4u/uD1U7pKtcFbrotfGurmE2LC/xxiYCvCGf GNDgUgvWuukGyYWDru9WsmBZQ5uZiF/BXJnrQe0KxbL5Sbo3iwZC1fbNlyuMXZCqxJcu qowuuAjzMFpIHZoH1wYCy+PLgejKu8xytt01XPI99THlqvKL6AZbyRnTjK+MXcrpWBkG +DD1EvBLYnmENuz3SBbfDACGkSVEwfNyaOFWWj7hsiukY5CGpIuto7TLkBQ1fA/v5HbN xJJcFZGfxn4VdqL7zo3zMeWNEoNp5iehvKKL9flNaSoqSpgohFb2tzOWXezlAvfQDAah cdew== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:mime-version:dkim-signature; bh=5kZHSsKYhzTo7D76In4TYNCmoYPZOCMHrNS1GCajXiA=; fh=2TYDFu6n7J5QCgQuKC9CHnL0/OX+GX+HLe30lvVBdcM=; b=AdcXLzD8pZW479tBcppIbP2lKBCQ5t/pH6211g/yLoKTFr2B6qvJWj/n78dhjoEmRV 2CARCyX8TNSxuRlCO0L1tSn1MKW1Ce5NfG5DtQ52DZR+FcPNrGQXP4G19YKa+Pfz+yuI WrLSWieSuLkeLwn54N25yl8i4luQ+JiYywMQz+YdZ9gLWImbeWBAPcsJzjefSKgGT05S BtfCFjMEW1SCvJEaO3FXo+ARtSm52WE/bFEf8qlDTC5UOn41FWJDT9heoUxTVzhUP/Es ecb7YmBfBpxMXDOMxbk+1h1U3SOKlf2WFfJdbR1pKYUA6n6U5Lh1OUXL8PGOh6I+sKrz BmMg==; 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=1776174995; x=1776779795; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=5kZHSsKYhzTo7D76In4TYNCmoYPZOCMHrNS1GCajXiA=; b=o1eB00NlFqdNt5KI9FpW9cbIC9Nk88lBxI0g4XZIfZbEbpIg2x4sfCIMC6F7qafoWA MVhmtJOepXTuDMummBp+EV486ieYIszIgr5+rGJMXdQ7jLZcHbsnU9d1IbOxEi4lYzC1 QhBoEfmp20p4C0um7cqFQuRifaBnteWdSlFCpiOJ8KUFqCI454Yv9VJT1fAQJYT7rViU DcTqAAM4ybQ/N4sHaAIBeP0csFysc6rCHy0zOmfX54c4t/YMlUUTLmP1oyZTj4OaSfbu vcrLDgeQAmxOu382Ht44j3ktIvaENdWQ8nxi49qNVryGxUf82qKqQBoq2O9fgtAskzpS jBwg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776174995; x=1776779795; h=to:subject:message-id:date:from:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=5kZHSsKYhzTo7D76In4TYNCmoYPZOCMHrNS1GCajXiA=; b=JCUDctzmtfWL63siLAI0gE0u2648hxPkEOI4y5WKqE6BbFaqJDBXMhceWH3p3P1TFW 72fFO5taaCn/wZoGAEOA6Xmco1v3Luta+TQA8xJtoEc12iNbditPknQFnH6F4Qbwy3jb rTAAyMsJucewNTJskBbZCh2q/HbtKxmFtmIpWry+s4CCf0P9IXWvXdt5Sk8JSHh4W7g4 KfUTV3sluMGoD/zYkjruXt52Mx5K3ID/ggvd47kur9RcOJ6EolFiIYl4N4sVDATTiV9y WgIbrAOfMlYnuMAxKdEp4jdKUHVPkcXZ76Krypw6lpw4C5z8xBKBuL8fnIMuauJH1XXU /mAw== X-Gm-Message-State: AOJu0YxgzMrvCfnusfV5g1PTXK1CYAsZBrReJRAHLFwWx5xHTOaWhSs1 76QuRWeA0DxhTu33qfH+qLhCTKYwRBoMFJM3tcw8qB8mUQkyvqmfDKvzwBdutBcrWMyG2V0OzwM 9GkS0DDTgPp37DvNmzd07ehtUDrxGc7omTA== X-Gm-Gg: AeBDiesLfumQMOestxtqZvNn+AtXbQ2inkCl31YplovAtnRPyXjwS5sPLj9Uxy3YvAA J37X2IHfJ3ziLKpx3Cjxg85QF2WFxml/+yTwuKOX2Sc/0Nvx2EYkdmfUs55vX7VA2cflgAD/SWK j7CjbeF9xmxBdVJBr66MSpq4q2TKLCnl5wHnQkHfMGOTyk+yOE76AvqkkTMwEL2w3czn5KliFZm /CidHqahCNytP9Eor1jugTYsrrqSwKBIDzZ0KxTySR5DfMZfkDyHDDJ5jD/qSDJpoRrPxdpFDXO BO2pY/L5STE9RbKZVsHoJ6I3 X-Received: by 2002:a17:907:8b98:b0:b9c:a70c:d30b with SMTP id a640c23a62f3a-b9d727931camr896725366b.19.1776174994303; Tue, 14 Apr 2026 06:56:34 -0700 (PDT) MIME-Version: 1.0 From: OMPRAKASH SAHU Date: Tue, 14 Apr 2026 19:26:22 +0530 X-Gm-Features: AQROBzBITLbiUyAI-e6zcZZk1wuMUN93w1uwbRoZzkdS0QBGC0T6nWLBXxv2_-A Message-ID: Subject: =?UTF-8?B?cGVyZm9ybWFuY2XCoGlzc3VlwqB3aXRowqBhwqBQTC9wZ1NRTMKgZnVuY3Rpb27CoA==?= To: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000982f0e064f6bf708" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000982f0e064f6bf708 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 around 20 times per day and on peak days more than 400 times. Under normal conditions, it completes in 2=E2=80=934 minutes dependi= ng 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 function 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 tim= e **Additional Findings:** * We added `RAISE NOTICE` statements and observed that the delay does not occur at a fixed step=E2=80=94it can happen at different parts of the function 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. Thanks in advance! Regards, OM --000000000000982f0e064f6bf708 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Team,

Greetings!

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

*= *Environment:**

*=C2=A0P= ostgreSQL=C2=A0version:=C2=A016.9
*=C2=A0OS:=C2=A0Ub= untu=C2=A022
*=C2=A0Table=C2=A0size:=C2=A0~80=C2=A0m= illion=C2=A0rows=C2=A0(~45=C2=A0GB)
*=C2=A0CPU/Memor= y/IO=C2=A0normal

**Probl= em=C2=A0Description:**
We=C2=A0have=C2=A0multiple=C2= =A0PL/pgSQL=C2=A0functions.=C2=A0One=C2=A0of=C2=A0the=C2=A0functions=C2=A0i= s=C2=A0executed=C2=A0around=C2=A020=C2=A0times=C2=A0per=C2=A0day and on pea= k days more than 400 times.=C2=A0Under=C2=A0normal=C2=A0conditions,=C2=A0it= =C2=A0completes=C2=A0in=C2=A02=E2=80=934=C2=A0minutes=C2=A0depending=C2=A0o= n=C2=A0input=C2=A0parameters.

However,=C2=A0intermittently=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=A0minutes.=C2=A0We=C2=A0typically=C2=A0cancel= =C2=A0the=C2=A0execution=C2=A0at=C2=A0that=C2=A0point.

**Observation:**
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/removin= g=C2=A0a=C2=A0`LOWER()`=C2=A0condition=C2=A0in=C2=A0the=C2=A0WHERE=C2=A0cla= use=C2=A0causes=C2=A0the=C2=A0function=C2=A0to=C2=A0execute=C2=A0again=C2= =A0in=C2=A0the=C2=A0normal=C2=A02=E2=80=934=C2=A0minutes.

Example:

Original=C2=A0condition:
=C2=A0RA= ISE=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=A0IF=C2=A0EXISTS=C2=A0"TempO= riginalDocument";
=C2=A0 CREATE=C2=A0TEMP=C2=A0= TABLE=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=A0JO= IN=C2=A0report."DocumentD"=C2=A0pdra=C2=A0=C2=A0
=C2=A0 =C2=A0 ON=C2=A0LOWER(pdra."OriginalDocumentNumber")= =C2=A0=3D=C2=A0LOWER(r_pd."DocumentNumber")=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."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()::time= stamp=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=A0AS
=C2=A0 SELECT=C2=A0
=C2=A0 =C2=A0pdra."Id= "=C2=A0"PurchaseDocumentRecoId"
=C2= =A0 FROM=C2=A0
=C2=A0 =C2=A0"Temp2BUnReconciled= Ids"=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=A0INNE= R=C2=A0JOIN=C2=A0report."DocumentD"=C2=A0pdra=C2=A0=C2=A0
=C2=A0 =C2=A0 ON=C2=A0LOWER(pdra."OriginalDocumentNumb= er")=C2=A0=3D=C2=A0LOWER(r_pd."DocumentNumber")=C2=A0
<= div dir=3D"auto">=C2=A0 =C2=A0 .
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0.
=C2=A0 =C2=A0WHERE<= /div>
=C2=A0 =C2=A0 pdra."OriginalDocumentNumber"= ;=C2=A0IS=C2=A0NOT NULL
=C2=A0 =C2=A0 AND=C2=A0pdra.= "OriginalDocumentDate"=C2=A0IS=C2=A0NOT NULL;

Or=C2=A0vice=C2=A0versa.

After=C2=A0such=C2=A0a=C2=A0change,=C2=A0p= erformance=C2=A0returns=C2=A0to=C2=A0normal.
FYI-- t= hese problematic functions will be executing 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=A0occur=C2=A0at=C2=A0a=C2=A0fixed= =C2=A0step=E2=80=94it=C2=A0can=C2=A0happen=C2=A0at=C2=A0different=C2=A0part= s=C2=A0of=C2=A0the=C2=A0function=C2=A0at=C2=A0step=C2=A06,8,=C2=A062=C2=A0e= tc.
*=C2=A0Running=C2=A0`VACUUM=C2=A0ANALYZE`=C2=A0o= n=C2=A0the=C2=A0main=C2=A0table=C2=A0did=C2=A0not=C2=A0resolve=C2=A0the=C2= =A0issue.
* Seeing the postgresql logs I have observ= ed that catlogue tables are being vacuumed too frequently=C2=A0

is=C2=A0it=C2=A0query=C2=A0plan=C2= =A0caching=C2=A0or=C2=A0parameter-sensitive=C2=A0planning=C2=A0?
Any=C2=A0guidance=C2=A0on=C2=A0how=C2=A0to=C2=A0diagnose=C2=A0= or=C2=A0resolve=C2=A0this=C2=A0issue=C2=A0would=C2=A0be=C2=A0greatly=C2=A0a= ppreciated.

Thanks=C2=A0= in=C2=A0advance!

Regards= ,
OM
--000000000000982f0e064f6bf708--