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