Re: Slow alter sequence with PG10.1

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(at)kruegers(dot)email
> Mobile: 0152 5891 8787

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com