Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kaOD5-0006Mj-5p for pgsql-performance@arkaria.postgresql.org; Wed, 04 Nov 2020 19:12:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kaOD2-0003GV-Ou for pgsql-performance@arkaria.postgresql.org; Wed, 04 Nov 2020 19:12:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kaOD2-0003G7-GD for pgsql-performance@lists.postgresql.org; Wed, 04 Nov 2020 19:12:40 +0000 Received: from mail-vs1-xe31.google.com ([2607:f8b0:4864:20::e31]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kaOCy-0005gG-8M for pgsql-performance@lists.postgresql.org; Wed, 04 Nov 2020 19:12:39 +0000 Received: by mail-vs1-xe31.google.com with SMTP id r14so7162697vsa.13 for ; Wed, 04 Nov 2020 11:12:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paslists-com.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=ygJPn1uXvdkUWFJIikF5HeLtdTA3Ufbe+puuzhQAE2k=; b=ZTPRI0HxEJbHeKLtFox111ElmAWPtikPyAQj03iKLGdwReY/7P+IAVSGJ5xkQerWEE nhvvVSQjdHV8+XAzRMi6KfmC9pHfcQeUm9ALNT94qYxjDt1JK0bBKuaatAy5TZvKTp3k LKk2qaqdsy7SPcSLuQsY9TqH2cHrRL+FSoLU712L3vdFW50PQjwRVzHg4dz/ZQwvWZqi stsg3jt2kJ3rR6HoOC6Hzsp2H/2UIiMQoR9rJ7qA+2fNBaYYnRf6zrg9dN429nHrGEf6 v6afkYgyBW4AqKOfeqClwE/jQ7tki0tgUKmMrrt0RwFositOfL8QIl0eSFywG4bjFWO2 cC4w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=ygJPn1uXvdkUWFJIikF5HeLtdTA3Ufbe+puuzhQAE2k=; b=bYsqxux1KTQTnpkRw3xmUEIfCUHrtx6g3ur+f0bRC9myNyiTcFUQH26F86FSARQB3k 5W4W3Gz6gpg35dLdURX1C5579mEinB46GXzS1zY3PMjeyGoksA5ufOvw1ufxwcAkPE9J ykRvm8WOKJfFUu20UALOP+jv5L/ZhOvBaeELfhIqErPnQc8cE68NhH73cFsRfG/S/m28 2qxwz08ypI+0wIFe4hBZwx9oUBdbePGcJjJFhGptkECo1BvaPdBllRVouBcGX5oYUcwL JONxjWJHFINFXwV/TzyKIorLkDnZPbqWjWa8vXZB6L8MvUWFu/dL+4vfcXkiY2yeIAXk pQAw== X-Gm-Message-State: AOAM5338KDGeG1jisGCvMOQ5Iay1gTfctzIvNJEngO9AfWratq8ob0uR cGXWW9PQqkcOHh6PzwI3gNl9IjFudsNGl/RIy/aGgg== X-Google-Smtp-Source: ABdhPJyIQiZWB8A9WBBMXAwHP4S7GAVcwLY9l+MO0YABUbaIOyTkzfpM7a4xFlyIg9X9FF+QcMRTIpY8ND1N+FpjDEg= X-Received: by 2002:a67:687:: with SMTP id 129mr23677912vsg.37.1604517154361; Wed, 04 Nov 2020 11:12:34 -0800 (PST) MIME-Version: 1.0 References: <1234726.1604513058@sss.pgh.pa.us> <1237035.1604516511@sss.pgh.pa.us> In-Reply-To: <1237035.1604516511@sss.pgh.pa.us> From: Eric Raskin Date: Wed, 4 Nov 2020 14:12:07 -0500 Message-ID: Subject: Re: Adding nextval() to a select caused hang/very slow execution To: Tom Lane Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000005a3e805b34cc344" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000005a3e805b34cc344 Content-Type: text/plain; charset="UTF-8" OK - I see. And to add insult to injury, I tried creating a temporary table to store the intermediate results. Then I was going to just do an insert... select... to insert the rows. That would de-couple the nextval() from the query. Strangely, the first query I tried it on worked great. But, when I tried to add a second set of data with a similar query to the same temporary table, it slowed right down again. And, of course, when I remove the insert, it's fine. And, of course, your explanation that inserts will not be parallelized must be the reason. I will certainly re-vacuum the tables. I wonder why auto-vacuum didn't collect better stats. vacuum analyze is all I need, right? As a last resort, what about a PL/PGSQL procedure loop on the query result? Since the insert is very few rows relative to the work the select has to do, I could just turn the insert.. select.. into a for loop. Then the select could be parallel? What do you think? On Wed, Nov 4, 2020 at 2:01 PM Tom Lane wrote: > Eric Raskin 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 = ANY ('{0,1,7,9}'::bpchar[])) THEN > c.rtype ELSE x.rtype END = '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 > -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Eric H. Raskin 914-765-0500 x120 or *315-338-4461 (direct)* Professional Advertising Systems Inc. fax: 914-765-0500 or *315-338-4461 (direct)* 3 Morgan Drive #310 eraskin@paslists.com Mt Kisco, NY 10549 http://www.paslists.com --00000000000005a3e805b34cc344 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
OK - I see.=C2=A0 And to add insult to injury, I tried cre= ating a temporary table to store the intermediate results.=C2=A0 Then I was= going to just do an insert... select... to insert the rows.=C2=A0 =C2=A0Th= at would de-couple the nextval() from the query.

Strange= ly, the first query I tried it on worked great.=C2=A0 But, when I tried to = add a second set of data with a similar query to the same temporary table, = it slowed right down again.=C2=A0 And, of course, when I remove the insert,= it's fine.=C2=A0=C2=A0

And, of course, your e= xplanation=C2=A0that inserts will not be parallelized must be the reason.= =C2=A0 I will certainly re-vacuum the tables.=C2=A0 I wonder why auto-vacuu= m didn't collect better stats.=C2=A0 vacuum=C2=A0 analyze <table>= is all I need, right?

As a last resort, what abou= t a PL/PGSQL procedure loop on the query result?=C2=A0 Since the insert is = very few rows relative to the work the select has to do, I could just turn = the insert.. select.. into a for loop.=C2=A0 Then the select could be paral= lel?

What do you think?

=
On Wed= , Nov 4, 2020 at 2:01 PM Tom Lane <= tgl@sss.pgh.pa.us> wrote:
Eric Raskin <eraskin@paslists.com> writes:
> And, to follow up on your question, the plan shape DOES change when I<= br> > add/remove the nextval() on a plain explain.
> Without nextval():=C2=A0 https://explain.depesz.com/s/SCdY=
> With nextval():=C2=A0 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.=C2=A0 It's not immediately clear why that wo= uld
result in more than about a 4X speed difference, given that the parallel plan is using 4 workers.=C2=A0 But some of the rowcount estimates seem fair= ly
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 choice= s
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.=C2=A0 I suspect most of the problem is join conditions like

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

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

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane


--

---------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------

Eric H. Raskin =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0914-765-0500 x120 or=C2=A0315-338-4461 (direct)

Professional Advertising Systems Inc.=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0fax: 914-765-0500 or 315-338-4461 (direct)

3 Morgan Drive #310=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0eraskin@paslists.com

Mt Kisco, NY 10549=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0http://www.paslists.com<= /span>

=
--00000000000005a3e805b34cc344--