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