More info: https://github.com/Timur00Kh/KHAFIZYANOV_11_702/wiki/PostgreSQL-Sequence-chache-test

pgsql_chache_test.sql

CREATE TABLE dog (
	id BIGINT,
	name VARCHAR(100)
);
ALTER TABLE dog ADD CONSTRAINT dog_id_pk PRIMARY KEY (id);

CREATE TABLE dog1000 (
	id BIGINT,
	name VARCHAR(100)
);
ALTER TABLE dog1000 ADD CONSTRAINT dog1000_id_pk PRIMARY KEY (id);

CREATE TABLE dog5000 (
	id BIGINT,
	name VARCHAR(100)
);
ALTER TABLE dog5000 ADD CONSTRAINT dog5000_id_pk PRIMARY KEY (id);

CREATE TABLE dog7500 (
	id BIGINT,
	name VARCHAR(100)
);
ALTER TABLE dog7500 ADD CONSTRAINT dog7500_id_pk PRIMARY KEY (id);

CREATE TABLE dog10000 (
	id BIGINT,
	name VARCHAR(100)
);
ALTER TABLE dog10000 ADD CONSTRAINT dog10000_id_pk PRIMARY KEY (id);

CREATE SEQUENCE dog_id INCREMENT BY 1 OWNED BY dog.id;
ALTER TABLE dog ALTER COLUMN id set default nextval('dog_id');

CREATE SEQUENCE dog1000_id INCREMENT BY 1 CACHE 1000 OWNED BY dog1000.id;
ALTER TABLE dog1000 ALTER COLUMN id set default nextval('dog1000_id');

CREATE SEQUENCE dog5000_id INCREMENT BY 1 CACHE 5000 OWNED BY dog5000.id;
ALTER TABLE dog5000 ALTER COLUMN id set default nextval('dog5000_id');

CREATE SEQUENCE dog7500_id INCREMENT BY 1 CACHE 7500 OWNED BY dog7500.id;
ALTER TABLE dog7500 ALTER COLUMN id set default nextval('dog7500_id');

CREATE SEQUENCE dog10000_id INCREMENT BY 1 CACHE 10000 OWNED BY dog10000.id;
ALTER TABLE dog10000 ALTER COLUMN id set default nextval('dog10000_id');


CREATE TABLE time_keeper (
  type VARCHAR(10),
  microisecs INT
);


create function do_cache_test()
  returns INTEGER
language plpgsql
as $$
DECLARE
  i     INT;
  j     INT;
  timer TIME;
BEGIN
  j := 0;
  WHILE j < 1 LOOP

    /*Вставка по 1000 элементов*/
    i := 0;
    timer := clock_timestamp();
    WHILE i < 1000 LOOP
      INSERT INTO dog (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int;
    INSERT INTO time_keeper VALUES ('1000', i);

    i := 0;
    timer := clock_timestamp();
    WHILE i < 1000 LOOP
      INSERT INTO dog1000 (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int + extract(seconds from timer) :: int * 1000;
    INSERT INTO time_keeper VALUES ('c1000', i);

    /*Вставка по 5000 элементов*/
    i := 0;
    timer := clock_timestamp();
    WHILE i < 5000 LOOP
      INSERT INTO dog (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int + extract(seconds from timer) :: int * 1000;
    INSERT INTO time_keeper VALUES ('5000', i);

    i := 0;
    timer := clock_timestamp();
    WHILE i < 5000 LOOP
      INSERT INTO dog5000 (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int + extract(seconds from timer) :: int * 1000;
    INSERT INTO time_keeper VALUES ('c5000', i);

    /*Вставка по 7500 элементов*/
    i := 0;
    timer := clock_timestamp();
    WHILE i < 7500 LOOP
      INSERT INTO dog (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int + extract(seconds from timer) :: int * 1000;
    INSERT INTO time_keeper VALUES ('7500', i);

    i := 0;
    timer := clock_timestamp();
    WHILE i < 7500 LOOP
      INSERT INTO dog7500 (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int + extract(seconds from timer) :: int * 1000;
    INSERT INTO time_keeper VALUES ('c7500', i);

    /*Вставка по 10000 элементов*/
    i := 0;
    timer := clock_timestamp();
    WHILE i < 10000 LOOP
      INSERT INTO dog (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int + extract(seconds from timer) :: int * 1000;
    INSERT INTO time_keeper VALUES ('10000', i);

    i := 0;
    timer := clock_timestamp();
    WHILE i < 10000 LOOP
      INSERT INTO dog10000 (name) VALUES ('somename');
      i := i + 1;
    end loop;
    timer := clock_timestamp() - timer;
    i := extract(microseconds from timer) :: int + extract(seconds from timer) :: int * 1000;
    INSERT INTO time_keeper VALUES ('c10000', i);

    j := j + 1;
  end loop;


  RETURN 1;
END;
$$;


create function delete_cache_test()
  returns INTEGER
language plpgsql
as $$
	BEGIN

    DROP TABLE dog;
    DROP TABLE dog1000;
    DROP TABLE dog5000;
    DROP TABLE dog7500;
    DROP TABLE dog10000;
    DROP TABLE time_keeper;

    DROP FUNCTION do_cache_test();
    DROP FUNCTION delete_cache_test();

  RETURN 1;
	END;
$$;