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

And, to follow up on your question, the plan shape DOES change when I
add/remove the nextval() on a plain explain.

Without nextval(): https://explain.depesz.com/s/SCdY

With nextval(): https://explain.depesz.com/s/oLPn

On Wed, Nov 4, 2020 at 1:22 PM Eric Raskin <eraskin(at)paslists(dot)com> wrote:

> Thanks for the reply. I see that the explain.depesz.com did not show you
> the query. My apologies:
>
> select unnest(array[273941676,273941677,273941678,273941679,273941680])
> countrow_id,
> disporder, fmtdate, typecode,
>
> unnest(array[count_273941676,count_273941677,count_273941678,count_273941679,count_273941680])
> countval
> from (select coalesce(count(distinct id_273941676),0) count_273941676,
> coalesce(count(distinct id_273941677),0) count_273941677,
> coalesce(count(distinct id_273941678),0) count_273941678,
> coalesce(count(distinct id_273941679),0) count_273941679,
> coalesce(count(distinct id_273941680),0) count_273941680,
> disporder, fmtdate, typecode
> from (select case when sexcode = 'M' then id else null end
> id_273941676,
> case when sexcode = 'F' then id else null end
> id_273941677,
> case when sexcode = 'A' then id else null end
> id_273941678,
> case when sexcode = 'C' then id else null end
> id_273941679,
> case when sexcode not in ('M','F','A','C') then id
> else null end id_273941680,
> hotline cnt_hotline
> from lruser.fortherb_indcounts c
> where ( (rtype = '2')
> and ((sexcode = 'M') or (sexcode = 'F') or (sexcode = 'A')
> or (sexcode = 'C') or (sexcode not in ('M','F','A','C'))
> )
> )
> ) as x
> right outer join count_tempcols t on (x.cnt_hotline between t.mindate and
> t.maxdate) group by disporder, fmtdate, typecode ) as y
>
> I know it seems overly complicated, but it is auto-generated by our code.
> The conditions and fields are variable based on what the user wants to
> generate.
>
> This is the topmost select. The only difference that causes the hang is
> adding nextval('sbowner.idgen') to the start of the select right before the
> first unnest().
>
> In the real application, this code feeds an insert statement with a
> trigger that accesses the sequence where we store the results of the
> query. I "simplified" it and discovered that the nextval() was the
> difference that caused the performance hit.
>
> Eric
>
>
> On Wed, Nov 4, 2020 at 1:04 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Eric Raskin <eraskin(at)paslists(dot)com> writes:
>> > I have a strange situation where a base query completes in about 30
>> seconds
>> > but if I add a nextval() call to the select it never completes. There
>> are
>> > other processes running that are accessing the same sequence, but I
>> thought
>> > that concurrency was not an issue for sequences (other than skipped
>> > values).
>>
>> Shouldn't be, probably ... but did you check to see if the query is
>> blocked on a lock? (See pg_stat_activity or pg_locks views.)
>>
>> > The only change that
>> > causes it to be extremely slow or hang (can't tell which) is that I
>> changed
>> > the select from:
>> > select unnest(....
>> > to
>> > select nextval('sbowner.idgen'), unnest(....
>>
>> Without seeing the complete query it's hard to say much. But if
>> this isn't the topmost select list, maybe what's happening is that
>> the presence of a volatile function in a sub-select is defeating
>> some key plan optimization. Did you compare plain EXPLAIN (w/out
>> ANALYZE) output for the two cases, to see if the plan shape changes?
>>
>> 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(at)paslists(dot)com
>
> Mt Kisco, NY 10549
> http://www.paslists.com
>
>

--

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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