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@xxxxxxxxxxxxxxxx> 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
