schema gen & populate

gistfile1.txt

-- Table: public._accumrg1162

-- DROP TABLE public._accumrg1162;

CREATE TABLE public._accumrg1162
(
    _period timestamp without time zone NOT NULL,
    _recorderrref bytea NOT NULL,
    _lineno numeric(9,0) NOT NULL,
    _active boolean NOT NULL,
    _fld1165rref bytea NOT NULL,
    _fld1163rref bytea NOT NULL,
    _fld1172rref bytea NOT NULL,
    _fld1168rref bytea NOT NULL,
    _fld1181rref bytea NOT NULL,
    _fld1175rref bytea NOT NULL,
    _fld1182rref bytea NOT NULL,
    _fld1178rref bytea NOT NULL,
    _fld1164rref bytea NOT NULL,
    _fld1169rref bytea NOT NULL,
    _fld1170rref bytea NOT NULL,
    _fld1171rref bytea NOT NULL,
    _fld1167rref bytea NOT NULL,
    _fld1166rref bytea NOT NULL,
    _fld1173rref bytea NOT NULL,
    _fld1174rref bytea NOT NULL,
    _fld1176rref bytea NOT NULL,
    _fld1177rref bytea NOT NULL,
    _fld1183rref bytea NOT NULL,
    _fld1179rref bytea NOT NULL,
    _fld1180rref bytea NOT NULL,
    _fld1184 numeric(20,10) NOT NULL,
    _fld1185 numeric(10,2) NOT NULL,
    _recordertref bytea NOT NULL DEFAULT '\\000\\000\\000G'::bytea
) PARTITION BY RANGE (_period) 
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

-- Index: _accumrg1162__recordertref__recorderrref__lineno__active_idx

-- DROP INDEX public._accumrg1162__recordertref__recorderrref__lineno__active_idx;

CREATE INDEX _accumrg1162__recordertref__recorderrref__lineno__active_idx
    ON public._accumrg1162 USING btree
    (_recordertref ASC NULLS LAST, _recorderrref ASC NULLS LAST, _lineno DESC NULLS FIRST)
    INCLUDE(_active)
    TABLESPACE pg_default;


-- Index: _accumrg1162_bydims1186

-- DROP INDEX public._accumrg1162_bydims1186;

CREATE UNIQUE INDEX _accumrg1162_bydims1186
    ON public._accumrg1162 USING btree
    (_fld1163rref ASC NULLS LAST, _period ASC NULLS LAST, _recordertref ASC NULLS LAST, _recorderrref ASC NULLS LAST, _lineno ASC NULLS LAST)
    TABLESPACE pg_default;


-- Index: _accumrg1162_bydims1187

-- DROP INDEX public._accumrg1162_bydims1187;

CREATE UNIQUE INDEX _accumrg1162_bydims1187
    ON public._accumrg1162 USING btree
    (_fld1164rref ASC NULLS LAST, _period ASC NULLS LAST, _recordertref ASC NULLS LAST, _recorderrref ASC NULLS LAST, _lineno ASC NULLS LAST)
    TABLESPACE pg_default;


-- Index: _accumrg1162_bydims1188

-- DROP INDEX public._accumrg1162_bydims1188;

CREATE UNIQUE INDEX _accumrg1162_bydims1188
    ON public._accumrg1162 USING btree
    (_fld1165rref ASC NULLS LAST, _period ASC NULLS LAST, _recordertref ASC NULLS LAST, _recorderrref ASC NULLS LAST, _lineno ASC NULLS LAST)
    TABLESPACE pg_default;


-- Index: _accumrg1162_bydims1338

-- DROP INDEX public._accumrg1162_bydims1338;

CREATE UNIQUE INDEX _accumrg1162_bydims1338
    ON public._accumrg1162 USING btree
    (_fld1168rref ASC NULLS LAST, _period ASC NULLS LAST, _recordertref ASC NULLS LAST, _recorderrref ASC NULLS LAST, _lineno ASC NULLS LAST)
    TABLESPACE pg_default;


-- Index: _accumrg1162_bydims1339

-- DROP INDEX public._accumrg1162_bydims1339;

CREATE UNIQUE INDEX _accumrg1162_bydims1339
    ON public._accumrg1162 USING btree
    (_fld1182rref ASC NULLS LAST, _period ASC NULLS LAST, _recordertref ASC NULLS LAST, _recorderrref ASC NULLS LAST, _lineno ASC NULLS LAST)
    TABLESPACE pg_default;


-- Index: _accumrg1162_byperiod

-- DROP INDEX public._accumrg1162_byperiod;

CREATE UNIQUE INDEX _accumrg1162_byperiod
    ON public._accumrg1162 USING btree
    (_period ASC NULLS LAST, _recordertref ASC NULLS LAST, _recorderrref ASC NULLS LAST, _lineno ASC NULLS LAST)
    TABLESPACE pg_default;


