Hi
My problem in partitioning is about using functions in table check constraints.
let me explain it
"
DROP DATABASE partitioning;
\set ON_ERROR_STOP y
CREATE DATABASE partitioning;
ALTER DATABASE partitioning set constraint_exclusion TO true;
\c partitioning
CREATE language plpgsql;
CREATE TABLE mainlog (sel int);
CREATE TABLE mainlog_p0 (CHECK (mod(sel,6)=0)) INHERITS (mainlog);
CREATE TABLE mainlog_p1 (CHECK (mod(sel,6)=1)) INHERITS (mainlog);
CREATE TABLE mainlog_p2 (CHECK (mod(sel,6)=2)) INHERITS (mainlog);
CREATE TABLE mainlog_p3 (CHECK (mod(sel,6)=3)) INHERITS (mainlog);
CREATE TABLE mainlog_p4 (CHECK (mod(sel,6)=4)) INHERITS (mainlog);
CREATE TABLE mainlog_p5 (CHECK (mod(sel,6)=5)) INHERITS (mainlog);
CREATE OR REPLACE FUNCTION trg_mainlog_partitioner() RETURNS TRIGGER AS $$
BEGIN
IF mod(NEW.sel,6) = 0 THEN INSERT INTO mainlog_p0 VALUES (NEW.*);
ELSIF mod(NEW.sel,6) = 1 THEN INSERT INTO mainlog_p1 VALUES (NEW.*);
ELSIF mod(NEW.sel,6) = 2 THEN INSERT INTO mainlog_p2 VALUES (NEW.*);
ELSIF mod(NEW.sel,6) = 3 THEN INSERT INTO mainlog_p3 VALUES (NEW.*);
ELSIF mod(NEW.sel,6) = 4 THEN INSERT INTO mainlog_p4 VALUES (NEW.*);
ELSIF mod(NEW.sel,6) = 5 THEN INSERT INTO mainlog_p5 VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$ language plpgsql;
CREATE TRIGGER trg_mainlog_partitioner BEFORE INSERT ON mainlog FOR EACH ROW
execute procedure trg_mainlog_partitioner();
INSERT INTO mainlog(sel) SELECT * FROM generate_series(1,1000);
"
partitioning=# SELECT * from only mainlog;
sel
-----
(0 rows)
partitioning=# SELECT * from only mainlog_p1;
sel
-----
1
7
13
19
25
31
.....
it seems tables contains expected datas.
But lets explain queries
partitioning=# explain SELECT * from mainlog where sel=123;
QUERY PLAN
------------------------------------------------------------------------------
Result (cost=0.00..58.50 rows=18 width=4)
-> Append (cost=0.00..58.50 rows=18 width=4)
-> Seq Scan on mainlog (cost=0.00..40.00 rows=12 width=4)
Filter: (sel = 123)
-> Seq Scan on mainlog_p0 mainlog (cost=0.00..3.08 rows=1 width=4)
Filter: (sel = 123)
-> Seq Scan on mainlog_p1 mainlog (cost=0.00..3.09 rows=1 width=4)
Filter: (sel = 123)
-> Seq Scan on mainlog_p2 mainlog (cost=0.00..3.09 rows=1 width=4)
Filter: (sel = 123)
-> Seq Scan on mainlog_p3 mainlog (cost=0.00..3.09 rows=1 width=4)
Filter: (sel = 123)
-> Seq Scan on mainlog_p4 mainlog (cost=0.00..3.09 rows=1 width=4)
Filter: (sel = 123)
-> Seq Scan on mainlog_p5 mainlog (cost=0.00..3.08 rows=1 width=4)
Filter: (sel = 123)
(16 rows)
i am expecting that query planer only scans "mainlog_p3" not the others since
mod(123,6)=3 so 123 can only be in "mainlog_p3" table and scaning other tables
is waste of time.
What is the problem with my partitioning strategy?
How can i make it fixed and make it working as i expected?
Any suggestions
Thanks for your interest
Note: Forgive me, i can not give real table definitions.
