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

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