Re: Slow alter sequence with PG10.1 - Mailing list pgsql-general

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

On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger <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.