Re: performance issue with a PL/pgSQL function  - Mailing list pgsql-admin

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!