Re: Need help identifying a periodic performance issue.

[Robert Creager <robertc@xxxxxxxxxxxxxxxx>]

On Nov 18, 2021, at 2:42 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

This message originated outside your organization.

Thomas Munro <thomas.munro@xxxxxxxxx> writes:

This is all guesswork though, since we don't know quite what's
happening on Robert's system.  It might be worth setting
"log_autovacuum_min_duration = 0" (either globally, or as a
reloption on the relevant tables), and seeing if there seems
to be any correlation between autovacuum/autoanalyze activity
and the occurrences of poor plan choices.

Ok, doing a SET plan_cache_mode=force_custom_plan before the COPY and resetting it after appears to fix the problem.  We’re going to run it over the weekend to make sure.

So, I thank you very much for all your help.

I have logs with autovacuum=0 and dtrace output every minute, but suspect that won’t help you now.  Would you like me to remove the fix next week and reproduce the issue with the same config to provide more information for trouble shooting?  I may be able get a SSH session into a live system, I’d have to check with IT to see if that’s possible/allowed.

Best,

Robert