From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Thomas Munro <thomas(dot)munro(at)gmail(dot)com> |
Cc: | Robert Creager <robertc(at)spectralogic(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Need help identifying a periodic performance issue. |
Date: | 2021-11-18 05:42:21 |
Message-ID: | 20211118054221.GJ17618@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Creager | 2021-11-18 17:03:08 | Re: Need help identifying a periodic performance issue. |
Previous Message | Thomas Munro | 2021-11-18 03:39:42 | Re: Need help identifying a periodic performance issue. |