Re: Slow alter sequence with PG10.1

"Ivan E. Panchenko" <i.panchenko@xxxxxxxxxxxxxx> · Mon, 22 Jan 2018 22:47:46 +0300



    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> 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.