I also confirm this problem:
Running Michael's script on 10.1 takes 314 seconds instead of 2.3
seconds on 9.6.5.
Moreover adding some timing shows that on 10.1 the iteration execution
time grows linearly with each iteration. (!!)
If we remove ALTER SEQUENCE, the difference is only 2.5 times (5 seconds
for 10.1 and 2 - for 9.6.5), and the linear growth effect is not observed.
Removing advisory locks saves ~ 200ms in both cases, and still 9.6.5.
seems faster.
Ivan Panchenko
Postgres Professional
the Russian PostgreSQL Company
+79104339846
22.01.2018 21:55, David G. Johnston пишет:
On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger
<michael@kruegers.email <mailto:michael@kruegers.email>>wrote:
Dear community,
I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after
upgrading it from PG9.6.6. My application heavily uses sequences
and requires different increments of sequence numbers, e.g. a
range of 100, 1000 or 5000 numbers, so it is not possible to set a
fixed increment on a sequence that can be used by my application.
With PG10.1 the performance has dropped seriously so that my
application becomes unusable. After investigating different
aspects, I was able to isolate the issue to be related to the
sequences in Postgres 10.1.
Below shows a simple test script showing the problem:
[...]
On my computer I tried this code on PG9.6.6 and it executed in
roughly 3 seconds.
When running it on PG10.1 it takes over 7 minutes.
Further investigation showed that the problem is related to ALTER
SEQUENCE...
I can't believe that PG10.1 was changed that dramatically without
providing a workaround or a way to switch to the old PG9.6
performance, at least I can't find anything in the documentation.
Is this a bug?
Without testing/confirming I'd be inclined to agree that this is a
regression for an unusual usage of sequences. Work was done to make
typical use cases of sequences more feature-full and it is quite
possible the added effort involved hurts your specific scenario. I'd
expect a hacker to eventually pick this up, confirm the observation,
and provide feedback. This seems like sufficient amount of detail to
get the ball rolling.
David J.
