public inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feed performance issue with a PL/pgSQL function
6+ messages / 3 participants
[nested] [flat]
* performance issue with a PL/pgSQL function
@ 2026-04-14 13:56 OMPRAKASH SAHU <sahuop2121@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: OMPRAKASH SAHU @ 2026-04-14 13:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
Thanks in advance!
Regards,
OM
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: performance issue with a PL/pgSQL function
@ 2026-04-14 14:20 Ron Johnson <ronljohnsonjr@gmail.com>
parent: OMPRAKASH SAHU <sahuop2121@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ron Johnson @ 2026-04-14 14:20 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: performance issue with a PL/pgSQL function
@ 2026-04-14 14:52 OMPRAKASH SAHU <sahuop2121@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: OMPRAKASH SAHU @ 2026-04-14 14:52 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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!
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: performance issue with a PL/pgSQL function
@ 2026-04-14 15:55 Ron Johnson <ronljohnsonjr@gmail.com>
parent: OMPRAKASH SAHU <sahuop2121@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ron Johnson @ 2026-04-14 15:55 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: performance issue with a PL/pgSQL function
@ 2026-04-15 02:59 OMPRAKASH SAHU <sahuop2121@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: OMPRAKASH SAHU @ 2026-04-15 02:59 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 =
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 = 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, <ronljohnsonjr@gmail.com> 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 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!
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: performance issue with a PL/pgSQL function
@ 2026-04-15 05:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: OMPRAKASH SAHU <sahuop2121@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2026-04-15 05:19 UTC (permalink / raw)
To: OMPRAKASH SAHU <sahuop2121@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
OMPRAKASH SAHU <sahuop2121@gmail.com> writes:
> 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,
That's definitely an anti-pattern. Can you TRUNCATE the temp table
instead of drop-n-recreate?
> 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.
There's no "whole execution plan" for a function, only for individual
queries within it. You might find auto_explain (with
log_nested_statements turned on) to be useful to capture those query
plans.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-04-15 05:19 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-14 13:56 performance issue with a PL/pgSQL function OMPRAKASH SAHU <sahuop2121@gmail.com>
2026-04-14 14:20 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-04-14 14:52 ` OMPRAKASH SAHU <sahuop2121@gmail.com>
2026-04-14 15:55 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-04-15 02:59 ` OMPRAKASH SAHU <sahuop2121@gmail.com>
2026-04-15 05:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox