Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> I ran this script
> CREATE SEQUENCE seq1;
> DO LANGUAGE plpythonu $$
> plan = plpy.prepare("SELECT nextval('seq1')")
> for i in range(0, 10000000):
> plpy.execute(plan)
> $$;
> and timed the "DO".
It occurred to me that plpy.execute is going to run a subtransaction for
each call, which makes this kind of a dubious test case, both because of
the extra subtransaction overhead and because subtransaction lock
ownership effects will possibly skew the results. So I tried to
reproduce the test using plain plpgsql,
CREATE SEQUENCE IF NOT EXISTS seq1;
\timing on
do $$
declare x int;
begin for i in 0 .. 10000000 loop x := nextval('seq1'); end loop;
end
$$;
On HEAD, building without cassert, I got a fairly reproducible result
of about 10.6 seconds. I doubt my machine is 6X faster than yours,
so this indicates that the subtransaction overhead is pretty real.
> I compared the stock releases with a patched version that replaces the
> body of open_share_lock() with just
> return relation_open(seq->relid, AccessShareLock);
Hm. I don't think that's a sufficient code change, because if you do it
like that then the lock remains held after nextval() returns. This means
that (a) subsequent calls aren't hitting the shared lock manager at all,
they're just incrementing a local lock counter, and whether it would be
fastpath or not is irrelevant; and (b) this means that the semantic issues
Andres is worried about remain in place, because we will hold the lock
till transaction end.
I experimented with something similar, just replacing open_share_lock
as above, and I got runtimes of just about 12 seconds, which surprised
me a bit. I'd have thought the locallock-already-exists code path
would be faster than that.
I then further changed the code so that nextval_internal ends with
"relation_close(seqrel, AccessShareLock);" rather than NoLock,
so that the lock is actually released between calls. This boosted
the runtime up to 15.5 seconds, or a 50% penalty over HEAD.
My conclusion is that in low-overhead cases, such as using a sequence
to assign default values during COPY IN, the percentage overhead from
acquiring and releasing the lock could be pretty dire. Still, we might
not have much choice if we want nice semantics.
regards, tom lane
