Re: Adding nextval() to a select caused hang/very slow execution

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(at)entrata(dot)com> wrote:

> On Wed, Nov 4, 2020 at 12:12 PM Eric Raskin <eraskin(at)paslists(dot)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(at)paslists(dot)com

Mt Kisco, NY 10549
http://www.paslists.com