Date: Wed, 17 Nov 2021 23:42:21 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Robert Creager <robertc@spectralogic.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@lists.postgresql.org
Subject: Re: Need help identifying a periodic performance issue.
Message-ID: <20211118054221.GJ17618@telsasoft.com>
References: <3A76761B-3DDD-4A3C-B791-E73107AFC9A2@spectralogic.com>
 <CA+hUKG+iKkdeS9g1=866pypHZwPMQL2ho+oFWLSpRjV=-PvuCg@mail.gmail.com>
 <0C3B1B2B-6571-477D-A89C-6FA7B6259840@spectralogic.com>
 <20211117190038.GB17618@telsasoft.com>
 <2736634.1637177330@sss.pgh.pa.us>
 <CA+hUKGKmn0g2nb6W7-kh=iGPLise=sLhiVJjBS+wV1ofDuci4g@mail.gmail.com>
 <5BAA534E-AECC-4533-951A-0B6D88A070F3@spectralogic.com>
 <20211117231815.GF17618@telsasoft.com>
 <1ADFE43B-5A43-40DA-B039-2D7E5FD82B6C@spectralogic.com>
 <CA+hUKGJ4597Hz1jydC5mQL29uDxbBG2CXruxx+S=qSSQmqiL3w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+hUKGJ4597Hz1jydC5mQL29uDxbBG2CXruxx+S=qSSQmqiL3w@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20211118054221.GJ17618%40telsasoft.com>
Precedence: bulk

On Thu, Nov 18, 2021 at 04:39:42PM +1300, Thomas Munro wrote:
> On Thu, Nov 18, 2021 at 1:18 PM Robert Creager <robertc@spectralogic.com> wrote:
> > So, how do I go about capturing more information for the big brains (you guys) to help figure this out?  I have all our resources at mine (and hence your) disposal.
> 
> As a workaround, does it help if you issue DISCARD PLANS before your
> COPY jobs, or alternatively start with a fresh connection?  I'm
> guessing that something like this is happening.
> 
> -- set up the auto_explain extension to show the internal foreign key check queries' plans
> load 'auto_explain';
> set auto_explain.log_nested_statements = true;
> set auto_explain.log_min_duration = 0;
> set auto_explain.log_analyze = true;

..and SET client_min_messages=debug;

> drop table if exists r, s cascade;
> create table r (i int primary key);
> create table s (i int references r(i));
> 
> -- collect stats showing r as empty
> analyze r;
> 
> -- execute RI query 6 times to lock the plan (inserts fail, log shows seq scan)
> insert into s values (42);
> insert into s values (42);
> insert into s values (42);
> insert into s values (42);
> insert into s values (42);
> insert into s values (42);
> 
> insert into r select generate_series(1, 1000000);
> 
> -- once more, we still get a seq scan, which is by now a bad idea
> insert into s values (42);
> 
> discard plans;
>
> -- once more, now we get an index scan
> insert into s values (42);

It also seems to work if one does SET plan_cache_mode=force_custom_plan;

Robert might try that, either in postresql.conf, or SET in the client that's
doing COPY.

Robert is using jdbc, which (as I recall) has this problem more often than
other clients.  But, in this case, I think JDBC isn't causing the problem.

-- 
Justin