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