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 1kaPZq-0000vq-UK for pgsql-performance@arkaria.postgresql.org; Wed, 04 Nov 2020 20:40:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kaPZo-0007HX-PR for pgsql-performance@arkaria.postgresql.org; Wed, 04 Nov 2020 20:40:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kaPZo-0007HQ-4y for pgsql-performance@lists.postgresql.org; Wed, 04 Nov 2020 20:40:16 +0000 Received: from mail-ua1-x929.google.com ([2607:f8b0:4864:20::929]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kaPZh-0002oM-GF for pgsql-performance@lists.postgresql.org; Wed, 04 Nov 2020 20:40:15 +0000 Received: by mail-ua1-x929.google.com with SMTP id y1so6434509uac.13 for ; Wed, 04 Nov 2020 12:40:09 -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=cwELtxZcaxG1/KdKqOyys/CfsATOxuhDzPj+/lkRJ7I=; b=fGNwTGfYugVYLnEZxgBFfTQKDDtGf7Qlee6mHhUw95NGIR3IA45Ws+jROMCEsASXIt msEwOSE+6JEKGr4GZgEphJLS2077ojabSEzM8aN2YKLTURvRfKxkP3G1Z/CSLYmSzJ82 lixsgL9I9wtsGsM4qTBSchN6a4XiIbOcNKtmoIN86mR6cb7L/qUWkDNvtmm7x3IbOxrq KnIGzDTG+4FGlSY8MIgihklRGou1BrjxKufGEwqeLbnBHoDAzfzBUpSAq1r+NAwuh1XT kZbXZgU2iDxurGHMMi9vgrQI3ZE8Fmd9jO7ydrsrnvhCMoWP9on63izBRYRastmrw6Vy 7RKA== 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=cwELtxZcaxG1/KdKqOyys/CfsATOxuhDzPj+/lkRJ7I=; b=owjEH/5d4mCUvPLo9l5ZVY9f9BCLfgwl1du+xXgxRXoAaPGAgz4oTW9bRJFa7dS5OV PVFBQ+HCuVZRcvLV2obpML0zSWH+9P95Q+8T0jA/IYTd6qpwFbk5sX1uGnRivRVssmuX W6Hy+wW/c3/7BVA8CCniIh6sl/7XzultR+56lB+rAfr4Q/jHDYmtD1TtRGAE/iupAWLf pYmF4BMmxH/RNSkRvkDAWnQ4pFQqNWN3tSa9xbYDK+xiV0+WtCDmyibSoQglImpjDeT6 C0UPw9OWUWZCPnvi/h/nPkZpolmVulh+2LUt22tv5n2jIwRwbzzONkSH9HtE9ZbpU9/q 9O4Q== X-Gm-Message-State: AOAM530ROO5UCe4Ty0M7LQXoJmBxbW/5bnFDv/LY+z250DVfwgekN1V/ D3tzlIPgktiWeeMrjYqFDbe38DMVXqPsXNBJMULv3Q== X-Google-Smtp-Source: ABdhPJx53OsNCQPJlhFfpUYHTyE+1OpuXlXhiusSKCdp1YjbEhgjWk09ZD3xG9yQSnop5u8lYZGY9wu6CcbZlrG+CEE= X-Received: by 2002:ab0:1d92:: with SMTP id l18mr3975811uak.41.1604522408184; Wed, 04 Nov 2020 12:40:08 -0800 (PST) MIME-Version: 1.0 References: <1234726.1604513058@sss.pgh.pa.us> <1237035.1604516511@sss.pgh.pa.us> In-Reply-To: From: Eric Raskin Date: Wed, 4 Nov 2020 15:39:41 -0500 Message-ID: Subject: Re: Adding nextval() to a select caused hang/very slow execution To: Michael Lewis Cc: Tom Lane , Pgsql Performance Content-Type: multipart/alternative; boundary="0000000000002ca86e05b34dfcb9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000002ca86e05b34dfcb9 Content-Type: text/plain; charset="UTF-8" So, things get even weirder. When I execute each individual select statement I am generating from a psql prompt, they all finish very quickly. If I execute them inside a pl/pgsql block, the second one hangs. Is there something about execution inside a pl/pgsql block that is different from the psql command line? On Wed, Nov 4, 2020 at 3:20 PM Michael Lewis wrote: > On Wed, Nov 4, 2020 at 12:12 PM Eric Raskin wrote: > >> 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. >> > > I am not entirely sure I am understanding your process properly, but just > a note- If you are getting acceptable results creating the temp table, and > the issue is just that you get very bad plans when using it in some query > that follows, then it is worth noting that autovacuum does nothing on temp > tables and for me it is nearly always worth the small cost to perform an > analyze (at least on key fields) after creating a temp table, or rather > after inserting/updating/deleting records in a significant way. > -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 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 --0000000000002ca86e05b34dfcb9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
So, things get even weirder.=C2=A0 =C2=A0When I execute ea= ch individual=C2=A0select statement=C2=A0I am generating from a psql prompt= , they all finish very quickly.=C2=A0=C2=A0

If I execute= them inside a pl/pgsql block, the second one hangs.

Is there something=C2=A0about execution=C2=A0inside a pl/pgsql block tha= t is different from the psql command=C2=A0line?

<= br>
On Wed,= Nov 4, 2020 at 3:20 PM Michael Lewis <mlewis@entrata.com> wrote:
On Wed, Nov 4, 202= 0 at 12:12 PM Eric Raskin <eraskin@paslists.com> wrote:
OK - I see.=C2=A0 And to add insult to injury, I tried creating a tempora= ry 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=A0That would de-cou= ple the nextval() from the query.

Strangely, the first q= uery I tried it on worked great.=C2=A0 But, when I tried to add a second se= t 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

I am not entirely sure I= am understanding your process properly, but just a note- If you are gettin= g acceptable results creating the temp table, and the issue is just that yo= u get very bad plans when using it in some query that follows, then it is w= orth noting that autovacuum does nothing on temp tables and for me it is ne= arly always worth the small cost to=C2=A0perform an analyze (at least on ke= y fields) after creating a temp table, or rather after inserting/updating/d= eleting records in a significant way.


--

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

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>

=
--0000000000002ca86e05b34dfcb9--