From: Justin Pryzby <pryzby@telsasoft.com>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Robert Creager <robertc@spectralogic.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Need help identifying a periodic performance issue.
Date: Wed, 17 Nov 2021 23:42:21 -0600
Message-ID: <20211118054221.GJ17618@telsasoft.com> (raw)
In-Reply-To: <CA+hUKGJ4597Hz1jydC5mQL29uDxbBG2CXruxx+S=qSSQmqiL3w@mail.gmail.com>
References: <3A76761B-3DDD-4A3C-B791-E73107AFC9A2@spectralogic.com>
<CA+hUKG+iKkdeS9g1=866pypHZwPMQL2ho+oFWLSpRjV=-PvuCg@mail.gmail.com>
<0C3B1B2B-6571-477D-A89C-6FA7B6259840@spectralogic.com>
<20211117190038.GB17618@telsasoft.com>
<2736634.1637177330@sss.pgh.pa.us>
<CA+hUKGKmn0g2nb6W7-kh=iGPLise=sLhiVJjBS+wV1ofDuci4g@mail.gmail.com>
<5BAA534E-AECC-4533-951A-0B6D88A070F3@spectralogic.com>
<20211117231815.GF17618@telsasoft.com>
<1ADFE43B-5A43-40DA-B039-2D7E5FD82B6C@spectralogic.com>
<CA+hUKGJ4597Hz1jydC5mQL29uDxbBG2CXruxx+S=qSSQmqiL3w@mail.gmail.com>
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@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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, thomas.munro@gmail.com, robertc@spectralogic.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: Need help identifying a periodic performance issue.
In-Reply-To: <20211118054221.GJ17618@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox