Slow alter sequence with PG10.1

[Michael Krüger]

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$;

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