pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
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
Date: Wed, 04 Nov 2020 14:01:51 -0500
Message-ID: <1237035.1604516511@sss.pgh.pa.us> (raw)
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>
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 = 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
view thread (14+ messages) latest in thread
Message-ID: <1237035.1604516511@sss.pgh.pa.us> Permalink: ../1237035.1604516511@sss.pgh.pa.us/ Also on: postgresql.org/message-id/1237035.1604516511@sss.pgh.pa.us ·
reply
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: tgl@sss.pgh.pa.us, eraskin@paslists.com, pgsql-performance@lists.postgresql.org Subject: Re: Adding nextval() to a select caused hang/very slow execution In-Reply-To: <1237035.1604516511@sss.pgh.pa.us> * 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