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 <eras...@paslists.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 <t...@sss.pgh.pa.us> wrote: > >> Eric Raskin <eras...@paslists.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 > eras...@paslists.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 eras...@paslists.com Mt Kisco, NY 10549 http://www.paslists.com
