On 5/10/17 12:24, Andres Freund wrote:
> Upthread I theorized whether
> that's actually still meaningful given fastpath locking and such, but I
> guess we'll have to evaluate that.
I did some testing.
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".
I compared 9.1 (pre fast-path locking) with 9.2 (with fast-path locking).
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);
First, a single session:
9.1 unpatched
Time: 57634.098 ms
9.1 patched
Time: 58674.655 ms
(These were within each other's variance over several runs.)
9.2 unpatched
Time: 64868.305 ms
9.2 patched
Time: 60585.317 ms
(So without contention fast-path locking beats the extra dance that
open_share_lock() does.)
Then, with four concurrent sessions (one timed, three just running):
9.1 unpatched
Time: 73123.661 ms
9.1 patched
Time: 78019.079 ms
So the "hack" avoids the slowdown from contention here.
9.2 unpatched
Time: 73308.873 ms
9.2 patched
Time: 70353.971 ms
Here, fast-path locking beats out the "hack".
If I add more concurrent sessions, everything gets much slower but the
advantage of the patched version stays about the same. But I can't
reliably test that on this hardware.
(One wonders whether these differences remain relevant if this is run
within the context of an INSERT or COPY instead of just a tight loop.)
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services