From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eric Raskin <eraskin@paslists.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Adding nextval() to a select caused hang/very slow execution
In-reply-to: 
 <CAF9L-R4ve0h0xHO9S6EHX7tK6wpZPuy44d1O8=SqqyCxHW7z2A@mail.gmail.com>
References: 
 <CAF9L-R7G_ge=v21mnfH=sbfCt9LF7LhaDbUzqso+LsFhgeKbyQ@mail.gmail.com>
 <1234726.1604513058@sss.pgh.pa.us>
 <CAF9L-R4oiksGgY8AWVfpYqCnMpyPDPk8-Sct05buKmkbVM03_w@mail.gmail.com>
 <CAF9L-R4ve0h0xHO9S6EHX7tK6wpZPuy44d1O8=SqqyCxHW7z2A@mail.gmail.com>
Comments: In-reply-to Eric Raskin <eraskin@paslists.com>
	message dated "Wed, 04 Nov 2020 13:25:42 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1237034.1604516511.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 04 Nov 2020 14:01:51 -0500
Message-ID: <1237035.1604516511@sss.pgh.pa.us>
Precedence: bulk

Eric Raskin <eraskin@paslists.com> writes:
> And, to follow up on your question, the plan shape DOES change when I
> add/remove the nextval() on a plain explain.
> Without nextval():  https://explain.depesz.com/s/SCdY
> With nextval():  https://explain.depesz.com/s/oLPn

Ah, there's your problem, I think: the plan without nextval() is
parallelized while the plan with nextval() is not, because nextval() is
marked as parallel-unsafe.  It's not immediately clear why that would
result in more than about a 4X speed difference, given that the parallel
plan is using 4 workers.  But some of the rowcount estimates seem fairly
far off, so I'm betting that the planner is just accidentally lighting on
a decent plan when it's using parallelism while making some poor choices
when it isn't.

The reason for the original form of your problem is likely that we don't
use parallelism at all in non-SELECT queries, so you ended up with a bad
plan even though the nextval() was hidden in a trigger.

What you need to do is get the rowcount estimates nearer to reality
--- those places where you've got estimated rowcount 1 while reality
is tens or hundreds of thousands of rows are just disasters waiting
to bite.  I suspect most of the problem is join conditions like

Join Filter: (CASE WHEN (c.rtype =3D ANY ('{0,1,7,9}'::bpchar[])) THEN c.r=
type ELSE x.rtype END =3D '2'::bpchar)

The planner just isn't going to have any credible idea how selective
that is.  I wonder to what extent you could fix this by storing
generated columns that represent the derived conditions you want to
filter on.

			regards, tom lane