-- Index: _accumrg1162_custom_1

-- DROP INDEX public._accumrg1162_custom_1;

CREATE INDEX _accumrg1162_custom_1
    ON public._accumrg1162 USING btree
    (_period ASC NULLS LAST, _recorderrref ASC NULLS LAST)
    TABLESPACE pg_default
    WHERE _recordertref = '\\000\\000\\000G'::bytea;


-- Partitions SQL

CREATE TABLE public._accumrg1162_default PARTITION OF public._accumrg1162
    DEFAULT;

CREATE TABLE public._accumrg1162_y2019_m01 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m02 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m03 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m04 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m05 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m06 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-06-01 00:00:00') TO ('2019-07-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m07 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-07-01 00:00:00') TO ('2019-08-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m08 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-08-01 00:00:00') TO ('2019-09-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m09 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-09-01 00:00:00') TO ('2019-10-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m10 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-10-01 00:00:00') TO ('2019-11-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m11 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-11-01 00:00:00') TO ('2019-12-01 00:00:00');

CREATE TABLE public._accumrg1162_y2019_m12 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2019-12-01 00:00:00') TO ('2020-01-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m01 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m02 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m03 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-03-01 00:00:00') TO ('2020-04-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m04 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-04-01 00:00:00') TO ('2020-05-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m05 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-05-01 00:00:00') TO ('2020-06-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m06 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-06-01 00:00:00') TO ('2020-07-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m07 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-07-01 00:00:00') TO ('2020-08-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m08 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-08-01 00:00:00') TO ('2020-09-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m09 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-09-01 00:00:00') TO ('2020-10-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m10 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-10-01 00:00:00') TO ('2020-11-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m11 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-11-01 00:00:00') TO ('2020-12-01 00:00:00');

CREATE TABLE public._accumrg1162_y2020_m12 PARTITION OF public._accumrg1162
    FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00');

-- Table: public._inforg896

-- DROP TABLE public._inforg896;

CREATE TABLE public._inforg896
(
    _period timestamp without time zone NOT NULL,
    _fld897rref bytea NOT NULL,
    _fld898 numeric(12,6) NOT NULL,
    _fld899 numeric(10,0) NOT NULL
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public._inforg896
    ALTER COLUMN _fld897rref SET STORAGE PLAIN;

-- Index: _inforg896_bydims

-- DROP INDEX public._inforg896_bydims;

CREATE UNIQUE INDEX _inforg896_bydims
    ON public._inforg896 USING btree
    (_fld897rref ASC NULLS LAST, _period ASC NULLS LAST)
    TABLESPACE pg_default;

ALTER TABLE public._inforg896
    CLUSTER ON _inforg896_bydims;


-- Index: _inforg896_byperiod

-- DROP INDEX public._inforg896_byperiod;

CREATE UNIQUE INDEX _inforg896_byperiod
    ON public._inforg896 USING btree
    (_period ASC NULLS LAST, _fld897rref ASC NULLS LAST)
    TABLESPACE pg_default;

--Generate custom data
INSERT INTO _inforg896  (_period, _fld897rref, _fld898, _fld899)
SELECT 
    t.day:: date,
    '\\200\\337\\014\\304z4\\233\\341\\021\\346H\\306\\210\\312?,'::bytea,
    random()*10,
    random()*10
FROM generate_series
        ( '2020-03-01'::timestamp 
        , '2020-03-31'::timestamp
        , '1 day'::interval) AS t(day);

CREATE EXTENSION pgcrypto;

INSERT INTO _accumrg1162 (
    _period,
    _recorderrref,
    _lineno,
    _active,
    _fld1165rref,
    _fld1163rref,
    _fld1172rref,
    _fld1168rref,
    _fld1181rref,
    _fld1175rref,
    _fld1182rref,
    _fld1178rref,
    _fld1164rref,
    _fld1169rref,
    _fld1170rref,
    _fld1171rref,
    _fld1167rref,
    _fld1166rref,
    _fld1173rref,
    _fld1174rref,
    _fld1176rref,
    _fld1177rref,
    _fld1183rref,
    _fld1179rref,
    _fld1180rref,
    _fld1184,
    _fld1185,
    _recordertref) 
SELECT
    date_trunc('day', timestamp '2020-03-01' + random() * INTERVAL '30 days'),
    gen_random_bytes(11)::bytea,
    l,
    TRUE,
    gen_random_bytes(11)::bytea,
    '\\000\\000\\000G'::bytea,
   '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    gen_random_bytes(11)::bytea,
    '\\000\\000\\000G'::bytea,
    gen_random_bytes(11)::bytea,
    '\\000\\000\\000G'::bytea,
    gen_random_bytes(11)::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    '\\000\\000\\000G'::bytea,
    random() * 1500,
    random() * 1000,
    gen_random_bytes(12)::bytea
FROM
    generate_series(1, 200000000) as l;

CREATE EXTENSION pg_strom;