Re: Need help identifying a periodic performance issue.

[Robert Creager <robertc@xxxxxxxxxxxxxxxx>]

On Nov 18, 2021, at 2:08 PM, Thomas Munro <thomas.munro@xxxxxxxxx> wrote:

This message originated outside your organization.

On Fri, Nov 19, 2021 at 6:03 AM Robert Creager <robertc@xxxxxxxxxxxxxxxx> wrote:

Which would be better?  Discard plans or forcing custom plans?  Seems like wrapping a copy might be better than the Postgres.conf change as that would affect all statements.  What kind of performance hit would we be taking with that do you estimate?  Microseconds per statement?  Yeah, hard to say, depends on hardware and such.  Would there be any benefit overall to doing that?  Forcing the replan?

Just to understand what's going on, it'd be interesting to know if the
problem goes away if you *just* inject the DISCARD PLANS statement
before running your COPYs, but if that doesn't help it'd also be

I’m doing that now, “SET plan_cache_mode=force_custom_plan” before the copy, then auto after the copy.

interesting to know what happens if you ANALYZE each table after each
COPY.  Are you running any explicit ANALYZE commands?  How long do
your sessions/connections live for?

No explicit analyze happening.  I’m not super familiar with this code base, but a bit of looking confirmed what I thought, they served via connection pool and appear to live for the life of the app, which could be months.

After this test (tomorrow likely) I can try the explicit ANALYZE after the copy completes.

Best,

Robert