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 1wCqU2-002MR2-2m for pgsql-admin@arkaria.postgresql.org; Wed, 15 Apr 2026 03:00:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wCqTz-00ELzZ-1V for pgsql-admin@arkaria.postgresql.org; Wed, 15 Apr 2026 03:00:04 +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 1wCqTy-00ELzL-2m for pgsql-admin@lists.postgresql.org; Wed, 15 Apr 2026 03:00:04 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wCqTx-000000014bv-0Epo for pgsql-admin@lists.postgresql.org; Wed, 15 Apr 2026 03:00:02 +0000 Received: by mail-ed1-x52c.google.com with SMTP id 4fb4d7f45d1cf-66bd4e0560fso8021692a12.0 for ; Tue, 14 Apr 2026 20:00:01 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1776222000; cv=none; d=google.com; s=arc-20240605; b=XHNVGnE/eKZ0TfEG/7O59XkhwemWyM4+dunY2sKauKyFyCKKsOc/lFQXZeN4ix8kpS Su35tiseR2uY8HcEd0eq9PPhPy3a8C/bMD5g6OLv/FCVwYLS0eKowCy4UVnCmKh5oqNB vP9ns5MgHqA1+TY32Mw6o7uBmkZEqYr/5hXy9umi8vhfBPoWKZr3L2mt+XPcmCV2zysV MoU6XGAzRYhwD1Xt3cYYg8u0Z23fHMHmzibg7kWJR8ThGG+7yEsdqKHpuMhJML7P0jEr lyKcmljqaVLu11wBkoFrbTq7WDocCPmFFbd1+TaVsNFjyLa6C57UxPq5b/VaymsYmmrK 4eMA== 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=r4zjRNVhKIneue+K/LCa1INOecCo9BSRgQUyViSuA9k=; fh=YVqAsyQLaqkSOJ1zvqJdlUnoB6bSkwHNHq4/MX0Vk/s=; b=CReWFIRXFoM/oaOci/dbXeCeVmPFXNJsQC19HsqYFjb5t+y3plBqWdKN3fw5dMqpw3 5aOv70R3ZkPeURt3mnyjsz+w2xqixrHx3UZ20SO7YpyNgBTcGp16hzhPGLmQbgyG5CI8 ZW6GHlWRUPV3983Hu98vQSI6sFSmbF5P68LiROdgfV/JONuLmeUzKpHO5IGsQgqa5Qbg nG2fCycjCaVU/JOZoJeZRI1izX04yVKtdhardKrlsSIX4s156DubZ0wNEwzrl+vzg/eR 1n6kRHDAPfF8etvkUKdkGCAMaOhNyv2MhF7buUhBDgA7kzaLE3JDVknkPpl2H1yE5zzT Gtog==; 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=1776222000; x=1776826800; 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=r4zjRNVhKIneue+K/LCa1INOecCo9BSRgQUyViSuA9k=; b=m7W5a4/scDm8eijPw1iTZXJHOOaSsJJCzJGZSg+WgrDxKznP9qL987DAJ3lcASTBbC QmH8y3IufLAyTGlmP0l64LNJIQOfvKuivP62I7VcWwlUcpmOF5d41m7MgLQDpjEs2d/D dk68L+5RPmeN6vSsrxA1165rjORLlIk+syzbIVtgIG3lHwLVC9v48R9KuotI4yi+HRlH UkoZ4SQfF4BSwIOpEFP/aSfsGAjUnnSPNrfctnygqr4AaEduwMznPmUmoCaaCAKHzQRV 8H9zcvYY8DVBTTP78sokErAdPRxgJdfMhpb9zDnoIBjlPx/RSnfRG2pDjOVxVsXeh3Ox 4DKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1776222000; x=1776826800; 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=r4zjRNVhKIneue+K/LCa1INOecCo9BSRgQUyViSuA9k=; b=VpIfl9koFd6UTqXbsCVbISJlwG3A+pYxHvj5D2xhKpBB8HG4H5sNfE/EyTduUMXR4b +cEsr9IJJraP+ioFE6kL6tlRPos0mBWnTKr8wPPn3mlkkmOBzfD3xyoUL3SVt/ux7PMV v3BaEJqPA8FV9ilYSCShdGwG2rM8Fv6c2EJx0zPU/n8nieVF8uCekBY5zhGEMytl2Got zdUPsv7JOfSRGJGd89W9GuWCiuGa1lSWRn8Vg3NbEEsdHZnIQMb9lz68EEDo63n3VA5F ADeHN+xItgmUjx6/y446JEewOenoCargnRTqwxoPzAaGUwN87EWhnyAYUofSYbo/X1EZ 0vRw== X-Gm-Message-State: AOJu0YxmOL2BVQJOgUcIVoW23iNt7fn1SykpBmXdgh3Jc0wbRESf1Fog UeHU7Nv+fuqNx/lThDBAWWqJyJxSbkQUq0Wkk7vkkbnDfso1LCpGO249/AtLLWlvkyr9mp5gXWz Xz1KaKto2S0EsZf0FK8PMYuGeeT4YFEs= X-Gm-Gg: AeBDieswgVuOaCRJ4W8U/f+Y7qEM+exMl+nr9wKm7Z9tz6IeJcvRDuZAgP0vA76a+1l fiq6SjKPBz9FynEKbDJCE6+tljKz5K7BxaXGksgvyslpbUUSztA9qN981Sww9Z+UWS9BI6YsR6V 3hDODBexFPc5xdddsVXxo+cMyQ5DkDOPEma7f7/qZWNYx635Tr+VlaBcWujkRZ7sj/krXpb3P7d A+Tu7svAl9YpeMkLtG4hiL0XiMWogAaP+sIOLMPeDXXZ5UqEK/hQ+87gh5FkjfFQpTqTEvr+z7/ X5AwarnbzDEXdfwv85Qrd4wbqYkaNLFQ X-Received: by 2002:a17:906:99c2:b0:b9b:51ac:cc1f with SMTP id a640c23a62f3a-b9d46228040mr1184664366b.19.1776221999242; Tue, 14 Apr 2026 19:59:59 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: OMPRAKASH SAHU Date: Wed, 15 Apr 2026 08:29:46 +0530 X-Gm-Features: AQROBzCIMXdzG3QBPMulfJxk2VUJ1FD5MknxjUS8ozMh4ia-ILdyzFGa1JSJRbw Message-ID: Subject: =?UTF-8?B?UmU6IHBlcmZvcm1hbmNlwqBpc3N1ZcKgd2l0aMKgYcKgUEwvcGdTUUzCoGZ1bmN0aW9u?= =?UTF-8?B?wqA=?= To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000004ea082064f76e9a6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004ea082064f76e9a6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks for the reply Ron Johnson. 2.Is that SELECT statement 4000+ lines long, or is there a lot of other stuff, too >>>> no it's not a single select statement, it is having more than 70 steps or segments and above shared code was the one of that segment, in most of the segments we are dropping and creating the temp table as select by joining main and other temp table on multiple columns. 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 >>>> as i explained in point 2 there are inner joins on more than 6 columns in most of the segments. I have gone through some articles for > "set plan_cache_mode =3D force_custom_plan" According to them this works in case of PREPARED statements although I tried it in the beginning of function already but no luck. I am using a temp table name 5 to 6 times, by storing the data temporarily and handing over to other temp and then drop & create temp_table with same name to store next temporary data, can it be something like temp table oid is in plan cache and recreating the temp again gives diff oid that makes execution worst intermittently??? Regards, OM On Tue, 14 Apr 2026, 21:25 Ron Johnson, wrote: > 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 tha= t > 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 t= his >> 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 poorl= y >> then adding lower in where clause work like magic, still i don't know wh= y >> 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 functi= on 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 de= pending on input parameters. >>>> >>>> >>>> However, intermittently (especially during peak load), the same functi= on 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 exampl= e, adding/removing a `LOWER()` condition in the WHERE clause causes the fun= ction 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 a= t >>>> 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 f= unction 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! >>> >> > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --0000000000004ea082064f76e9a6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks for the reply Ron Johnson.
2.Is that SELECT statement 4000+ lines long, or i= s there a lot of other stuff, too >>>> no it's not a single= select statement, it is having more than 70 steps or segments and above sh= ared code was the one of that segment, in most of the segments we are dropp= ing and=C2=A0 creating the temp table as select by joining main and other t= emp table on multiple columns.

