From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eric Raskin <eraskin@paslists.com>
cc: Michael Lewis <mlewis@entrata.com>,
        Pgsql Performance <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>
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>
 <CAF9L-R7Oji7E6zbRpWqL+8E=1qO2rb9deU_KjzeaaCsmhoKmLQ@mail.gmail.com>
Comments: In-reply-to Eric Raskin <eraskin@paslists.com>
	message dated "Wed, 04 Nov 2020 15:39:41 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1322416.1604524589.1@sss.pgh.pa.us>
Date: Wed, 04 Nov 2020 16:16:29 -0500
Message-ID: <1322417.1604524589@sss.pgh.pa.us>
Precedence: bulk

Eric Raskin <eraskin@paslists.com> 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