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

[Eric Raskin <eraskin@xxxxxxxxxxxx>]

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@xxxxxxxxxxxxx> wrote:

Eric Raskin <eraskin@xxxxxxxxxxxx> 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@xxxxxxxxxxxx

Mt Kisco, NY 10549                                                                                              http://www.paslists.com