
CREATE TABLE tablea (
    id            serial,
    reference     int not null,
    created       date not null
)  PARTITION BY RANGE (created);


CREATE TABLE tablea_part1 PARTITION OF tablea
    FOR VALUES FROM ('2018-01-01') TO ('2018-01-02');
CREATE TABLE tablea_part2 PARTITION OF tablea
    FOR VALUES FROM ('2018-01-02') TO ('2018-01-03');
CREATE TABLE tablea_part3 PARTITION OF tablea
    FOR VALUES FROM ('2018-01-03') TO ('2018-01-04');
CREATE TABLE tablea_part4 PARTITION OF tablea
    FOR VALUES FROM ('2018-01-04') TO ('2018-01-05');
CREATE TABLE tablea_part5 PARTITION OF tablea
    FOR VALUES FROM ('2018-01-05') TO ('2018-01-06');

CREATE INDEX tablea_id_1 ON tablea_part1 (id);
CREATE INDEX tablea_id_2 ON tablea_part2 (id);
CREATE INDEX tablea_id_3 ON tablea_part3 (id);
CREATE INDEX tablea_id_4 ON tablea_part4 (id);
CREATE INDEX tablea_id_5 ON tablea_part5 (id);
CREATE INDEX tablea_reference_1 ON tablea_part1 (reference);
CREATE INDEX tablea_reference_2 ON tablea_part2 (reference);
CREATE INDEX tablea_reference_3 ON tablea_part3 (reference);
CREATE INDEX tablea_reference_4 ON tablea_part4 (reference);
CREATE INDEX tablea_reference_5 ON tablea_part5 (reference);
CREATE INDEX tablea_created_1 ON tablea_part1 (created);
CREATE INDEX tablea_created_2 ON tablea_part2 (created);
CREATE INDEX tablea_created_3 ON tablea_part3 (created);
CREATE INDEX tablea_created_4 ON tablea_part4 (created);
CREATE INDEX tablea_created_5 ON tablea_part5 (created);
alter table tablea_part1 add CHECK ( created >= DATE '2018-01-01' AND created < DATE '2018-01-02');
alter table tablea_part2 add CHECK ( created >= DATE '2018-01-02' AND created < DATE '2018-01-03');
alter table tablea_part3 add CHECK ( created >= DATE '2018-01-03' AND created < DATE '2018-01-04');
alter table tablea_part4 add CHECK ( created >= DATE '2018-01-04' AND created < DATE '2018-01-05');
alter table tablea_part5 add CHECK ( created >= DATE '2018-01-05' AND created < DATE '2018-01-06');

create or replace function populate_tablea()
 RETURNS integer AS
$BODY$                                                             
DECLARE                                      
  i integer;                                   
  v_created date;
BEGIN                                           
    i := 0;                                       
    WHILE (i < 50000)
    loop
        i := i + 1;                                        
        IF (mod(i,5) = 1) THEN
            v_created = '2018-01-01';
        ELSIF (mod(i,5) = 2) THEN
            v_created = '2018-01-02';
         ELSIF (mod(i,5) = 3) THEN
            v_created = '2018-01-03';
        ELSIF (mod(i,5) = 4) THEN
            v_created = '2018-01-04';
        ELSIF (mod(i,5) = 0) THEN
            v_created = '2018-01-05';
        END IF;                            
        insert into tablea values (i, i, v_created);

    end loop;                                                                                   
           
    RETURN i;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
    COST 100;

CREATE TABLE tableb(
    id         serial,
    reference  int not null,
    created    date not null
) PARTITION BY RANGE (created);


CREATE TABLE tableb_part1 PARTITION OF tableb
    FOR VALUES FROM ('2018-01-01') TO ('2018-01-02');
CREATE TABLE tableb_part2 PARTITION OF tableb
    FOR VALUES FROM ('2018-01-02') TO ('2018-01-03');
CREATE TABLE tableb_part3 PARTITION OF tableb
    FOR VALUES FROM ('2018-01-03') TO ('2018-01-04');
CREATE TABLE tableb_part4 PARTITION OF tableb
    FOR VALUES FROM ('2018-01-04') TO ('2018-01-05');
CREATE TABLE tableb_part5 PARTITION OF tableb
    FOR VALUES FROM ('2018-01-05') TO ('2018-01-06');


CREATE INDEX tableb_id_1 ON tableb_part1 (id);
CREATE INDEX tableb_id_2 ON tableb_part2 (id);
CREATE INDEX tableb_id_3 ON tableb_part3 (id);
CREATE INDEX tableb_id_4 ON tableb_part4 (id);
CREATE INDEX tableb_id_5 ON tableb_part5 (id);
CREATE INDEX tableb_reference_1 ON tableb_part1 (reference);
CREATE INDEX tableb_reference_2 ON tableb_part2 (reference);
CREATE INDEX tableb_reference_3 ON tableb_part3 (reference);
CREATE INDEX tableb_reference_4 ON tableb_part4 (reference);
CREATE INDEX tableb_reference_5 ON tableb_part5 (reference);
CREATE INDEX tableb_created_1 ON tableb_part1 (created);
CREATE INDEX tableb_created_2 ON tableb_part2 (created);
CREATE INDEX tableb_created_3 ON tableb_part3 (created);
CREATE INDEX tableb_created_4 ON tableb_part4 (created);
CREATE INDEX tableb_created_5 ON tableb_part5 (created);
alter table tableb_part1 add CHECK ( created >= DATE '2018-01-01' AND created < DATE '2018-01-02');
alter table tableb_part2 add CHECK ( created >= DATE '2018-01-02' AND created < DATE '2018-01-03');
alter table tableb_part3 add CHECK ( created >= DATE '2018-01-03' AND created < DATE '2018-01-04');
alter table tableb_part4 add CHECK ( created >= DATE '2018-01-04' AND created < DATE '2018-01-05');
alter table tableb_part5 add CHECK ( created >= DATE '2018-01-05' AND created < DATE '2018-01-06');


create or replace function populate_tableb()
 RETURNS integer AS
$BODY$
DECLARE
  i integer;
  v_created date;
BEGIN
    i := 0;
    WHILE (i < 50000)
    loop
        i := i + 1;
        IF (mod(i,5) = 0) THEN
            v_created = '2018-01-01';
        ELSIF (mod(i,5) = 1) THEN
            v_created = '2018-01-02';
         ELSIF (mod(i,5) = 2) THEN
            v_created = '2018-01-03';
        ELSIF (mod(i,5) = 3) THEN
            v_created = '2018-01-04';
        ELSIF (mod(i,5) = 4) THEN
            v_created = '2018-01-05';    
        END IF;
        insert into tableb values (i, i, v_created);
    end loop;
    RETURN i;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
    COST 100;

select populate_tablea();
select populate_tableb();
vacuum analyze;