Re: performance issue with a PL/pgSQL function 

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 =
force_custom_plan" just before the stored procedure fixed that problem.

On Tue, Apr 14, 2026 at 10:52 AM OMPRAKASH SAHU <sahuop2121(at)gmail(dot)com>
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 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, <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On Tue, Apr 14, 2026 at 9:56 AM OMPRAKASH SAHU <sahuop2121(at)gmail(dot)com>
>> 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 around 20 times per day
>>> and on peak days more than 400
>>> times. Under normal conditions, it completes in 2–4 minutes depending 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–4 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" = r_pd."Id"
>>> INNER JOIN report."DocumentD" pdra
>>>
>>> ON LOWER(pdra."OriginalDocumentNumber") = 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" = r_pd."Id"
>>> INNER JOIN report."DocumentD" pdra
>>>
>>> ON LOWER(pdra."OriginalDocumentNumber") = 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 not occur at a fixed step—it 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.
>>>
>>
>> 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 <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> lobster!