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