3

PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Client is Data Grip and same behavior from my report server which uses the driver that comes with Jaspersoft

I am running the same function for reporting with different parameters multiple times under the same session. Also does the same thing using the same parameters.

These are being one run one after the other and not at the same time.

The result only has a few rows but does read from quite a few tables, no writes.

It is just table joins and selects no inputs or updates to the tables themselves (would like to be able to post query but can't for security reasons).

After I run the function a few times it starts to slow down and gets to an unacceptable level. For example one of the functions goes from 1 second to over 90+ seconds (that is where I stopped testing).

Troubleshooting:

I have gone to the server and terminated the session and after that it starts to run normally for a few runs.

The standard report does use a temp table but I have removed that for testing. I have run the following after it starts having the issue to try and fix the issue.

VACUUM all touched tables; -- I know this should not be required as there are no major changes to these tables but was trying pretty much anything. Language plpgsql.

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD SEQUENCES;
DISCARD TEMP;

There does not seem to be a good way to terminate idle connections without a separate application or script run through a job.

Further data point: when looking at the execution plans with auto_explain, I see plans with $1 in them from the sixth execution on.

2
  • You hit the nail on the head with the "That symptoms are placeholders like $1 in the execution plan and that it becomes slower from the sixth execution on" comment. Is there a way to overcome that or fix it in some way?
    – James
    Jun 23, 2020 at 15:51
  • PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
    – James
    Jun 23, 2020 at 19:14

1 Answer 1

2

Since the problem are bad generic plans (symptom: execution becomes slower from the sixth execution on) and you are using PostgreSQL v12, the solution is simple:

ALTER FUNCTION xy SET plan_cache_mode = force_custom_plan;
1
  • That seems to have fixed it. I did not know that command existed. Thanks for the help.
    – James
    Jun 24, 2020 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.