Re: Need help identifying a periodic performance issue.

On Wed, Nov 24, 2021 at 10:44:12PM +0000, Robert Creager wrote:
> I forgot, I had reloaded postgres, but had not re-started our app, so the connections wouldn’t have that plan setting on them. Re-doing now.

Are you sure? GUC changes should be applied for existing sessions, right ?

Would you send the logs surrounding the slow COPY ?
Specifically including the autovacuum logs.

> We are at it again. I have a DELETE operation that’s taking 48 minutes so far.

Before, you had slow COPY due to FKs. Now you have a slow DELETE, which you
only alluded to before.

> So how do we avoid this query plan? Do we need to start doing explicit analyzes after every delete?

If your DELETE is deleting the entire table, then I think you should VACUUM
anyway (or else the next inserts will bloat the table).

Or (preferably) use TRUNCATE instead, which will set relpages=0 and (one
supposes) avoid the bad plans. But read the NOTE about non-mvcc behavior of
TRUNCATE, in case that matters to you.

But first, I believe Thomas was suggesting to put plan_cache_mode back to its
default, and (for testing purposes) try using issue DISCARD PLANS.

On Fri, Nov 19, 2021 at 10:08:02AM +1300, Thomas Munro wrote:
> 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
> 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?

--
Justin