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 1wCecu-002CBL-2v for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 14:20:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wCecs-00CHhH-2q for pgsql-admin@arkaria.postgresql.org; Tue, 14 Apr 2026 14:20:27 +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 1wCecs-00CHh8-1c for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 14:20:27 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wCecr-000000012Lj-1QMH for pgsql-admin@lists.postgresql.org; Tue, 14 Apr 2026 14:20:27 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-4138136f02eso3483721fac.2 for ; Tue, 14 Apr 2026 07:20:25 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776176423; cv=none; d=google.com; s=arc-20240605; b=W8e6EfQ+tOywlyRQYfqGgR40+e9186z/AVD1OqOqXeasuqa6S1PjXb+gFxO14K+E5p AcCfLaRhW1fT9IpyZs42zJOnyQqGL3xbpPzpTHWfA4ZScNW0RWkp/+SXltVf8kMW+qV2 2Lr7jZ1GY/c2/Q4wkZ7gj4rsG6AfH20KyiVkd8xsCFN2n3oc1YmV82ihqANcSRX4QeLw 4vIKxG4tAy7Daqmb+Opvml2DjoIZyzaO2wMplnXcciHlUJFWl6flCw0CMQA6bhTIIbEZ UT3KqP77oyUXdEVo5LlgAIrRZBBnCRu7JodZH3iSTnvKXn1Dah/q68uESYQSYPxbdWcm fE7A== 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=kr4IAEX7HaE+HyrKJ4FaRsBAhXybm+15PVdEgbvimZI=; fh=druxZHa2fk4e6MLibibygn9AWWgeaAPo4m8Gpo2MBXU=; b=li66gZTKFykGt578hsWrGjQhwdEJVy4JZLfHmHUwW158omLsvSnddOtioHwqP8OFDH B5QJoMXnOrzinjuGCmw2+Y7QMfO21Rt36+Er173rPDN4Rw1NYEQkp5ooPL/oE6tXOM69 4gZFR/Rp5flE5M3uE5aWrfjTtRZp5cD8svbpns3fcfQq09N9qPiIXr1Vpd5ImD0BMsd3 8svXW2Zm7Dxjzo/Ug9V+76cKwTRPUw0mWrK8fiqmdogwO4Qo4V1loizc1Z5VfgF+H6/p AM+wZ1BTgOUN8M44vdgVm84kIY7RAGXKQxx1WhqCUKrzFMePZGukGmM+UTys9l4uBkIs yqrQ==; 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=1776176423; x=1776781223; 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=kr4IAEX7HaE+HyrKJ4FaRsBAhXybm+15PVdEgbvimZI=; b=Vy9DPTqUcI7Z4vdc5necF4P76vx0h1P/j+0LzH/g+odEavVnWkbuYVW2EeILmdYg9L dmQO94OjRcATyF/dwRoqKJOppn5Faoqum+VkrXzY0HrZku2WUl3cNOySNPgtBYD68ncd RTTbCNOJ0pOQB5EkTENQyjnRonkTfZAlqHpJrNjRFaSIVjqRwER7ZUauGCdHRkvyyyrx ix4OT+VFmIbH+/dqo46wIMzaGEzfkTv1umxQPkpij/t0QrqeS5G3efggt4+/VgGwlKmc pNE/9uWyKMnLw/uCq5i20QMrWEDCawksevKRl2D78b6HQY5YUFfu//+ZKHiZx98fNSiN 0xXA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776176423; x=1776781223; 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=kr4IAEX7HaE+HyrKJ4FaRsBAhXybm+15PVdEgbvimZI=; b=BBW4NXnwt9n1FNBdv/1G5bPB0xhGPgqinaWpX1hO2c3iP24YC//8PaQSjA90LbARBJ 5pahp70LOefx7DQJlVcRL1DjaF9HPJs1cRqSW2Ze0q5TirQiyyReO5ICI+LRXSWnKXHu IQwn354lzJJJVj5W4nQo5HrAf5QkBwTJmsrt6tWnsfrAEixI5VovakYFwt5pJtdOy2WM 20yg5uAwt2ZkcYwmZDydlGJFWJ0nFciuWgq2dnQrkquKwat8qKhzB7wcdLYc9wfVdVJC s3Bu5PcR/hfbHuQE4zehdEyXbuBVx39J0J9smrOeancoDWmoDgBh6mT82aj9aUKqvI3Q 3EvQ== X-Gm-Message-State: AOJu0YyktbDmhIvKVU1r6ezPMjKALMfGqfkl0IxJqPcTE4woLnFm864+ VOwMSmhFFAQdSDu6uNhEzyJTzcpmD37UY9xM0umrnGMXhuKfCXVpXM44M4Qu+hykzha9jRiHZ5H Ql97qoNpfl3LDGKnQZsrGZwwJiMC1kbziWj2k X-Gm-Gg: AeBDietaP9aXwLcNwG77lmdBciJIuX1KZHGOtwOrvEU8FHzMschdy0f3L20en/0gyTB VaFH09Wokh9csPalzoti8gKIMEi7U4ik6KvdVG2AkifIAq9EY4UlG2xvZCQ/Chl7x0sAuZXPKg6 e4VknziLMfhaXuAOS/4E6EzbjC8MCYFQrgWgjq+pE44dcJskui0fvSH1yFC8KxEpCVIijI5R6ci WQO6fLM0zqmg98QxGo+9oFilRnXcmIkh5MzhGTc03a16pEM+a4cTclMBJPAuYXRRcHxd0tBae6i OVVOkMUuxzTiRo72uoA= X-Received: by 2002:a05:6870:80c7:b0:41c:f37:ede1 with SMTP id 586e51a60fabf-423e0e570aamr10552149fac.14.1776176423126; Tue, 14 Apr 2026 07:20:23 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 14 Apr 2026 10:20:11 -0400 X-Gm-Features: AQROBzC2tHtlqqgR0-2TLG_iuXgsCg40oBtChEDEIs97VGTtBx0wPhDcraAFe18 Message-ID: Subject: =?UTF-8?B?UmU6IHBlcmZvcm1hbmNlwqBpc3N1ZcKgd2l0aMKgYcKgUEwvcGdTUUzCoGZ1bmN0aW9u?= =?UTF-8?B?wqA=?= To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000c2494e064f6c4c59" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c2494e064f6c4c59 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 aro= und 20 times per day > and on peak days more than 400 > times. Under normal conditions, it completes in 2=E2=80=934 minutes depen= ding 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 functi= on 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."DocumentNumbe= r") > . > . > 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."DocumentNumbe= r") > . > . > 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 not= occur at a fixed step=E2=80=94it can happen at different parts of the func= tion 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 ap= preciated. > 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. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000c2494e064f6c4c59 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 14, 2026 at 9:56=E2=80=AFAM O= MPRAKASH SAHU <sahuop2121@gmail.= com> wrote:
Hi=C2=A0T= eam,

