public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Ron Johnson <ronljohnsonjr@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: performance issue with a PL/pgSQL function
Date: Tue, 14 Apr 2026 11:55:27 -0400
Message-ID: <CANzqJaCXihre4rEYn-FrF1ggDW84Ke81c+YLDg_CRrDje7o7EA@mail.gmail.com> (raw)
In-Reply-To: <CAOZWJqPk2UCcS59MeNy1UHC0MW3cKxWzf1CfmXJLo2ioVvtTEQ@mail.gmail.com>
References: <CAOZWJqMLq8LiHmSOZXJ0+H0x6XF7RaFvh-eNvVgAn4JeSfcyew@mail.gmail.com>
<CANzqJaCABtV4=GRfEG15msU41-qjno4eA9_wxLYQjgPrs3t26w@mail.gmail.com>
<CAOZWJqPk2UCcS59MeNy1UHC0MW3cKxWzf1CfmXJLo2ioVvtTEQ@mail.gmail.com>
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@gmail.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@gmail.com> wrote:
>
>> On Tue, Apr 14, 2026 at 9:56 AM OMPRAKASH SAHU <sahuop2121@gmail.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!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Re: performance issue with a PL/pgSQL function
In-Reply-To: <CANzqJaCXihre4rEYn-FrF1ggDW84Ke81c+YLDg_CRrDje7o7EA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox