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

[Eric Raskin <eraskin@xxxxxxxxxxxx>]

set enable_nestloop=off did the trick.  Execution time when down to seconds per query.

Thanks very much for your help.

On Wed, Nov 4, 2020 at 4:16 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Eric Raskin <eraskin@xxxxxxxxxxxx> writes:
> 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?

Generic vs specific plan, perhaps?  Are you passing any parameter
values in from plpgql variables?

IIRC, you could force the matter by using EXECUTE, though it's
somewhat more notationally tedious.  In late-model PG versions,
plan_cache_mode could help you too.

                        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@xxxxxxxxxxxx

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