Re: Need help identifying a periodic performance issue.

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
ourresources 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;

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);