Greetings!

We=C2=A0are=C2=A0facing=C2=A0an=C2=A0= intermittent=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)
*=C2=A0CPU/Memory/IO=C2=A0normal
<= div dir=3D"auto">
**Problem=C2=A0Description:**<= /div>
We=C2=A0have=C2=A0multiple=C2=A0PL/pgSQL=C2=A0functi= ons.=C2=A0One=C2=A0of=C2=A0the=C2=A0functions=C2=A0is=C2=A0executed=C2=A0ar= ound=C2=A020=C2=A0times=C2=A0per=C2=A0day and on peak days more than 400 ti= mes.=C2=A0Under=C2=A0normal=C2=A0conditions,=C2=A0it=C2=A0completes=C2=A0in= =C2=A02=E2=80=934=C2=A0minutes=C2=A0depending=C2=A0on=C2=A0input=C2=A0param= eters.

However,=C2=A0int= ermittently=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=A0fun= ction=C2=A0(for=C2=A0example,=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=A0minutes.

Example:

Origi= nal=C2=A0condition:
=C2=A0RAISE=C2=A0NOTICE=C2=A0= 9;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"TempOriginalDocument";<= /div>
=C2=A0 CREATE=C2=A0TEMP=C2=A0TABLE=C2=A0"TempOr= iginalDocument"=C2=A0AS
=C2=A0 SELECT=C2=A0
=C2=A0 =C2=A0pdra."Id"=C2=A0"PurchaseDoc= umentRecoId"
=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&quo= t;
=C2=A0 =C2=A0INNER=C2=A0JOIN=C2=A0report."Do= cumentD"=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()::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";<= /div>
=C2=A0 CREATE=C2=A0TEMP=C2=A0TABLE=C2=A0"TempOr= iginalDocument"=C2=A0AS
=C2=A0 SELECT=C2=A0
=C2=A0 =C2=A0pdra."Id"=C2=A0"PurchaseDoc= umentRecoId"
=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&quo= t;
=C2=A0 =C2=A0INNER=C2=A0JOIN=C2=A0report."Do= cumentD"=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 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=A0performance=C2=A0returns=C2=A0to= =C2=A0normal.
FYI-- these problematic functions will= be executing in a sequnce one at time

**Additional=C2=A0Findings:**

*=C2=A0We=C2=A0added=C2=A0`RAISE=C2=A0NOTICE`=C2=A0sta= tements=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=A0functi= on=C2=A0at=C2=A0step=C2=A06,8,=C2=A062=C2=A0etc.
*= =C2=A0Running=C2=A0`VACUUM=C2=A0ANALYZE`=C2=A0on=C2=A0the=C2=A0main=C2=A0ta= ble=C2=A0did=C2=A0not=C2=A0resolve=C2=A0the=C2=A0issue.
* Seeing the postgresql logs I have observed that catlogue tables are b= eing vacuumed too frequently=C2=A0

is=C2=A0it=C2=A0query=C2=A0plan=C2=A0caching=C2=A0or=C2=A0parame= ter-sensitive=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= meaningless when checking for nullity:
=C2=A0LOWER(pdra.&= quot;OriginalDocumentNumber")=C2=A0IS=C2=A0NOT NULL

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

--
Death to <Redacted>, and but= ter sauce.
Don't boil me, I'm still alive.
<Red= acted> lobster!
--000000000000c2494e064f6c4c59--