Date: Wed, 24 Nov 2021 17:15:42 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Robert Creager <robertc@spectralogic.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Thomas Munro <thomas.munro@gmail.com>,
	pgsql-performance@lists.postgresql.org,
	Barry Litt <barryl@spectralogic.com>
Subject: Re: Need help identifying a periodic performance issue.
Message-ID: <20211124231542.GB17618@telsasoft.com>
References: <20211117231815.GF17618@telsasoft.com>
 <1ADFE43B-5A43-40DA-B039-2D7E5FD82B6C@spectralogic.com>
 <CA+hUKGJ4597Hz1jydC5mQL29uDxbBG2CXruxx+S=qSSQmqiL3w@mail.gmail.com>
 <20211118054221.GJ17618@telsasoft.com>
 <8048B2EE-E627-4818-9FB4-00E78D245CC8@spectralogic.com>
 <CA+hUKGJOdENwAwSDAdQbjJyvFfviXT_0Sa=oRRry3BoRgZYtnQ@mail.gmail.com>
 <2977426.1637271745@sss.pgh.pa.us>
 <40167D9A-8FDE-4F47-9AD0-897F7242A6B7@spectralogic.com>
 <77BB05DD-9543-4AD2-A58C-533D71864637@spectralogic.com>
 <BA7F0900-1AD5-4680-A11D-1D47429FCF2F@spectralogic.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <BA7F0900-1AD5-4680-A11D-1D47429FCF2F@spectralogic.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211124231542.GB17618%40telsasoft.com>
Precedence: bulk

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