XhkthRwM

1DROP TABLE IF EXISTS measurement CASCADE;
2
3-- our master table
4CREATE TABLE measurement (
5    id        bigserial PRIMARY KEY NOT NULL,
6    logdate   date NOT NULL
7);
8
9-- create the partitions
10CREATE TABLE measurement_2016_12 (
11    CHECK ( logdate >= DATE '2016-12-01' AND logdate < DATE '2017-01-01' )
12) INHERITS (measurement);
13
14CREATE TABLE measurement_2017_01 (
15    CHECK ( logdate >= DATE '2017-01-01' AND logdate < DATE '2017-02-01' )
16) INHERITS (measurement);
17
18CREATE TABLE measurement_2017_02 (
19    CHECK ( logdate >= DATE '2017-02-01' AND logdate < DATE '2017-03-01' )
20) INHERITS (measurement);
21
22-- route inserts to the correspondent partition
23CREATE OR REPLACE FUNCTION measurement_insert_trigger()
24RETURNS TRIGGER AS $$
25BEGIN
26    IF ( NEW.logdate >= DATE '2016-12-01' AND
27         NEW.logdate < DATE '2017-01-01' ) THEN
28        INSERT INTO measurement_2016_12 VALUES (NEW.*);
29        
30    ELSIF ( NEW.logdate >= DATE '2017-01-01' AND
31            NEW.logdate < DATE '2017-02-01' ) THEN
32        INSERT INTO measurement_2017_01 VALUES (NEW.*);
33        
34    ELSIF ( NEW.logdate >= DATE '2017-02-01' AND
35            NEW.logdate < DATE '2017-03-01' ) THEN
36        INSERT INTO measurement_2017_02 VALUES (NEW.*);
37        
38    ELSE
39        RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
40    END IF;
41    RETURN NULL;
42END;
43$$
44LANGUAGE plpgsql;
45
46CREATE TRIGGER insert_measurement_trigger
47    BEFORE INSERT ON measurement
48    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
49
50-- add new data
51INSERT INTO measurement (logdate) VALUES ('2016-12-15');
52INSERT INTO measurement (logdate) VALUES ('2017-01-15');
53INSERT INTO measurement (logdate) VALUES ('2017-02-15');
54
55-- how tables are scanned when we disable constraint_exclusion?
56SET constraint_exclusion = off;
57EXPLAIN SELECT * FROM measurement where logdate = '2016-12-15';
58
59-- and when we enable it?
60SET constraint_exclusion = on;
61EXPLAIN SELECT * FROM measurement where logdate = '2016-12-15';