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

On 01/22/2018 07:24 AM, Michael Krüger 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:
> -- 1) Create a sequence
> CREATE SEQUENCE my_sequence_1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE 
> START WITH 1 CYCLE;
> 
> -- 2) Create a function that allows to request a number range
> CREATE OR REPLACE FUNCTION multi_nextval(
> use_seqname text,
> use_increment integer)
>      RETURNS bigint
>      LANGUAGE 'plpgsql'
>      COST 100
>      VOLATILE
> AS $BODY$
> DECLARE
>      reply int8;
>      lock_id int4;
> BEGIN
>      SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname = 
> split_part(use_seqname, '.', 2);
>      perform pg_advisory_lock(lock_id);
>      execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY ' || 
> use_increment::text;
>      reply := nextval(use_seqname);
>      execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY 1';
>      perform pg_advisory_unlock(lock_id);
>      return reply - use_increment + 1;
> END;
> $BODY$;

Not entirely sure I understand how the batching above works, still maybe 
something like this:

CREATE OR REPLACE FUNCTION public.multi_nextval(use_seqname text, 
use_increment integer)
  RETURNS bigint
  LANGUAGE plpgsql
AS $function$
DECLARE
     reply int8;
     lock_id int4;
     seq_idx int8 :=nextval(use_seqname);
BEGIN
     SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname = 
split_part(use_seqname, '.', 2);
     perform pg_advisory_lock(lock_id);
     perform setval(use_seqname, seq_idx + use_increment, 't');
     reply := nextval(use_seqname);
     perform pg_advisory_unlock(lock_id);
     return reply;
END;
$function$

On an older laptop this does the 20000 loops in about 1.6 secs.

> 
> -- 3) Loop 20000 times and request 5000 values each time
> DO $$
> DECLARE
> --
>    i_index integer;
>    i_value bigint;
> BEGIN
>    FOR i_index IN select * from generate_series(1,20000,1)
>    LOOP
>      SELECT multi_nextval('my_sequence_1',5000) INTO i_value ;
>      if (i_index % 250 = 0) THEN
>        raise notice 'Loop: % - NextVal: %', i_index, i_value;
>      end if;
>    END LOOP;
> END$$;
> 
> 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?
> 
> Thanks in advance,
> Michael
> 
> 
> 
> 
> 
> 
> -- 
> Email:   michael@kruegers.email
> Mobile: 0152 5891 8787


-- 
Adrian Klaver
adrian.klaver@aklaver.com