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

Hi Adrian and all,

I do not fully understand the reasoning of making sequences transactional in the first place. As far as I know its also not done on Oracle or SQL Server, but maybe I'm even wrong on that. What I question is a change in behavior of existing functionality with such big impact, without config option to restore old behavior, or maybe to have another variant in place like:

create sequence [non] [transactional] to at least be able to decide which variant to use. Maintain the performance of the old behavior, or if transactions safety is needed, the new behavior with the performance impact but more safety if needed.

I will try if Adrians proposal does the trick for my application. Sounds promising, thanks.

Regards,

Michael 

Adrian Klaver <adrian.klaver@aklaver.com> schrieb am Mo., 22. Jan. 2018 um 22:29 Uhr:

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