| 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. |
