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

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