3. Is the SELECT statement called in a loop? Because I've seen w= here that scenario causes the query planner-optimizer to switch to a generi= c plan after about 5 iterations. Executing "set plan_cache_mode =3D fo= rce_custom_plan" just before the stored procedure fixed that problem &= gt;>>> as i explained in point 2 there are inner=C2=A0 joins on mo= re than 6 columns=C2=A0 in most of the segments.
I have gone through some articles for >
"set plan_cache_mode =3D force_custom_plan"=C2=A0=
According to them this works in case of PREPARED st= atements although I tried it in the beginning of function already but no lu= ck.

=C2=A0I am using a t= emp table name 5 to 6 times, by storing the data temporarily and handing ov= er to other temp and then drop & create temp_table with same name to st= ore next temporary data, can it be something like temp table oid is in plan= cache and recreating the temp again gives diff oid that makes execution wo= rst intermittently???

Regards,
OM
On Tue, 14 Apr 2026, 21:25 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
1. Ok, I missed the &quo= t;adding/removing=C2=A0a=C2=A0`LOWER()`" part.
2. Is = that SELECT statement 4000+ lines long, or is there a lot of other stuff, t= oo?
3. Is the SELECT statement called in a loop?=C2=A0 Because I&= #39;ve seen where that scenario causes the query planner-optimizer to switc= h to a generic plan after about 5 iterations.=C2=A0 Executing "set pla= n_cache_mode =3D force_custom_plan" just before the stored procedure f= ixed that problem.

On Tue, Apr 14, 202= 6 at 10:52=E2=80=AFAM OMPRAKASH SAHU <sahuop2121@gmail.com> wro= te:
Hi Ron Johnson,

Thanks for the reply.

