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 <mle...@entrata.com> wrote:
> On Wed, Nov 4, 2020 at 12:12 PM Eric Raskin <eras...@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
eras...@paslists.com
Mt Kisco, NY 10549
http://www.paslists.com
