Re: Need help identifying a periodic performance issue.

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

In response to

Responses

Browse pgsql-performance by date

  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.