Re: Need help identifying a periodic performance issue.

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(at)spectralogic(dot)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