part.sql

-- 
CREATE TABLE measurement_np (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

-- partitioned

-- 1. create parent table
CREATE TABLE measurement_p (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- 2. create partition tables
create table measurement_p_y2018m12 partition of measurement_p 
for values from ('2018-12-01') to ('2019-01-01');

create table measurement_p_y2019m01 partition of measurement_p 
for values from ('2019-01-01') to ('2019-02-01');

create table measurement_p_y2019m02 partition of measurement_p 
for values from ('2019-02-01') to ('2019-03-01');

create table measurement_p_y2019m03 partition of measurement_p 
for values from ('2019-03-01') to ('2019-04-01');

create table measurement_p_y2019m04 partition of measurement_p 
for values from ('2019-04-01') to ('2019-05-01');

create table measurement_p_y2019m05 partition of measurement_p 
for values from ('2019-05-01') to ('2019-06-01');

create table measurement_p_y2019m06 partition of measurement_p 
for values from ('2019-06-01') to ('2019-07-01');

-- 3. create index -> it creates an index on outer table and partition tables
create index on measurement_p (logdate);


-- 4. ensure enable_partition_pruning is enabled
show enable_partition_pruning;


-- adding data(212k) to do tests

-- non partitioned
INSERT INTO public.measurement_np
(city_id, logdate, peaktemp, unitsales)
select c, d, 100, 100
from generate_series('2018-12-01'::date, '2019-07-01'::date - 1, '1 day'::interval) as t(d),
generate_series(1, 1000) as m(c);

analyze measurement_np;

create index on measurement_np (logdate);

explain analyze
select *
from public.measurement_np
where logdate >= '2019-01-01' and logdate < '2019-02-01';

-- partitioned

INSERT INTO public.measurement_p
(city_id, logdate, peaktemp, unitsales)
select c, d, 100, 100
from generate_series('2018-12-01'::date, '2019-07-01'::date - 1, '1 day'::interval) as t(d),
generate_series(1, 1000) as m(c);

analyze measurement_p;
analyze measurement_p_y2018m12;
analyze measurement_p_y2019m01;
analyze measurement_p_y2019m02;
analyze measurement_p_y2019m03;
analyze measurement_p_y2019m04;
analyze measurement_p_y2019m05;
analyze measurement_p_y2019m06;

explain analyze
select *
from public.measurement_p
where logdate >= '2019-01-01' and logdate < '2019-02-01';