Function performance degrades after repeated execution

[Dave Roberge <droberge@xxxxxxxxxxxx>]

Hi,

We've been troubleshooting a slow running function in our postgres database. I've been able to boil it down to the simplest function 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 getting insight. 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.