>>>The call to LOWER() is utterly meaningless when ch= ecking for nullity:
=C2=A0LOWER(pdra."OriginalD= ocumentNumber") IS NOT NULL >>> yes you are absolutely right = on this statement but as I said previously I just add this lower() to someh= ow replan the query execution and if the lower is there then removing the s= ame works and if not there and still performing 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 &= quot;Int+0" to replan the query execution=C2=A0


Seeing the query p= lan 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 chec= k the whole execution plan for a function just like we get for a select que= ry.

Regards,
OM

On Tue, 14 Apr 2026, 19:50 Ron Johnson, <ronljo= hnsonjr@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=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=A0si= ze:=C2=A0~80=C2=A0million=C2=A0rows=C2=A0(~45=C2=A0GB)
*=C2=A0CPU/Memory/IO=C2=A0normal

**Problem=C2=A0Description:**
We=C2=A0ha= ve=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=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=A0intermittently=C2=A0(especially= =C2=A0during=C2=A0peak=C2=A0load),=C2=A0the=C2=A0same=C2=A0function=C2=A0ex= ecution=C2=A0takes=C2=A0more=C2=A0than=C2=A035=C2=A0minutes.=C2=A0We=C2=A0t= ypically=C2=A0cancel=C2=A0the=C2=A0execution=C2=A0at=C2=A0that=C2=A0point.<= /div>

**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/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=A0exe= cute=C2=A0again=C2=A0in=C2=A0the=C2=A0normal=C2=A02=E2=80=934=C2=A0minutes.=

Example:

Original=C2=A0condition:
=C2=A0RAISE=C2=A0NOTICE=C2=A0'Step6 : %',=C2=A0clock_tim= estamp()::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=A0EXI= STS=C2=A0"TempOriginalDocument";
=C2=A0 CR= EATE=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"Temp= 2BUnReconciledIds"=C2=A0tid
=C2=A0 =C2=A0INNER= =C2=A0JOIN=C2=A0report."DocumentD"=C2=A0r_pd=C2=A0 ON=C2=A0tid.&q= uot;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."Origin= alDocumentNumber")=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."Origi= nalDocumentNumber")=C2=A0IS=C2=A0NOT NULL
=C2= =A0 =C2=A0 AND=C2=A0pdra."OriginalDocumentDate"=C2=A0IS=C2=A0NOT = NULL;
=C2=A0 =C2=A0=C2=A0
rep= lace=C2=A0with......=C2=A0 =C2=A0=C2=A0
=C2=A0
=
=C2=A0RAISE=C2=A0NOTICE=C2=A0'Step6 : %',=C2=A0cl= ock_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= =A0AS
=C2=A0 SELECT=C2=A0
=C2= =A0 =C2=A0pdra."Id"=C2=A0"PurchaseDocumentRecoId"
=
=C2=A0 FROM=C2=A0
=C2=A0 =C2=A0&qu= ot;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=A0pdr= a=C2=A0=C2=A0
=C2=A0 =C2=A0 ON=C2=A0LOWER(pdra."= ;OriginalDocumentNumber")=C2=A0=3D=C2=A0LOWER(r_pd."DocumentNumbe= r")=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."Original= DocumentNumber"=C2=A0IS=C2=A0NOT NULL
=C2=A0 = =C2=A0 AND=C2=A0pdra."OriginalDocumentDate"=C2=A0IS=C2=A0NOT NULL= ;

Or=C2=A0vice=C2=A0vers= a.

After=C2=A0such=C2=A0= a=C2=A0change,=C2=A0performance=C2=A0returns=C2=A0to=C2=A0normal.
FYI-- these problematic functions will be executing in a sequ= nce one at time

**Additi= onal=C2=A0Findings:**

*= =C2=A0We=C2=A0added=C2=A0`RAISE=C2=A0NOTICE`=C2=A0statements=C2=A0and=C2=A0= observed=C2=A0that=C2=A0the=C2=A0delay=C2=A0does=C2=A0not=C2=A0occur=C2=A0a= t=C2=A0a=C2=A0fixed=C2=A0step=E2=80=94it=C2=A0can=C2=A0happen=C2=A0at=C2=A0= different=C2=A0parts=C2=A0of=C2=A0the=C2=A0function=C2=A0at=C2=A0step=C2=A0= 6,8,=C2=A062=C2=A0etc.
*=C2=A0Running=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 postgre= sql logs I have observed that catlogue tables are being vacuumed too freque= ntly=C2=A0

is=C2=A0it=C2= =A0query=C2=A0plan=C2=A0caching=C2=A0or=C2=A0parameter-sensitive=C2=A0plann= ing=C2=A0?
Any=C2=A0guidance=C2=A0on=C2=A0how=C2=A0t= o=C2=A0diagnose=C2=A0or=C2=A0resolve=C2=A0this=C2=A0issue=C2=A0would=C2=A0b= e=C2=A0greatly=C2=A0appreciated.
<= div>
The call to LOWER() is utterly meaningless when checking= for nullity:
=C2=A0LOWER(pdra."OriginalDocumentNumbe= r")=C2=A0IS=C2=A0NOT NULL

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

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


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