public inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

From: 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 10:20:11 -0400
Message-ID: <CANzqJaCABtV4=GRfEG15msU41-qjno4eA9_wxLYQjgPrs3t26w@mail.gmail.com> (raw)
In-Reply-To: <CAOZWJqMLq8LiHmSOZXJ0+H0x6XF7RaFvh-eNvVgAn4JeSfcyew@mail.gmail.com>
References: <CAOZWJqMLq8LiHmSOZXJ0+H0x6XF7RaFvh-eNvVgAn4JeSfcyew@mail.gmail.com>

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!

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: <CANzqJaCABtV4=GRfEG15msU41-qjno4eA9_wxLYQjgPrs3t26w@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