Hi,
We've been troubleshooting a slow running function in our postgres database. I've been able to boil it down to the
simplestfunction possible. It looks like this:
FOR rec IN select 1 as matchval FROM table1 t1, table2 t2
join table3 t3 on t3.col = t2.col
WHERE t1.col = id
LOOP
IF rec.matchval > 0 THEN
co := co + 1;
END IF;
if co % 100 = 0 then
raise notice 'match value %', co;
end if;
END LOOP;
Here's the interesting parts:
- The result of that query returns about 13,000 rows.
- If I open a PSQL session and execute the function it returns almost immediately.
- If I execute the same function 4 more times in the same session (a total of 5 times) it returns immediately.
- On the 6th execution it slows down. It processes 100 records every 1.5 minutes.
- On every subsequent execution from the same session (after 5 times) it is slow.
- It reliably slows down after 5 consecutive executions.
- If I exit the PSQL session and open a new one the function returns immediately (up to the 6th execution.)
- If I replace the function from a separate session after executing it 5 times, it returns immediately up to 5
executions.
- The CPU spikes to 100% after the 5 execution.
I'm attempting to understand what is causing the slow down after 5 consecutive executions. But I'm having a hard time
gettinginsight. We are on PostgreSQL 9.6.15.
We've tried:
- Increase logging to debug5 but don't get any helpful feedback there.
- Reviewing the execution plan of the query. Seems fine when running it outside of the function.
- Turn on temp file logging -- but no temp files are logged.
Any ideas for where we might get insight? Or clues as to what is happening?
Thank you.
