From: Eric Raskin <eraskin@paslists.com>
To: Michael Lewis <mlewis@entrata.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Adding nextval() to a select caused hang/very slow execution
Date: Wed, 4 Nov 2020 15:39:41 -0500
Message-ID: <CAF9L-R7Oji7E6zbRpWqL+8E=1qO2rb9deU_KjzeaaCsmhoKmLQ@mail.gmail.com> (raw)
In-Reply-To: <CAHOFxGr5FWfykQf4QtUOUap-aRBh-Yw4XAOx=m_GTdA2HKMfqw@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>
<1237035.1604516511@sss.pgh.pa.us>
<CAF9L-R42+L101Pg63bUFFtXWzRqGcHLkL6DFibfqUprNccTEZw@mail.gmail.com>
<CAHOFxGr5FWfykQf4QtUOUap-aRBh-Yw4XAOx=m_GTdA2HKMfqw@mail.gmail.com>
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 <mlewis@entrata.com> wrote:
> On Wed, Nov 4, 2020 at 12:12 PM Eric Raskin <eraskin@paslists.com> 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
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: eraskin@paslists.com, mlewis@entrata.com, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: Adding nextval() to a select caused hang/very slow execution
In-Reply-To: <CAF9L-R7Oji7E6zbRpWqL+8E=1qO2rb9deU_KjzeaaCsmhoKmLQ@mail.gmail.com>
* 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